Change event for data validation listbox

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 [email protected]> wrote in
message news:[email protected]...
> 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 [email protected]> wrote in
> message news:[email protected]...
> > 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" <[email protected]> wrote in
message news:[email protected]...
> 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 [email protected]> wrote in
> > message news:[email protected]...
> > > 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" <[email protected]> wrote in
> message news:[email protected]...
> > 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 [email protected]> wrote in
> > > message news:[email protected]...
> > > > 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
> > >
> > >
> > >
>
>
>