Run a Macro from a Drop Down list [VBA]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two macro names Macro1 and Macro 2, however, you can easily add more if you like.
Event code makes this possible, it also allows you to open a workbook programmatically when another workbook is opened.
The animated picture below shows a Drop Down list containing two text strings, Macro1 and Macro2. When I select Macro1 in cell B2 a worksheet event code reads the selected value and runs a macro.
The example macros display a message box with the message Macro1 or Macro2 based on the selected value.
Watch this video where I explain how to run a macro from a drop down list
Below are detailed instructions on how I did it.
Create a Drop Down list
A Drop Down list is a Data Validation tool that lets you control what the user can enter in a cell, however, remember that the Drop Down list is not that great in restricting what the user can enter.
For example, you can easily copy a cell and paste it to the cell containing the Drop Down list and Excel won't even warn you.
- Select cell B2.
- Go to tab "Data" on the ribbon.
- Press with left mouse button on the "Data validation" button and a dialog box appears.
- Press with mouse on the Drop Down list below "Allow:" and select "List".
- Type your macro names in Source: field, separated by a comma.
- Press with left mouse button on OK button.
Where to put the event code?
Event code is VBA code that is triggered if a specific thing happens, for example, if a worksheet is selected or a cell is selected, however, there are many many more. The event code is triggered, in this example, if a cell value is changed in worksheet Sheet1.
Remember that the worksheet_change event works only if you put it in a worksheet module, here is how to access a worksheet module:
- Press with right mouse button on on the worksheet name located at the very bottom of your Excel screen and a menu shows up.
- Press with left mouse button on "View code" on that menu, see image below, and the Visual Basic Editor opens with the worksheet module visible for Sheet1.
- The Visual Basic Editor opens. Copy VBA event code below.
- Paste to worksheet module.
- Exit VB Editor (Alt + Q).
VBA Event code
'Event code, Target variable contains the cell or range that has been changed
Private Sub Worksheet_Change(ByVal Target As Range)
'Interesect method returns a range object of a rectangular intersection of two or more cell ranges
If Not Intersect(Target, Range("B2")) Is Nothing Then
'Select Case statment performs one out of several groups of statements based on a condition
Select Case Range("B2")
'If text in cell B2 is equal to Macro1 a macro named Macro1 is rund
Case "Macro1": Macro1
'If text in cell B2 is equal to Macro2 a macro named Macro2 is rund
Case "Macro2": Macro2
'Stops Select Case statement
End Select
End If
End Sub
Here is the first macro named Macro1, it shows a message box with text "Macro1".
Sub Macro1() MsgBox "Macro1" End Sub
The second macro named Macro2 shows a message box with text "Macro2".
Sub Macro2() MsgBox "Macro2" End Sub
These macros are located in a code module that you need to insert yourself.
Where to put macros?
- Press keyboard shortcut keys Alt + F11 to open the Visual Basic Editor.
- Press with left mouse button on "Insert" on the menu, see image above.
- Press with left mouse button on "Module" to insert a module to your workbook.
- Copy and paste macros to code window.
- Exit VB Editor and return to Excel.
Save your workbook with file extension *.xlsm.
What happens if the Drop Down list is overwritten?
As I mentioned at the beginning of this article Drop Down lists have a major disadvantage, it can easily be overwritten.
For example, copy another cell and paste it to the cell containing a Drop Down list and the Drop Down list is now gone without a warning.
The event code is not even returning an error message, you can, however, add an extra line to the event code that takes care of invalid values.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B2")) Is Nothing Then
Select Case Range("B2")
Case "Macro1": Macro1
Case "Macro2": Macro2
Case Else: MsgBox "Macro not available"
End Select
End If
End Sub
This will not prevent the Drop Down list from being overwritten, it will only return a message telling the Excel user that the value in cell B2 is not valid.
To prevent it from being overwritten you can use the code I found here: Restrict paste into dropdown cells
Drop down lists category
This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]
Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]
If then else statement category
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
This article explains how to hide a specific image in Excel using a shape as a button. If the user […]
This post Find the longest/smallest consecutive sequence of a value has a few really big array formulas. Today I would like to […]
Macro category
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
Excel categories
26 Responses to “Run a Macro from a Drop Down list [VBA]”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Thank you. This worked perfectly!
I know this is a little late, but THANK YOU VERY MUCH!!! I have been trying to do this a few different ways via the change event and your method has worked! Love that I can use named ranges as well!! Thanks again!
Excellent. Hats off to you..!!
This took care of the issue I had with Buttons being deleted when they were hidden and saved. Thank you so much as you saved some major time for me..
This worked perfectly for one row in my excel, however, I need to copy this same thing for multiple rows... how do I add to the code?
For example, I have a drop down list with 2 options, each performs a different macro on that same row. (When I recorded the macro, I used relative references in hopes that I could use this same macro on multiple rows...
How do I add this to the VBA Event code?
I need the Event cell, in the example above was B2, to happen in multiple cells in the same column, but only perform that macro on the row that the drop down was changed... is that clear?
Thanks for any help I can get!!
Carolyn,
Have you solved your problem yet? Because im running into the same problem!
Carolyn Vermeulen and Paul van Amelsvoort,
Try this workbook:
Run-a-macro-from-a-drop-down-listv2.xlsm
I need the Event cell, in the example above was B2, to happen in multiple cells in the same column, but only perform that macro on the row that the drop down was changed... is that clear?
Use Target address to perform the macro on the selected cell.
I want to create a macro to provide full details from other sheet based on selected name from a drill down cell
I.e. if I have 10 staff visiting around 100 companies per day and I want to select one staff then the full details of his visit came including date of visit, name of the company etc.
Thanks in advance
The macro's worked great from the drop down list. I wanted to hide certain rows for ease of use on a form. I tried adding a second drop down and vba change coding, but without success. I am new to vba, so perhaps did it incorrectly. So,instead of the second drop down list, I added a filter to the form, but when the filter is used, the lines hidden by the original macro unhide.
Is there a way to either run two different macro combinations from two different drop lists, or alternately use the filter feature in conjunction with one macro/drop down?
Thanks.
This was really simple and worked perfectly. I used "Yes" and "No" on the dropdown to run different macros. Many thanks.
Oscar, this works pretty well. I have added a "Select one option" that appears on top of the other ones; however, if you select an option and run the corresponding macro, the list shows the last option you selected, not the first one. What should I do to always show the first option (in my case "Select an option") even after selecting an option?
Bruno
This event code shows always the first option.
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("B2")) Is Nothing And Target.Value <> "Please select an option" Then Select Case Range("B2") Case "Macro1": Macro1 Case "Macro2": Macro2 End Select Range("B2") = "Please select an option" End If End SubThanks Oscar! It works perfectly.
Hi,
I am trying to use this code for various dropdown boxes on the same worksheet.
Background:
I am working KPI's for several departments that I would firstly like to filter by department name (this is what I used the above code for). Secondly, I would like another dropdown box in a different cell that enables the user to filter by month. However, I cannot seem to get this to work. The dropdown opens but does not run the macros.
Any help would be appreciated.
Many thanks,
Lee Wood
Have you tried the excel file attached to this post?
It might give a clue what is wrong with you workbook.
Make sure you spell the macro name correctly in your vba code
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("B2")) Is Nothing Then Select Case Range("B2") Case "Macro1": Macro1 Case "Macro2": Macro2 End Select End If End SubMacro names used in above code:
Macro1
Macro2
I presume your macros work if you run them?
Are the macros in the same workbook?
Did you put your code in a code module?
Hello Oscar,
I am running your code, exact same position exact same scripts, but once I change from dropdown list from Macro1 to Macro2 I get the next error: "Compile Error: Sub or Function not defined" .
Also what is the script once I chose the item from dropdown list to automaticly change a cell into what I want?
Thank you for everything Oscar.
I have just opened your xsl. file, and it works on your file, why doesn't work on mine? I use exact same position exact same script.
Does it have anything to do with the fact that I am on my third sheet?
Cristi
I am running your code, exact same position exact same scripts, but once I change from dropdown list from Macro1 to Macro2 I get the next error: "Compile Error: Sub or Function not defined" .
Does it have anything to do with the fact that I am on my third sheet?
Yes, make sure you have your event code on your third sheet. See blog post instructions below "Create a change event".
Dear Sir,
I tried your macro, it works perfect until reach the data validation source character limitation. Do you have any suggestion for limitless source macro source
Thanks in advance
Hi there,
First of all thx for the post. I find it very usefull! I works fine in my case. However, when I extend the range to various cells (all of them with the same drop down list), a "Run-time error ‘13’: Type mismatch" pops up from the VBA editor.
Any suggestion on how to solve it?
Thx in advance!!
German
German
Which line in the VBA code is highlighted when this error shows up?
Perhaps this explains why:
https://excelmacromastery.com/vba-type-mismatch/
When I use this with a Number List - Each time I select 0 I can't use the drop down menu. It keeps running the same Macro over and over. I can't even select the cell to change it.
When I comment out your code - I can do anything.
Try making sure that you've specified "worksheet" and "change" in your Sheet1 Code dropdowns. I had the same problem, and it was because I had "sheet change" on the right dropdown instead of "change"
Well Explained.Immensely benefited.Thanks a lot Oscar
I have 2 Dropdown List with the same options (Included and Excluded) in different sheets. If I select " Included" in one dropdown list, it should also select "Included" on another dropdown list and vice versa. I need VBA coding for this to execute
Hi! I tried the Macro, and it worked when I used Macro 1/ Macro 2.
I tried to implement it for my codes though, and it did not work. The event is in Sheets, the Macros in Modules. But I change the values of the dropdown list and it does not work. Have I done something wrong in the code?
Private Sub Wywolanie(ByVal Target As Range) 'do przebadania/ miasta powinno tu być
If Not Intersect(Target, Range("H15")) Is Nothing Then
Select Case Range("H15")
Case "Bialystok": Call Raporty_Miast
Case "Katowice": Call Raporty_Miast
Case "Kilece": Raporty_Miast
Case "Koszalin": Raporty_Miast
Case "Krakow": Raporty_Miast
Case "Lodz": Raporty_Miast
Case "Lublin": Raporty_Miast
Case "Opole": Raporty_Miast
Case "Poznan": Raporty_Miast
Case "Rzeszow": Raporty_Miast
Case "Sopot": Raporty_Miast
Case "Warszawa": Raporty_Miast
Case "Wroclaw": Raporty_Miast
Case "Zielona Gora": Raporty_Miast
End Select
End If
End Sub
I would appreciate any help! Thank you!
Okay, I found out,
the name of the macro has to be exactly
Private Sub Worksheet_Change(ByVal Target As Range)
this worked