How to assign a macro to autorun when a particular list item is chosen?

**RatHat** · 06-13-2013, 11:11 PM

If you create a List using Data Validation, and you record several macro's to carry out different functions, how could you assign a macro to each of the values of the List choices so that it ran on list item select?

For example, I create Macro1 to change the format of a set selection of cells to Accounting, and another, Macro2, to format the same set selection of cells to Text.

I create a List that contains two values:

Accounting
Text

If I use the dropdown to choose Accounting, then on select it calls and executes Macro1

If I use the dropdown to choose Text, then on select it calls and executes Macro2

Attached is an example:

Example.xlsm

**Cofad** · 06-13-2013, 11:39 PM

Off the top of my head, you could have a macro run when that particular cell value changes. Depending on the value of the cell it is changed too, it could do whatever you needed it to.

Look at the example and let me know if you have questions!

-CoFad

**RatHat** · 06-14-2013, 02:46 AM

That works perfectly! Many thanks, this has solved one of my problems.

So to reiterate, a private subroutine needs to be created with the following code:

Please Login or Register  to view this content.

To do this, within the VBA editor, I would go to Menu item: Insert > Procedure

Then in the dialogue enter a Name, then ensure Type is set to Sub, and Scope set to Private.

What does the checkbox All Local variables as Statics do? And what effect would this have?

Once the Subroutine is created, I would need to enter the code in between the Private Sub and End Sub statements, where I could edit the code to suit the spreadsheet.

Is this correct?

**Cofad** · 06-14-2013, 04:08 PM

Pretty close. I wouldn't use the insert > procedure though.

For your needs, you need the code to run when the textbox is changed. Well, there's no way native way for excel to know when a cell has been changed, but it does know when anything on a worksheet is changed. So, you put that code in the work sheet change event and it will run when the sheet is changed.

You can get to the worksheet change event procedure by opening the vba editor, selecting the worksheet from the object browser and opening it, and then changing the procedure type from declaration to worksheet from the drop down on the left, and then selecting change from the drop down on the right.

That's a horrible explanation of worksheet event procedures, but do a google search and it'll help you understand.

HTH

-CoFad