There are many ways to run 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 provide those choices in a data validation check. This lets you choose tasks at the sheet level. You can use this technique to perform any number of tasks, from a simple recording task to something more complex, such as copying dynamic ranges or performing advanced filters.
In this tutorial, I’ll show you how to insert a data validation control that executes a VBA event procedure. If you’re not familiar with VBA, don’t worry. I will provide full instructions, but you must 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’ll see the terms used interchangeably. Even Microsoft does it.
I’m using Microsoft 365 on a 64-bit Windows 10 system, but you can use older versions of Excel. Excel for the web does not support VBA procedures.
SEE: How to start an Excel accounting system (TechRepublic Academy)
How to embed a data validation check in Excel
We’ll start by creating a data validation control and populating it with the names of a few tasks. When applying this to your own work, you can start by creating the event procedure first. It doesn’t matter which route 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 check. Using the simple demonstration sheet shown in Figure A, click B2. Feel free to use one of your own files if you wish or choose another cell. You will need to update the actual VBA code accordingly. I added some formatting so users can find the control quickly.
Figure A
Click the Data tab, then click Data Validation in the Data Tools group. In the resulting dialog box, choose List from the Allow drop-down list. In the Source field, enter Say hello, say goodbye (Figure B). Click OK.
Figure B
As you can see in Figure C, the drop-down list contains the “tasks” you entered. There is no space before or after the comma that separates the two elements. The next step is to add the VBA event procedure, but before that save the file as a macro-enabled file, if you are using .xlsx format.
Figure C
How to Add VBA Procedures in Excel
Choosing either item from the drop-down list does nothing at this time. We need to add the event procedure that runs when you select one of the items from the dropdown. First, select the Editor option from the Visual Basic group on the Developer tab to open the Visual Basic Editor (VBE). In the Project Explorer on 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. Walk in List A as shown on the Figure D.
List A
Private Sub-Worksheet_Change(ByVal Target As Range)
‘Enable data validation check in Sheet1!B2 to run the procedure.
If Target.Address = “$B$2” Then
Select Case Target.Value
Case “Say hello”
MsgBox “Hello”
Case “Say goodbye”
MsgBox “Goodbye”
Other case
MsgBox “Something went wrong”
End of selection
End if
End caption
Figure D
You can enter the code manually or import the downloadable .cls file. Additionally, the procedure is in the downloadable .xlsm file. If you enter the code manually, do not paste it from this webpage. Instead, copy the code into a text editor, and then paste that code into the Sheet1 module. This will remove any ghost web characters that might otherwise cause errors.
VBA triggers this event procedure whenever you make a change to the sheet. This makes it a bit risky to use in a busy spreadsheet – it can slow things down a bit. In this case, you will not notice anything.
When triggered, the event procedure checks the current cell (Target). If it’s not B2, you don’t want to continue and the IF statement stops the flow.
The SELECT CASE statement allows you to check a value for different conditions. In this case, it checks the value of Target, i.e. B2. If the value is equal to the text “Say Hello”, a message box displays the word “Hello”. If the Target 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 you apply this to your own work, you’ll want to add more meaningful text or even run an error handling routine. By the way, this simple procedure has no error handling, so you’ll want to think about that 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 check to run the event procedure is the easy part. Simply click on the drop-down menu and choose one of the items. Do it now and choose the first Say Hello item. You should see the message box displayed in Figure E.
Figure E
Note that the formula bar displays the text of the selected item. This is because the validation check enters the item into B2, and that’s why the code can check the selected item. Close the message box and try again. This time, choose Say goodbye to display the message shown in Figure F. Note that the text in the formula bar is “Say goodbye”.
Figure F
Let’s try one more time. This time delete the content. This will trigger the event procedure, which will eventually evaluate the CASE ELSE clause, which displays the message shown in G-figure.
G-figure
If the procedure does not run, check your trust settings to make sure the procedures are enabled as follows:
- Click on the File tab.
- Click Options in the left pane.
- Click Trust Center, then click Trust Center Settings.
- In the left pane, click Macro Settings.
- Click on options to disable VBA macros with notification if needed. This setting blocks procedures but allows on a case-by-case basis.
The code is intentionally simple, but when you apply the technique to your own work, the code will likely be more complex. The focus is on configuring data validation, which triggers the event procedure. This is the part of this technique that you really need. It’s simple and yet little known.
