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

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

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

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" wrote:

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

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

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

"Steve Parkinson" wrote in
message ...
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" wrote:

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

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

Thanks Bernie - does the trick.

"Bernie Deitrick" wrote:

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

"Steve Parkinson" wrote in
message ...
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" wrote:

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

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