Try putting =now() in a cell somewhere

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"IanKR" wrote in message
...
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