当范围内的单元格更改并满足给定条件时，显示Excel VBA消息框[关闭]

3 回答

• 1

  UPDATE

  只有输入单元格改变时才会触发此代码，这比仅使用“Worksheet_Calulate”更好：

  Private Sub Worksheet_Change(ByVal Target As Range)
  
  Dim myRange As Range
  
  myRange = Range("B1:B2") '-> assumes input cells are in B1:B2
  
  If Intersect(myRange, Target) Then
  
      '-> assumes calculated cell in A1
      If Range("A1").Value > 0.2 Then MsgBox "Above 20%"
  
      '-> to loop through many cells, do this
      Dim cel As Range
      For Each cel In Range("A1:A10")
  
          If cel.Value > 0.2 Then MsgBox cel.Address & " Above 20%"
          Exit For
  
      Next
  
  End If
  
  End Sub
  

  回复于 2024-04-19T16:59:22+08:00
• 0

  编辑：斯科特让我想起了 Intersect 函数，它比这个 InRange 函数更好

  编辑2：这将允许您对不同的范围有不同的规则 . 如果用户更改的单元格在您的一个受控范围内，则调用该范围的验证规则 . 否则该功能继续 .

  Private Sub Worksheet_Change(ByVal Target As Range)
      Dim Range1 As Range, Range2 As Range '...
      Set Range1 = Me.Range("A1:A9")
      Set Range2 = Me.Range("B1:B9")
      '...
  
      If Not intersect(Range1, Target) Is Nothing Then
          'Rule for Range1
          If Target.Value > 0.2 Then   'put your condition here
              MsgBox "You exceeded 20%"
          End If
  
      ElseIf intersect(Range2, Target) Is Nothing Then
          'Rule for Range2...
      'elseif more ranges...
           'More rules...
      End If
  
  End Sub
  

  回复于 2024-04-19T16:59:22+08:00
• 1

  下面是使用工作簿工作表更改事件的示例，该事件检查工作表1中A1单元格的更改

  Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  On Error Resume Next
      'check to ensure we are working in the correct Sheet
      If ActiveWorkbook.ActiveSheet.Name = "Sheet1" Then
          'check to see how many cells have been targeted
          If Target.Cells.Count = 1 Then
              If Target.Cells.Address = "$A$1" Then
                  'check to see what value has been entered into the cell
                  If Target.Value = 20 Then
                      MsgBox "Alerting the user"
                  End If
              End If
          End If
      End If
  End Sub
  

  回复于 2024-04-19T16:59:22+08:00