How to run a VBA procedure from a data validation control in Microsoft Excel
There are many ways to run a VBA procedure in Microsoft Excel. You can add sheet-level macro buttons or add them to the ribbon in a custom group. If you have multiple procedures, you might want to offer those choices in a data validation control. This allows you to select sheet-level tasks. You can use this technique to perform any number of tasks, from a simple storage task to something more complex like copying dynamic ranges or running advanced filters.
In this tutorial I will 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 provide full instructions, but you should have a basic knowledge of Excel. In this tutorial I will use the term “procedure” instead of “macro”. Technically, macros and procedures are not the same thing, but you’ll find that the terms are 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 start by creating a data validation control and populating it with the names of a few tasks. Applying this to your own work, you can start by creating the event handler first. It doesn’t matter which way you take.
We’ll add two items to the drop-down list, but you can add many more. Now let’s insert the data validation control. Use the simple demonstration sheet shown in Figure A, click B2. Feel free to use one of your own files if you’d like, or choose a different cell. You need to update the actual VBA code accordingly. I added formatting to help users find the control quickly.
Figure A
Click the Data tab, and then click Data Validation in the Data Tools group. In the resulting dialog box, select Allow from the drop-down list. Enter into source control Say hello, say goodbye (Figure B). click OK.
Figure B
As you can see in it Figure C, the drop-down menu contains the “Tasks” you entered. There is no space before or after the comma separating the two items. The next step is to add the VBA event procedure, but before you do that, 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
Selecting any of the items in the drop-down list currently does nothing. We need to add the event handler that will be executed when you select one of the items in the dropdown list. First, select Editor in the Visual Basic group on the Developer tab to open the Visual Basic Editor (VBE). In the Project Explorer, double-click Sheet 1 on the left. We’ll use a sheet-level module since the control is on Sheet 1. You cannot access it from other sheets. Enter Listing A as shown in Figure D.
Listing A
Private Sub Worksheet_Change(ByVal Target As Range)
‘Activate the data validation control on Sheet1!B2 to run the procedure.
If Target.Address = “$B$2” then
Select Case Target
Case “Say Hello”
MsgBox “Hello”
Case “Say Goodbye”
MsgBox “Goodbye”
case otherwise
MsgBox “Something went wrong”
exit selection
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, do not paste it from this website. Instead, copy the code into a text editor, and then paste this code into the Sheet1 module. This will remove any phantom web characters that might otherwise cause errors.
VBA fires 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 will not notice anything.
When fired, the event handler examines the current cell (target). If it’s not B2, you don’t want to continue, and the IF statement stops the flow.
You can use the SELECT CASE statement to test a value for various conditions. In this case it checks the value of Target – that is B2. If the value matches the text “Say Hello”, a message box displays the word “Hello”. If the value of Target is Say Goodbye, a message box displays the word Goodbye.
The CASE ELSE clause is there just in case something else happens. It says “Something went wrong”. If you apply this to your own work, you might want to add more descriptive text or even run an error handler. By the way, this simple procedure has no error handling, so you should think about it when applying it to your own work.
Now that you know what to expect, let’s give it a try.
How to run event procedure using data validation control in Excel
Using the data validation control to run the event handler is the easy part. Just click on the drop down menu and select one of the items. Do this now and select 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. This is because the validation control enters the item into B2, and therefore the code can look for the selected item. Close the message box and try again. This time, select Say Goodbye to view the message shown in Figure F. Notice that the text in the formula bar reads “Say Goodbye.”
Figure F
Let’s try again. This time, delete the content. This triggers the event handler, which eventually evaluates 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 like the following are enabled:
- Click the File tab.
- In the left pane, click Options.
- Click Trust Center and then click Trust Center Settings.
- In the left pane, click Macro Settings.
- If necessary, click Disable VBA macros with notification options. This setting blocks operations but enables them on a case-by-case basis.
The code is intentionally simple, but if you apply the technique to your own work, the code will most likely become more complex. The focus is on setting up the data validation that triggers the event procedure. This is the part of this technique that you really need. It is simple and yet unknown.