Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet events - when are they triggered!
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 -- agarwaldv ----------------------------------------------------------------------- agarwaldvk's Profile: http://www.excelforum.com/member.php...fo&userid=1134 View this thread: http://www.excelforum.com/showthread.php?threadid=26411 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet events - when are they triggered!
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 In article , agarwaldvk wrote: Could someone tell me if the worksheet_change event of say Sheet1 will be triggered when the value in a cell on this worksheet (sheet1 that is) is changed by the worksheet_change event of another worksheet say sheet2? I know that it won't be triggered off when the value in a cell in sheet1 gets changed by Visual Basic or by external links but in this case I am not so sure!!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet Events | Excel Discussion (Misc queries) | |||
Combobox Click event triggered when copying worksheet | Excel Programming | |||
Events for a Protected Worksheet | Excel Programming | |||
Findjjing the range that triggered worksheet calculate | Excel Programming | |||
Trapping Events generated by a Worksheet | Excel Programming |