Worksheet_Change on Excel 97

[Guest]

Hi,
I'm running on Excel 97 and use this event macro to keep history of
modifications :

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 1 Then
Cells(Target.Row, Target.Column + 1) = Now
Cells(Target.Row, Target.Column + 2) =
CreateObject("Wscript.Network").UserName
End If
Application.EnableEvents = True
End Sub

I also use, for the first column cells, the "Validation" tool allowing only
three param values in the cell.

Problem : the event macro works by typing the param value and pressing
"enter" key but NO by selecting a direct value within the combo list.

Any idea to make it work ?! Thanks in advance...

 

[Tom Ogilvy]

In xl97, the change event does not fire in this situation if you populate
the validation from cells on the worksheet. I understand that if you hard
code the values in the textbox for the list in the data validation dialog
(Data=>Validation, list option), then it will fire. So that would be a work
around if you only allow 3 values.

 

[Guest]

Many thanks Tom...

In xl97, the change event does not fire in this situation if you populate
the validation from cells on the worksheet. I understand that if you hard
code the values in the textbox for the list in the data validation dialog
(Data=>Validation, list option), then it will fire. So that would be a work
around if you only allow 3 values.

 

[Bob Phillips]

You could also add a reference to that DV cell (=H10 say), and then use the
Worksheet_Calculate event to do your stuff, as this does then get triggered.

--

HTH

RP
(remove nothere from the email address if mailing direct)