Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change event not triggered as expected
I've been searching on past posts to this NG do with the Worksheet_Change
not firing as expected, but have not found a reference to this issue (only on Data Validation). I set up a ComboBox (from the Control Toolbox toolbar) and also a ComboBox (from the Forms toolbar) Sheet1, linked them to different cells on the same worksheet, and populated them with entries from that worksheet. Once I'd set them up, the Worksheet_Change event was never fired by changing the selection on either ComboBox. I also linked a second pair of cells on the same worksheet to the linked cells, so that these also changed as the selections of either ComboBox were changed. After the initial setting up of these cells, again, the Worksheet_Change event was never triggered. I also linked another cell on Sheet1 to a cell on Sheet2, and even when I changed the value in the cell on Sheet2, the Worksheet_Change event on the Sheet1 didn't fire, after the initial setting up of the link. It appears that the user has to actually select a cell and change its value directly, in order for the Worksheet_Change event to fire. I had to get around this by using the Worksheet_Calculate event for my project, which fired every time in the above examples. The problem with this, however, is that you can't specify a Target cell as a parameter for Worksheet_Calculate, as you can for Worksheet_Change. I checked the above by putting the following code in the Sheet1 module: Private Sub Worksheet_Calculate() Call Module1.Calculated End Sub Private Sub Worksheet_Change(ByVal Target As Range) MsgBox Target.Address Call Module1.Changed End Sub and this as the code for Calculated and Changed in the code module Module1: Sub Calculated() MsgBox "Calculated!" End Sub Sub Changed() MsgBox "Changed!" End Sub My questions a 1. Is this the correct (intended) behaviour for the Worksheet_Change event? 2. Is there a workaround where you can specify a Target cell that's changed? I'm using Excel 2003 SP3 (fully patched and updated) on a WindowsXP Home box. Many thanks Ian |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change event not triggered as expected
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change event not triggered as expected
Try putting =now() in a cell somewhere
Thanks. Tried that - but it still triggers only Worksheet_Calculate and not Worksheet_Change. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change event not triggered as expected
I'm not sure what was intended with anything MS has done, what happens on the
will indeed cause a change, linking does not necessarily do this, but that's why events are built for the toolbox items. I'm not sure what you are trying to do, but if a combo boxes linked cell changing is what you want an event on, set that in the comboboxes change event. If thats not what you meant then be more specific on what you want to happen and when. -- -John Please rate when your question is answered to help us and others know what is helpful. "IanKR" wrote: I've been searching on past posts to this NG do with the Worksheet_Change not firing as expected, but have not found a reference to this issue (only on Data Validation). I set up a ComboBox (from the Control Toolbox toolbar) and also a ComboBox (from the Forms toolbar) Sheet1, linked them to different cells on the same worksheet, and populated them with entries from that worksheet. Once I'd set them up, the Worksheet_Change event was never fired by changing the selection on either ComboBox. I also linked a second pair of cells on the same worksheet to the linked cells, so that these also changed as the selections of either ComboBox were changed. After the initial setting up of these cells, again, the Worksheet_Change event was never triggered. I also linked another cell on Sheet1 to a cell on Sheet2, and even when I changed the value in the cell on Sheet2, the Worksheet_Change event on the Sheet1 didn't fire, after the initial setting up of the link. It appears that the user has to actually select a cell and change its value directly, in order for the Worksheet_Change event to fire. I had to get around this by using the Worksheet_Calculate event for my project, which fired every time in the above examples. The problem with this, however, is that you can't specify a Target cell as a parameter for Worksheet_Calculate, as you can for Worksheet_Change. I checked the above by putting the following code in the Sheet1 module: Private Sub Worksheet_Calculate() Call Module1.Calculated End Sub Private Sub Worksheet_Change(ByVal Target As Range) MsgBox Target.Address Call Module1.Changed End Sub and this as the code for Calculated and Changed in the code module Module1: Sub Calculated() MsgBox "Calculated!" End Sub Sub Changed() MsgBox "Changed!" End Sub My questions a 1. Is this the correct (intended) behaviour for the Worksheet_Change event? 2. Is there a workaround where you can specify a Target cell that's changed? I'm using Excel 2003 SP3 (fully patched and updated) on a WindowsXP Home box. Many thanks Ian |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheet_Change event not triggered as expected
I'm not sure what was intended with anything MS has done, what
happens on the will indeed cause a change, linking does not necessarily do this, but that's why events are built for the toolbox items. I'm not sure what you are trying to do, but if a combo boxes linked cell changing is what you want an event on, set that in the comboboxes change event. If thats not what you meant then be more specific on what you want to happen and when. Thanks - I never I thought of that. Basically, I want code to run every time the linked cell changes. I shall put the code in the ComboBox's own change event, instead of the worksheet's. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro triggered by an event | Excel Programming | |||
Worksheet_Change - determine how it is triggered | Excel Programming | |||
Event triggered by paste | Excel Programming | |||
Macro triggered by an event | Excel Discussion (Misc queries) | |||
Worksheet_Change Event triggered off specific cell | Excel Programming |