Initiating code utilizing drop down (Data Validation)

[Kirk]

I would like to be able to initiate some VBA code when
the value of a cell changes when a user selects a
specific value from a drop down list. List is created by
using Data -> Validation. It is not a drop down box that
is created by using the Component toolbox.

does anyone know if I can initialize code this way? I
have been unable to figure it out. Any help would be
appreciated.

Thanks.

Kirk

 

[EarlK]

Kirk,

Use the Worksheet_Change event in the sheet module (not a regular code
module). It fires with a change to any cell in the sheet, so you may have
to test to determine which cell was changed if you're interested in changes
in only certain cells. Post back for details on any of this.

Regards from Virginia Beach,

EarlK

 

[EarlK]

Dave,

Yeah. That's what I thought too. But I tried it with XL97 before I
responded, and it sure enough fired the Worksheet_Change event when choosing
a value from a Data Validation dropdown. XP worked too.

On checking it again, I found that if the list elements are supplied
immediately in the Data Validation dialog box (I put 1,2,3,4,5), the event
fires. But if it's supplied from cells, it doesn't. ):

I think it's my own fault. I could have taken up archery or something
instead!

Regards from Virginia Beach,

EarlK
-------------------------------------------------------------

 

[steve]

Kirk,

Changes in a Data Validation drop down will not activate a change event.

What some have done is to setup a separate cell with a formula dependent
on the validation value. Than they use the calculation event to capture the
change.

steve