Change event for data validation listbox

[Guest]

I'm trying to use the worksheet_change event to trigger some changes on a
worksheet. I have a cell with values restricted to a list, using the Data,
Validation menu. Picking a cell value from this listbox does not appear to
trigger a change event.

What can I do to get round this problem? I don't want to use a separate
listbox control since, in addition to trying to trigger code from the listbox
value, it is also used in calculations using formulae in other cells.

Any help appreciated.

Steve

 

[Bernie Deitrick]

Steve,

Selecting a value from a Data Validation List should trigger the change
event - at least in Excel XP it does. Are you sure you have events enabled?
Often, if an error within an event will leave the application.enableevents
set to false. Try running this code:

Sub ResetEvents()
Application.EnableEvents = True
End Sub

HTH,
Bernie
MS Excel MVP

 

[Guest]

Thanks Bernie - but no joy.

I should have added that I'm in Excel 97. Perhaps it doesn't function
correctly in that version.

If you have any other suggestions....

Steve

 

[Bernie Deitrick]

Steve,

One (of many) work-arounds for this is to use the worksheet calculate event.
Say that that cell is cell A1. In another cell, say B1, use the formula
=A1. You need a third cell, say C1, that will store the initial value.
Then, use the calculate event

Private Sub Worksheet_Calculate()
If Range("B1").Value <> Range("C1").Value Then
'the value in A1 has changed
'Code to run here
MsgBox "Cell A1 has changed!"
Application.EnableEvents = False
Range("C1").Value = Range("B1").Value
Application.EnableEvents = True
End If
End Sub

HTH,
Bernie
MS Excel MVP

 

[Guest]

Thanks Bernie - does the trick.

Steve,

One (of many) work-arounds for this is to use the worksheet calculate event.
Say that that cell is cell A1. In another cell, say B1, use the formula
=A1. You need a third cell, say C1, that will store the initial value.
Then, use the calculate event

Private Sub Worksheet_Calculate()
If Range("B1").Value <> Range("C1").Value Then
'the value in A1 has changed
'Code to run here
MsgBox "Cell A1 has changed!"
Application.EnableEvents = False
Range("C1").Value = Range("B1").Value
Application.EnableEvents = True
End If
End Sub

HTH,
Bernie
MS Excel MVP