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.

1. Run a Macro from a Drop Down list - VBA

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.

1. Select cell B2.
2. Go to tab "Data" on the ribbon.
3. Press with left mouse button on the "Data validation" button and a dialog box appears.
   
4. Press with mouse on the Drop Down list below "Allow:" and select "List".
5. Type your macro names in Source: field, separated by a comma.
6. 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:

1. Press with right mouse button on on the worksheet name located at the very bottom of your Excel screen and a menu shows up.
2. 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.
   
3. The Visual Basic Editor opens. Copy VBA event code below.
4. Paste to worksheet module.
   
5. 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?

1. Press keyboard shortcut keys Alt + F11 to open the Visual Basic Editor.
2. Press with left mouse button on "Insert" on the menu, see image above.
3. Press with left mouse button on "Module" to insert a module to your workbook.
4. Copy and paste macros to code window.
5. 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

Get the Excel file

Run-a-macro-from-a-drop-down-list.xlsm

2. Toggle a macro on/off using a button

This section demonstrates how the user can run a macro by press with left mouse button oning on a button, the text on the button changes based on what the macro has performed. The macro itself reads the button and runs code based on that text.

The animated image above shows a macro that hides and shows checkboxes using only a button. The text on the button shows if they are visible or invisible.

You will in this section learn how to:

• Insert a button (Form Control) on the worksheet
• Change the size of the button.
• Identify button name
• Create a macro
• Manipulate the text on a button programmatically.
• Read button text to determine what VBA lines to run.
• How to start another macro within a macro.
• Assign a macro to a specific button.
• Save the workbook as a macro-enabled workbook.

How to insert a button?

To create a button go to tab "Developer" and press with left mouse button on "Insert" button. Press with mouse on the "button" button and then press and hold with left mouse button on the worksheet.

Now drag with the mouse to create the button, lastly release the left mouse button and the button is now visible on your worksheet.

This allows you to control the size of the button. Don't worry, you can adjust the size later if you didn't get it right.

How to determine the name of a button?

In order to manipulate the button text, you need to know the name of the button. Simply select the button you just created and read the name box.

The dots around the button demonstrated in the picture above tells you that the button is selected, press and hold on one of the dots to change the size of the button.

The name box is located to the left of the formula bar, it contains text "Button 1" without the double quotes in the example image above.

How to toggle button text when you press with left mouse button on the button?

The macro below changes the text shown on "Button 1", this lets you create the toggle effect. It also guides the macro so it knows what lines of code to run.

Make sure you assign this macro to the button you just created, however, first you need to copy the code shown below to a regular code module.

VBA Macro

'Name of macro
Sub ToggleMacro()

'Simplify code by refering to object once
With ActiveSheet.Shapes("Button 1").TextFrame.Characters

'Check if button text is equal to a specific string.
If .Text = "Checkboxes: On" Then

    'Change button text.
    .Text = "Checkboxes: Off"

'This happens if button text is not equal to the specific string.
Else

    'Change button text.
    .Text = "Checkboxes: On"
End If

End With

End Sub

Where to put the VBA code?

1. Copy VBA code above.
2. Press short cut keys Alt+F11 to open the VB Editor.
3. Press with left mouse button on "Insert" on the top menu.
4. Press with left mouse button on "Module".
5. Paste code to module.
6. Return to Excel.

How to assign a macro to a specific button?

1. Press with right mouse button on on the button and a context menu appears.
2. Press with mouse on "Assign Macro..." and the following dialog box shows up.
   
3. Select the macro you want to assign.
4. Press with left mouse button on "OK" button.

The selected macro is rund every time the user press with left mouse button ons on the button.

How to toggle checkboxes?

There are two different macros being used that add and delete checkboxes demonstrated here: Add checkboxes to a sheet (1/2)(VBA). Those two macros have to be added to a code module in your workbook in order to make this work.

The macro code below shows how to determine which macro to run by reading the button text displayed on "Button 1". It also shows that the macro is unnecessary long because I am not using the With - With End statement.

VBA Macro

Sub Button1_Press with left mouse button on()

If ActiveSheet.Shapes("Button 1").TextFrame.Characters.Text = "Checkboxes: On" Then
    ActiveSheet.Shapes("Button 1").TextFrame.Characters.Text = "Checkboxes: Off"
    Call RemoveCheckboxes
Else
    ActiveSheet.Shapes("Button 1").TextFrame.Characters.Text = "Checkboxes: On"
    Call Addcheckboxes
    ActiveSheet.Range("A1").Select
End If

End Sub

Get the Excel file

Toggle-macro-on-off.xlsm

3. Use a drop down list to display data in worksheet header - VBA

Answer

Yes, it is possible. The animated image above demonstrates a worksheet that allows the user to select a value using a drop-down list and the header changes accordingly. The header and footer are located above and below the cell grid respectively when you print a worksheet.

The drop-down list is a regular drop-down list located in cell C2, the company name and address populates the left header. The zip code and city name populate the center header and the image is displayed on the right header.

If you get the workbook and the images are not showing then you need to create images and save them to c:\temp\ folder or change the paths and file names in column F worksheet Data to the images you want to use.

The following VBA code is event code that is saved to a worksheet module and not a regular module. Adjust cell reference A1:A4 below so it points to all of your data set values.

Event code

The event code below matches the selected company name in the drop-down list to the correct record on sheet "Data", the event macro then saves the row number of that record to a variable and uses that number to extract each value from the record.

Lastly, it saves the data to the header. The header contains three containers, left, center and right. Char(10) is the character for a new line, it separates the company name and address in the left container. It is also used in the center container to put zip code and email address on a line each.

'This event code is rund when a cell value is changed.
Private Sub Worksheet_Change(ByVal Target As Range)

'Dimension variables and data types
Dim iRow As Single

'Check if target is cell C2
If Not Intersect(Target, Range("C2")) Is Nothing Then

   'Enable error handling
   On Error Resume Next
  
   'Save row number of matching cell in cell range A1:A4 to variable iRow
   iRow = Application.Match(Range("c2"), Worksheets("Data").Range("A1:A4").Value, 0)
   
   'Check if there has been an error, MATCH function returns an error if no value is found
   If Err <> 0 Then

      'Show a message box
      MsgBox "Company name not found!"

      'Stop event code
      Exit Sub
   End If

   'Disable error handling
   On Error GoTo 0

'Save text and picture from data table to header
   With ActiveSheet
  
      'Save company name and address to left header on active worksheeet
      .PageSetup.LeftHeader = Worksheets("Data").Range("A" & iRow).Text & Chr(10) & _
      Worksheets("Data").Range("B" & iRow).Text

      'Save zip code and email address to center header on active worksheeet
      .PageSetup.CenterHeader = Worksheets("Data").Range("C" & iRow).Text & " " & _
      Worksheets("Data").Range("D" & iRow).Text & _
      Chr(10) & Worksheets("Data").Range("E" & iRow).Text
      
      'Save picture to right header on active worksheeet
      .PageSetup.RightHeaderPicture.Filename = Worksheets("Data").Range("G" & iRow).Text
   End With
End If
End Sub

Where to put the code?

1. Copy code above.
2. Press with right mouse button on on sheet name.
   
3. Press with left mouse button on "View code" to open the worksheet module in the VB Editor.
   
4. Paste code to worksheet module.
5. Exit vba editor and return to Excel.

Data source

The data set used to populate the header is located on worksheet "Data", the image above shows what the geted file will contain. Column A contains the company names.

Column B contains addresses, column C zip codes, column D city names, column E email addresses and column F contains folder paths and file names to company logos.

Final thoughts

I recommend that you use an Excel defined Table on sheet Data, it allows you to reference only the Table name and table header name. You don't need to adjust cell references when you add records, the Excel defined Table is dynamic.

For example, the following row:

iRow = Application.Match(Range("c2"), Worksheets("Data").Range("A1:A4").Value, 0)

becomes

iRow = Application.Match(Range("c2"), Worksheets("Data").Range("Table1[Company Name]").Value, 0)

Get the Excel file

Dynamic-header.xlsm

Drop down lists category

Create dependent drop down lists containing unique distinct values
Table of Contents Create dependent drop down lists containing unique distinct values - Excel 365 Create dependent drop down lists […]

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 […]

How to use an Excel Table name in Data Validation Lists and Conditional Formatting formulas – use named ranges
This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]

If then else statement category

Excel calendar
Table of Contents Excel monthly calendar - VBA  Calendar Drop down lists Headers Calculating dates (formula) Conditional formatting Today Dates […]

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 […]

Working with comments – VBA
Did you know that you can select all cells containing comments in the current sheet? Press F5, press with left […]

Macro category

Excel calendar
Table of Contents Excel monthly calendar - VBA  Calendar Drop down lists Headers Calculating dates (formula) Conditional formatting Today Dates […]

How to create an interactive Excel chart
Table of Contents How to create an interactive Excel chart How to filter chart data How to build an interactive […]

Split data across multiple sheets – VBA
Table of Contents Split data across multiple sheets - VBA Add values to worksheets based on a condition - VBA […]

Excel categories

Comments (31)