Workbook_SheetChange

I'm using the Workbook_SheetChange event to run some code when the selection
in a drop down list (created using data validation) changes. This works fine
in 2003, but it doesn't work at all in 97. Is this right? Do I have to
change the way my code is called or am I just doing something wrong?

Any suggestions gratefully received as my current solution now seems to be a
dead duck.

Thanks. Matt B

Data validation using the list option did not trigger the change event in
Excel 97 when a selection was made from the list if the list was filled from
a worksheet range (as opposed to typed in directly).

This was a bug fixed in later versions of Excel. (so if you need this
functionality in xl97, you will have to use a workaround. Popular
suggestions are to have a formula dependent on the selection and use the
calculate event - although this is less specific in determining when to run.

--
Regards,
Tom Ogilvy

"Matt B" <[email protected]> wrote in message
news:[email protected]...
> I'm using the Workbook_SheetChange event to run some code when the
selection
> in a drop down list (created using data validation) changes. This works
fine
> in 2003, but it doesn't work at all in 97. Is this right? Do I have to
> change the way my code is called or am I just doing something wrong?
>
> Any suggestions gratefully received as my current solution now seems to be
a
> dead duck.
>
> Thanks. Matt B

Matt,

This is correct, a 'feature' of XL97.

I get around it by linking a cell to my target cell, which will trigger the
Worksheet_Calculate event. From here, you can test if the activecell is your
target cell.

--

HTH

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


"Matt B" <[email protected]> wrote in message
news:[email protected]...
> I'm using the Workbook_SheetChange event to run some code when the
selection
> in a drop down list (created using data validation) changes. This works
fine
> in 2003, but it doesn't work at all in 97. Is this right? Do I have to
> change the way my code is called or am I just doing something wrong?
>
> Any suggestions gratefully received as my current solution now seems to be
a
> dead duck.
>
> Thanks. Matt B

Or just use a combobox from the control toolbox toolbar.

Matt B wrote:
>
> I'm using the Workbook_SheetChange event to run some code when the selection
> in a drop down list (created using data validation) changes. This works fine
> in 2003, but it doesn't work at all in 97. Is this right? Do I have to
> change the way my code is called or am I just doing something wrong?
>
> Any suggestions gratefully received as my current solution now seems to be a
> dead duck.
>
> Thanks. Matt B

--

Dave Peterson