Worksheet events - when are they triggered!

[agarwaldvk]

Hi All

Could someone tell me if the worksheet_change event of say Sheet1 wil
be triggered when the value in a cell on this worksheet (sheet1 tha
is) is changed by the worksheet_change event of another worksheet sa
sheet2?

I know that it won't be triggered off when the value in a cell i
sheet1 gets changed by Visual Basic or by external links but in thi
case I am not so sure!!!!


Thanks in advance and best regards!


Deepa

 

[JE McGimpsey]

Your second statement is incorrect. The Worksheet_Change event fires
whenever a cell in the worksheet is changed by manual entry or external
link. It will also fire when an entry is made via VBA, so try:

Sheet1:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Select Case Target.Address(False, False)
Case "A1"
MsgBox "Fired on Sheet1!A1"
With Sheets(2).Range("B1")
.Value = .Value + 1
End With
MsgBox "Done with Sheet1!A1"
Case "B1"
MsgBox "Fired on Sheet1!B1"
Case Else
End Select
End Sub

Sheet2:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Select Case Target.Address(False, False)
Case "A1"
MsgBox "Fired on Sheet2!A1"
With Sheets(1).Range("B1")
.Value = .Value + 1
End With
MsgBox "Done with Sheet2!A1"
Case "B1"
MsgBox "Fired on Sheet2!B1"
Case Else
End Select
End Sub

Whether the change is made from the same worksheet's _Change() event or
a different sheet's _Change() event doesn't matter.

To avoid firing when making a change via VBA, use
Application.EnableEvents = False:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Select Case Target.Address(False, False)
Case "A1"
Application.EnableEvents = False
MsgBox "Fired on Sheet1!A1"
With Sheets(2).Range("B1")
.Value = .Value + 1
End With
MsgBox "Done with Sheet1!A1"
Application.EnableEvents = True
Case "B1"
MsgBox "Fired on Sheet1!B1"
Case Else
End Select
End Sub