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

Works by me!

Are you sure you check the right Target.Address ?

HTH
--
AP

"GerbilGod7" a
écrit dans le message de news:
...

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?


--
GerbilGod7
------------------------------------------------------------------------
GerbilGod7's Profile: http://www.excelforum.com/member.php...o&userid=13073
View this thread: http://www.excelforum.com/showthread...hreadid=539886

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!


--
GerbilGod7
------------------------------------------------------------------------
GerbilGod7's Profile: http://www.excelforum.com/member.php...o&userid=13073
View this thread: http://www.excelforum.com/showthread...hreadid=539886