Worksheet Change Event-an grow and shrink

I have a dropdown menu (created with data validation) that refers to a list of values in my sheet that can grow and shrink (the validation is something like "=OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)"

What I'm trying to do is to have the cell turn red when a certain value is selected. To do this, I'm using the Worksheet_Change event. However, the Change event does not seem to fire when I use the menu. I know the routine works because if I change anything ELSE in the sheet, the event fires and everything works like it's supposed to. It just seems to ignore changes in the menu cell. It also ignores any related changes... For example, if the menu is in B1, and C1 contains "=B1", selecting a different value from the menu will change the value in C1 but won't fire the event.

I can sort of get around this by changing Worksheet_Change to Selection_Change, but it's uglier.

Any suggestions? Thanks!

Works by me!

Are you sure you check the right Target.Address ?

HTH
--
AP

"GerbilGod7" <GerbilGod7.27hwca_1147102504.923@excelforum-nospam.com> a
écrit dans le message de news:
GerbilGod7.27hwca_1147102504.923@excelforum-nospam.com...
>
> I have a dropdown menu (created with data validation) that refers to a
> list of values in my sheet that can grow and shrink (the validation is
> something like "=OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)"
>
> What I'm trying to do is to have the cell turn red when a certain value
> is selected. To do this, I'm using the Worksheet_Change event. However,
> the Change event does not seem to fire when I use the menu. I know the
> routine works because if I change anything ELSE in the sheet, the event
> fires and everything works like it's supposed to. It just seems to
> ignore changes in the menu cell. It also ignores any related changes...
> For example, if the menu is in B1, and C1 contains "=B1", selecting a
> different value from the menu will change the value in C1 but won't
> fire the event.
>
> I can sort of get around this by changing Worksheet_Change to
> Selection_Change, but it's uglier.
>
> Any suggestions? Thanks!
>
>
> --
> GerbilGod7
> ------------------------------------------------------------------------
> GerbilGod7's Profile:
> http://www.excelforum.com/member.php...o&userid=13073
> View this thread: http://www.excelforum.com/showthread...hreadid=539886
>

I'm pretty sure I am, but it doesn't even get that far... I put in a notifier before the Target intersect to let me know when the change event is called by the sheet:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

MsgBox ("Change Event Called")

If Intersect(Target, Range("B1")) Then
...etc.

I am using '97 (at work), could that be the problem? I also created a workbook from scratch with nothing in it but the list, the validated menu referring to the list, and the change code. Same problem. However, if I input a list directly into the validation form (i.e. if it's not a dynamic list), the change event works.

Is this just an Excel (or an Excel 97) bug?

Are you using xl97?

From Debra Dalgleish's site:
http://contextures.com/xlDataVal08.html#Change

GerbilGod7 wrote:
>
> I have a dropdown menu (created with data validation) that refers to a
> list of values in my sheet that can grow and shrink (the validation is
> something like "=OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)"
>
> What I'm trying to do is to have the cell turn red when a certain value
> is selected. To do this, I'm using the Worksheet_Change event. However,
> the Change event does not seem to fire when I use the menu. I know the
> routine works because if I change anything ELSE in the sheet, the event
> fires and everything works like it's supposed to. It just seems to
> ignore changes in the menu cell. It also ignores any related changes...
> For example, if the menu is in B1, and C1 contains "=B1", selecting a
> different value from the menu will change the value in C1 but won't
> fire the event.
>
> I can sort of get around this by changing Worksheet_Change to
> Selection_Change, but it's uglier.
>
> Any suggestions? Thanks!
>
> --
> GerbilGod7
> ------------------------------------------------------------------------
> GerbilGod7's Profile: http://www.excelforum.com/member.php...o&userid=13073
> View this thread: http://www.excelforum.com/showthread...hreadid=539886

--

Dave Peterson

That answers the question, thanks!