Worksheet_Change event not triggered as expected

[IanKR]

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 are:

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

 

[Don Guillett]

Try putting =now() in a cell somewhere

 

[IanKR]

Try putting =now() in a cell somewhere

Thanks. Tried that - but it still triggers only Worksheet_Calculate and not
Worksheet_Change.

 

[Guest]

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.

 

[IanKR]

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.