Hi all
In Excel I have a cell with validation set , (restricting input to values in a range), and a dropdown list for the user. When the user changes this cell by using the dropdown list it does not seem to trigger the worksheet change event. I want the change of cell to trigger an event to collect data relating to the selection. Any ideas why the drop does not trigger the event as expected
Regards
ADG
Worksheet_Change event and dropdown list
james_cline_
Hi
Thanks for the reply.
We are using Office 97, (changing to 2003 shortly). Events are enabled. I have put a statement to print the target address into the debug window in the change event, all other cells generate an entry. If I type a valid entry the cell address $D$3 appears. If I use the dropdown to change the cell the event does not trigger.
dtlinker
Looks like xl97 does not trigger the Change event with data validation.
Simon Heffer
Data validation list changing triggers the Worksheet_Change event for me in xl2000 and xl2003.
Is it possible you have disable events with code and forget to switch it back on
Application.EnableEvents = True
JRQ
more information:
worksheet_change occurs before calculationstate is xldone
in case of incell dropdown try to remember calculationstate in public variable and use worksheet_calculate for your macro
(worksheet_calculate occur twice! before calculations done and after that)
(use "if calculationstate<>xldone then" to ignore wrong entry of worksheet_calculate)
(you can also use application.enableevents=false to ignore wrong entry of worksheet_calculate)
public bwas as boolean
private sub worksheet_calculate()
if bwas then
'your_procedures
bwas=false
endif
end sub
private sub worksheet_change(byval target as range)
if application.calculationstate<>xldone then bwas=true
end sub
(you can use worksheet_calculate only)
in case of checkboxes use chkBox.onaction="your_macro" (onaction accur after calculations is done)
application.calculationstate can be wrong if one of workbooks has manual calculation - use local calculationstates or autofilter property if lastone exist (is in false value when calculations is not xldone)
regards