I need to run a subroutine depending on the value of a validation list.
Currently I use Worksheet_SelectionChange to see if the validation list
was entered from another cell and that works. However I have the need
to run subroutines where a specific subroutine is run depending on the
value of the validation list.

Where this all breaks down is when a user changes the value of the
valdation list without leaving the cell. I can manually change the
value of the cell that contains the validation list, but I can't get
any type of event to fire when the value is changed.

Suppose the cell A1 has a validation list of "Pass", "Fail", and "N/A".
If I go from any other cell to A1 and select "Pass" the
Worksheet_SelectionChange event fires. But if A1 remains the current
cell and I change its value from "Pass" to "Fail" the
Worksheet_SelectionChange event fails to fire. I need specific
subroutines to run depending on what the cell's value is manually
changed to.

Using the trick of a separate worksheet's Calulate event to hack firing
an event with a change of the validation list is unacceptable. (And I
could never get it to work the way I need things to work).

Any ideas?

Use the Worksheet_Change event to check a change in the DV cell.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"MrAlb" wrote in message
oups.com...
I need to run a subroutine depending on the value of a validation list.
Currently I use Worksheet_SelectionChange to see if the validation list
was entered from another cell and that works. However I have the need
to run subroutines where a specific subroutine is run depending on the
value of the validation list.

Where this all breaks down is when a user changes the value of the
valdation list without leaving the cell. I can manually change the
value of the cell that contains the validation list, but I can't get
any type of event to fire when the value is changed.

Suppose the cell A1 has a validation list of "Pass", "Fail", and "N/A".
If I go from any other cell to A1 and select "Pass" the
Worksheet_SelectionChange event fires. But if A1 remains the current
cell and I change its value from "Pass" to "Fail" the
Worksheet_SelectionChange event fails to fire. I need specific
subroutines to run depending on what the cell's value is manually
changed to.

Using the trick of a separate worksheet's Calulate event to hack firing
an event with a change of the validation list is unacceptable. (And I
could never get it to work the way I need things to work).

Any ideas?

Use the Change event.

Worksheet_Change fires when a cell value changes.
Worksheet_SelectionChange fires when the cell is selected and before the
value is changed.

--
Regards,
Tom Ogilvy

"MrAlb" wrote in message
oups.com...
I need to run a subroutine depending on the value of a validation list.
Currently I use Worksheet_SelectionChange to see if the validation list
was entered from another cell and that works. However I have the need
to run subroutines where a specific subroutine is run depending on the
value of the validation list.

Where this all breaks down is when a user changes the value of the
valdation list without leaving the cell. I can manually change the
value of the cell that contains the validation list, but I can't get
any type of event to fire when the value is changed.

Suppose the cell A1 has a validation list of "Pass", "Fail", and "N/A".
If I go from any other cell to A1 and select "Pass" the
Worksheet_SelectionChange event fires. But if A1 remains the current
cell and I change its value from "Pass" to "Fail" the
Worksheet_SelectionChange event fails to fire. I need specific
subroutines to run depending on what the cell's value is manually
changed to.

Using the trick of a separate worksheet's Calulate event to hack firing
an event with a change of the validation list is unacceptable. (And I
could never get it to work the way I need things to work).

Any ideas?

I knew that. ;-)

Huh boy... the things we forget when we don't practice our skills.

Thanks, Bob. :-)

Bob Phillips wrote:
Use the Worksheet_Change event to check a change in the DV cell.

--
HTH

Bob Phillips