Macro to select Validation List & allowing Worksheet_change to operate

[jordan2322]

Hi Guys,

I was wondering whether it was possible to have a macro run, select the first option from a validation list - this starts another macro in worksheet_Change.
When Worksheet_Change macro finishes then select the next option from the validation list. which will in turn cause worksheet_Change to run.
etc etc for all possible options in the validation list.

When the validation list changes this causes rows to hide/show on all other worksheets in the WB.
My goal is to create a macro that will select the first option from this validation list, let the Worksheet_Change macro run and hide/show the rows, then export the workbook.
Then repeat this for all values found in the validation list

regards

[TMS]

Simple answer: yes, it's possible. You just loop through the entries in the DV list, dropping each value into the cell, taking whatever action is required and doing a SaveCopyAs.

Detailed answer: you'll need to provide a lot more detail including a sample workbook, bullet points of the action(s) required, any constraints, etc.

Regards, TMS

[jordan2322]

WB attached. ive supplied some notes / explanations in this attached WB

thanks a heap

[JBeaucaire]

CYCLE THROUGH DV OPTIONS
Here's a basic macro for cycling through all the Data Validation options in a certain cell. The sample then prints out the workbook, but you can replace that line of code with whatever action you prefer, this should give you the basics.

Cycle Through Data Validation Options

[jordan2322]

Thank you JBeaucaire!

This is crashing on the line

Please Login or Register  to view this content.

an example of myDVList is 'Red,Blue,Green Trees'
not sure why this may be causing the issue

Just a quick note (and please see the attached WB to confirm this) I am automatically generating the DVList using the worksheet_activate function.
Im not sure if this would throw of your supplied code because of this?

thanks again!

[JBeaucaire]

Ah, this method assumes the DV list is a range of cells, not a builtin list. Hmm...

[jordan2322]

Is there any other methods to this if its a 'built in list' ?

[JBeaucaire]

Ok, I've updated the published page to add in that capability into the basic macro.