Data Validation List not recognised as a Worksheet_Change

[LeighAnne]

I've set up some data validation on my spreadsheet that forces the user to select from a list of options for that cell.

I want to run a "Worksheet_Change" macro when the value of this cell changes.

The macro runs when the user types in a change but not when they select a different option from the drop down list ?

How can I get around this ?

Leigh-Anne

 

[VulcanJedi]

IF you mean you have a combobox embedded on the worksheet and the code worksheet_change isn't working, you can all the worksheet_change code or replicate it, and I believe to get it operational you'll need to identify it w/:

Private Sub ComboBox1_Change()
'* The code from worksheet_change() or try calling the sub from here
End Sub
Tranpkp
************************************
- Let me know if this helped/worked!
Please remember to give helpful posts the stars they deserve!
This facilitates others navigating through the threads / posts!

 

[LeighAnne]

Tranpkp,

I don't mean a combo box.

From the 'Data' menu in Excel I've selected 'Validation' and forced the user to select a value from my list (these are held elsewhere on the worksheet.

Thw Worksheet_Change macro that i've written is activated if you type in this cell but not if you select an option from the list.

Does this clear up what I'm trying to do ?

Le

 

[xlbo]

A fairly naff workaround but if you have a column that calculates based on the data validation, when the validation is changed, the formula re-calcs and the change event is called
so, if data validation entry is in A1, in AA1 enter
=A1
You can hide this column or whatever - the crux is that it will force the change event to occur - only prob is that if you have loads of data validation cells, you'll need loads of formulae to trigger...
Rgds
~Geoff~

 

[LeighAnne]

Geoff,

I tried your solution but the same thing happens again.

The value in AA1 changes when I select a drop down in A1 but it still doesn't set off the Worksheet_Change event.

Any other ideas ?

Leigh-Anne

(P.S. It's helpful to know I'm not the only one that's puzzled !)

 

[kylua]

Replace the validated drop down box with a combo box and put the code required in the change event. The combobox will do anything that the validated list box will do including taking values from the sheet.
I don't think the sheet change event is fired by anything other than direct editing of the sheet and anything using a listbox is just changing a reference.

 

[xlbo]

Fair enuff - worked in my workbook but I have to say I was working on a different approach of utilising the worksheet calculate event - again this has it's drawbacks as it would run when ANYTHING on the sheet calcs
Other than that, using a combobox may be a goer....if you don't have too many of them - if you have a whole list of data validations that need to be changed to combo boxes, you're opening yourself up to large workbook size and easy corruption

What is the situation - do you have loads of data validation or only a few / loads of calcs or not many...what is the structure of the worksheet and what are you trying to acheive with the worksheet change?? bit more info may provide a valid workaround ('cos that's the territory we're in now) Rgds
~Geoff~