call a sub when data validation list changes

[mperrah]

I have a data validation list in a worksheet that changes the source for a pie chart.
I have a sub that updates the numbers the source uses.
I'm trying to get the sub to run when the data list changes (user chooses different drop down value)
Any suggestions?

This is the sub

Please Login or Register  to view this content.

Mark

[royUK]

have you got a ListBox/

It would be easier to use a ComboBox than Data Validation.

[mperrah]

Added in a combo box,
first time using one.
My code was checking the change event on the cell the data validation was in.
How do I check the combo box?

Here is the code so far:

Please Login or Register  to view this content.

[royUK]

Your first code was on a Listbox_Change event, which is why I asked if you were using a ListBox.

Now you are using a Worksheet_Change Event. can you explain exactly what you are doing?

[mperrah]

posted on vbaexpress
http://www.vbaexpress.com/forum/showthread.php?t=14013
I have a cell that shows data based on a start and stop date
using sumproduct.
I also pull pass or fail info from named range based also on the week range
I was using a vlookup on the worksheet but the users tend delete cells and I am forced to troubleshoot.
I can lock sheets, but some macros add and copy and paste, locking won't allow that.
So, I was working on the idea of having any cell based formula to run in vba and avoid the need to use cells to store a formula or the variables...

The socond reason for vba over cell based is to make the form work at different office with different data, having dynamic code can scan for used cells and update automaticaly.
I haven't used combo or list boxes and am still new to data validation list.
The data list was working but trying to use vba for the vlookup posed a challenge. I got the combobox to trigger the sub, but not sure how to make the combobox list in the sheet (not in a user-form), and how to update the combobox list if the cell data changes (source)
here is the function I have so far..
I call the function from a sheet change event.

Please Login or Register  to view this content.

[MrFox]

I'm not sure if you have solved this, but here's my 2 cents.
I had the same problem with being abe to detect the change event of a specific data validation list. Turns out that it's nearly just as easy to catch as a ListBox or Combo event.

Just name the data validation cell and check its address against the Target.Address in the Worksheet_Change event.

Please Login or Register  to view this content.

Hope this helps you or anyone else.

[mperrah]

I like the idea. I can capture the event, but not sure how to turn a vlookup into a vba code
=VLOOKUP(AD35,AJ7:AL67, 3)
this vlookup scans the data validation list in AD35,
the vlookup checks a pasted link to another page with the tech numbers.
The 3rd column has the match, then the chart updates.
I just need the code off the sheet to avoid deletion and have it dynamic if the source changes size,
or gets out of numerical order (my code fails if the techs aren't sorted)

this is how I started with your code...

Please Login or Register  to view this content.

[MrFox]

Not really sure how to implement the vlookup in VBA other than to hard code the process through iteration and offsets. I use this quite often. I'm sure there is a simpler or more elegant way to do it, but it works for me.

i.e.

Please Login or Register  to view this content.

[mperrah]

That was the trick,
I altered a little to match my names and sources and all is well in vba
Thank you so much.
Here is what I'm using to simulate a vlookup using vba

Please Login or Register  to view this content.

Mark