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 SubHere is the first macro named Macro1, it shows a message box with text "Macro1".
Sub Macro1[] MsgBox "Macro1" End SubThe second macro named Macro2 shows a message box with text "Macro2".
Sub Macro2[] MsgBox "Macro2" End SubThese 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 SubThis 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
Get the Excel file
Run-a-macro-from-a-drop-down-list.xlsm
Weekly Blog EMAIL
Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
Change chart data range using a Drop Down List [VBA]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox [drop-down list]. The above []
Add or remove a value in a drop down list programmatically
This article demonstrates how to add or remove a value in a regular drop down list based on a list []
Excel calendar [VBA]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The []
Show / hide a picture using a button
This article explains how to hide a specific image in Excel using a shape as a button. If the user []
How to create a list of comments from a worksheet programmatically
Did you know that you can select all cells containing comments in the current sheet? Press F5, press with left []
Find the most/least consecutive repeated value [VBA]
This postFind the longest/smallest consecutive sequence of a value has a few really big arrayformulas. Today I would like to []
Create dependent drop down lists containing unique distinct values
This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We []
Populate drop down list with unique distinct values sorted from A to Z
Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using []
Apply dependent combo box selections to a filter
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. []
Excel calendar [VBA]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The []
Show / hide a picture using a button
This article explains how to hide a specific image in Excel using a shape as a button. If the user []
Save invoice data [VBA]
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies []
Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The []
Split data across multiple sheets [VBA]
In this post I am going to show how to create a new sheet for each airplane using vba. The []
Comments [23]
23 Responses to Run a Macro from a Drop Down list [VBA]
- Lisa R. says:May 21, 2014 at 4:58 pm
Thank you. This worked perfectly!
Reply - Sam says:June 9, 2014 at 5:22 am
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!
Reply - Isuru says:August 18, 2014 at 8:29 pm
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..Reply - Carolyn Vermeulen says:September 19, 2014 at 4:34 pm
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!!
Reply- Paul van Amelsvoort says:October 9, 2014 at 8:23 am
Carolyn,
Have you solved your problem yet? Because im running into the same problem!
- Oscar says:October 10, 2014 at 9:05 am
Carolyn Vermeulen and Paul van Amelsvoort,
Try this workbook:
Run-a-macro-from-a-drop-down-listv2.xlsmI 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.
- Mohamed Zaher says:April 12, 2016 at 7:08 am
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
Reply - Rose says:April 13, 2016 at 9:10 pm
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.Reply - James says:October 19, 2016 at 2:15 pm
This was really simple and worked perfectly. I used "Yes" and "No" on the dropdown to run different macros. Many thanks.
Reply - Bruno says:November 4, 2016 at 4:56 pm
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?
Reply- Oscar says:November 10, 2016 at 10:12 am
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 Sub
- Bruno says:November 15, 2016 at 3:36 pm
Thanks Oscar! It works perfectly.
Reply - Lee Wood says:February 2, 2017 at 10:52 am
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,
Reply- Oscar says:February 2, 2017 at 11:43 am
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
Macro2I presume your macros work if you run them?
Are the macros in the same workbook?
Did you put your code in a code module?
- Cristi says:April 20, 2017 at 4:34 pm
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.
Reply- Cristi says:April 20, 2017 at 4:37 pm
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? - Oscar says:April 20, 2017 at 7:52 pm
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".
- Ahmet says:September 23, 2019 at 1:25 pm
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
Reply - German says:November 1, 2019 at 7:32 pm
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!!
GermanReply- Oscar says:November 6, 2019 at 8:59 am
German
Which line in the VBA code is highlighted when this error shows up?
Perhaps this explains why:
//excelmacromastery.com/vba-type-mismatch/
- Rob says:April 11, 2020 at 2:35 am
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.
Reply - MohanKumar says:August 15, 2020 at 5:50 pm
Well Explained.Immensely benefited.Thanks a lot Oscar
Reply - Daljeet says:May 16, 2021 at 2:59 pm
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
Reply
Leave a Reply
Name [required]
Mail [will not be published] [required]
Website
Δ
How to comment
How to add a formula to your commentInsert your formula here.
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.
Contact Oscar
You can contact me through this contact form