How To Create A Drop Down List In Vb Net

Author: Oscar Cronquist Article last updated on August 30, 2021

Run a macro from a drop down list

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.

Run a macro from a drop down list

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?

Run a macro from a drop down list 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).

Note, save the workbook with file extension *.xlsm (macro enabled) to attach the code to your workbook. This will save the workbook and the code so you can use it the next you open the workbook.

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?

Run a macro from a drop down list VBA code

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?

Run a macro from a drop down list drop down 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