Worksheet_Change with validated list data

  • Thread starter Thread starter Paul Gouteff
  • Start date Start date
P

Paul Gouteff

Hi

I have previously used the worksheet_change event
successfully. However, when I change the value of a cell
that has its values restricted by a validated list the
Worksheet_Change event does not trigger.

Why is this? Is there anyway around this problem?
(I am using Excel 97)

thanks for your assistance,
Paul.
 
Hi Paul
this is a problem of Excel 97 (in Excel 2000+ the worksheet_change
event is triggered by this). One possible workaround:
- use a formula which references your cell with the drop-down (e.g. if
A1 stores your drop-down listbox enter the folloiwng in X1: =A1)
- you may hide this helper cell (the respective column)
- use the worksheet_calculate event which is triggered due to this
'helper' formula
 
Or just put a combobox on the worksheet and put code behind that.

(From a Tom Ogilvy suggestion to my suggestion that matched yours!)

Why go through all that rigamarole(?) of _calculate and helper cells.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Worksheet_Change after validation 6
Worksheet_SelectionChange & Worksheet_Change results in different Targets 2
bug: Worksheet_Change clashes with dropdown list validation. 4
Run VBA code only worksheet change, but don't trigger worksheet_change event based on what the code 6
Data form not triggering Worksheet_Change event 3
Runtime-error 1004 (excel 2007) when selecting validation list optiontriggering worksheet_change pro 1
Worksheet_Change event not triggered as expected 4
Validation not triggering Change event in Excel XP 3

Back
Top