How to run a VBA procedure from a data validation control in Microsoft Excel

There are lots of ways to execute a VBA procedure in Microsoft Excel. You can add macro buttons at the sheet level or add them to the ribbon in a custom group. When you have multiple procedures, you might want to offer those choices in a data validation control. Doing so lets you choose tasks at the sheet level. You can use this technique to run any number of tasks from a simple save task to something more complex such as copying dynamic ranges or running advanced filters.

In this tutorial, I’ll show you how to insert a data validation control that runs a VBA event procedure. If you’re not familiar with VBA don’t worry. I’ll supply complete instructions, but you should have basic Excel skills. Throughout this tutorial, I will use the term “procedure,” instead of “macro.” Technically, macros and procedures aren’t the same thing, but you will see the terms used interchangeably. Even Microsoft does it.

I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use earlier versions of Excel. Excel for the web doesn’t support VBA procedures.

SEE: How to start an Excel accounting system (TechRepublic Academy)

How to embed a data validation control in Excel

We’ll begin by creating a data validation control and populating it with the name of a few tasks. When applying this to your own work, you might start by creating the event procedure first. It doesn’t matter which route you take.

We’ll add two items to the dropdown list, but you can add many more. Now, let’s insert the data validation control. Using the simple demonstration sheet shown in Figure A, click B2. Feel free to use one of your own files if you like or choose another cell. You’ll have to update the actual VBA code accordingly. I added formatting so users can find the control quickly.

Figure A

Click the Data tab, and then, click Data Validation in the Data Tools group. In the resulting dialog, choose List from the Allow dropdown. In the Source control, enter Say Hello,Say Goodbye (Figure B). Click OK.

Figure B

As you can see in Figure C, the dropdown contains the “tasks” you entered. There’s no space before or after the comma that separates the two items. The next step is to add the VBA event procedure, but before you do, save the file as a macro-enabled file, if you’re using the .xlsx format.

Figure C

How to add the VBA procedures in Excel

Choosing either item in the dropdown does nothing right now. We need to add the event procedure that runs when you select one of the items in the dropdown. First, select the Editor option in the Visual Basic group on the Developer tab to open the Visual Basic Editor (VBE). In the Project Explorer to the left, double-click Sheet 1. We’re using a sheet-level module because the control is in sheet 1. You won’t have access to it from other sheets. Enter Listing A as shown in Figure D.

Listing A

Private Sub Worksheet_Change(ByVal Target As Range)

‘Enable data validation control in Sheet1!B2 to execute the procedure.

If Target.Address = “$B$2” Then

    Select Case Target.Value

        Case “Say Hello”

            MsgBox “Hello”

        Case “Say Goodbye”

            MsgBox “Goodbye”

        Case Else

            MsgBox “Something went wrong”

    End Select

End If

End Sub

Figure D

You can enter the code manually or import the downloadable .cls file. In addition, the procedure is in the downloadable .xlsm file. If you enter the code manually, don’t paste from this web page. Instead, copy the code into a text editor, and then, paste that code into the Sheet1 module. Doing so will remove any phantom web characters that might otherwise cause errors.

VBA triggers this event procedure every time you make a change in the sheet. That makes it a bit risky to use in a very busy worksheet—it can slow things down a bit. In this case, you won’t notice a thing.

When triggered the event procedure checks the current cell (Target). If it isn’t B2, you don’t want to continue, and the IF statement stops the flow.

The SELECT CASE statement lets you check a value for different conditions. In this case, it checks Target’s value—that’s B2. If the value equals the text “Say Hello,” a message box displays the word “Hello.” If Target’s value is “Say Goodbye,” a message box displays the word “Goodbye.”

The CASE ELSE clause is there just in case something else happens. It displays “Something went wrong.” When applying this to your own work, you’ll want to add more meaningful text or even execute an error-handling routine. By the way, this simple procedure has no error-handling, so you’ll want to give that some thought when applying this to your own work.

Now that you know what to expect, let’s try it.

How to execute the event procedure using the data validation control in Excel

Using the data validation control to execute the event procedure is the easy part. Simply click the dropdown, and choose one of the items. Do so now, and choose the first item Say Hello. You should see the message box shown in Figure E.

Figure E

Notice that the Formula bar displays the text of the selected item. That’s because the validation control enters the item into B2, and that’s why the code can check for the selected item. Close the message box, and try again. This time choose Say Goodbye to display the message shown in Figure F. Notice that the text in the Formula bar is “Say Goodbye.”

Figure F

Let’s try it one more time. This time, delete the contents. Doing so will trigger the event procedure, which will end up evaluating the CASE ELSE clause, which displays the message shown in Figure G.

Figure G

If the procedure doesn’t run, check your Trust Settings to make sure procedures are enabled as follows:

1. Click the File tab.
2. Click Options in the left pane.
3. Click Trust Center, and then, click Trust Center Settings.
4. In the left pane, click Macro Settings.
5. Click the Disable VBA macros with notification options if necessary. This setting blocks procedures but enables on a case-by-case basis.

The code is simple on purpose, but when you apply the technique to your own work, the code will most likely be more complex. The focus is the data validation setup, which triggers the event procedure. That’s the piece of this technique that you really need. It’s simple and yet not well known.