There are many ways to perform 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. If you have multiple procedures, you may want to present these choices in a data validation check. Doing so allows you to choose worksheet-level tasks. You can use this technique to perform any number of tasks, from a simple save task to something more complicated, such as copying dynamic ranges or running advanced filters.
In this tutorial, I’ll show you how to insert a data validation control that executes a VBA event procedure. Don’t worry if you are not familiar with VBA. I will give full instructions, but you must have basic skills in Excel. In this tutorial I will use the term ‘procedure’ instead of ‘macro’. Technically, macros and procedures are not the same, but you will see the terms used interchangeably. Even Microsoft does it.
I use Microsoft 365 on a Windows 10 64-bit system, but you can use earlier versions of Excel. Excel for the web does not support VBA procedures.
TO SEE: How to Start an Excel Accounting System (TechRepublic Academy)
Embedding a data validation control in Excel
We’ll start by creating a data validation control and populate it with the name of a few tasks. If you apply 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 control. Using the simple demonstration sheet shown in Image A, click on B2. Feel free to use any of your own files if you like or choose a different cell. You need to update the actual VBA code accordingly. I’ve added formatting to help users find the control quickly.
Image A
Click the Data tab, and 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 control, 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 separating the two items. The next step is to add the VBA event procedure, but before doing this save the file as a macros file, if you are using the .xlsx format.
Figure C
How to add the VBA procedures in Excel
Choosing either item from the drop-down list now does nothing. We need to add the event procedure that runs when you select one of the items in the drop-down list. 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 on the left, double-click Sheet 1. We’re using a sheet-level module because the control is in sheet 1. You cannot access it from other sheets. Enter Advertisement A as shown in Figure D.
Advertisement A
Private Sub Worksheet_Change (ByVal target as scope)
‘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”
Case Other
MsgBox “Something went wrong”
Select end
End if
end sub
Figure D
You can enter the code manually or import the downloadable .cls file. In addition, the procedure is contained in the downloadable .xlsm file. If you enter the code manually, do not paste it from this web page. Instead, copy the code into a text editor and then paste that code into the Sheet1 module. Doing this will remove all ghost web characters that might otherwise cause errors.
VBA triggers this event procedure every time you make a change to 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 anything.
When activated, 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.
You can use the SELECT CASE statement to check a value for several conditions. In this case it checks the value of Target – that is B2. If the value equals the text “Say hello,” a message box displays the word “Hello.” If the value of Target is “Say Goodbye”, a message box will display the word “Goodbye”.
The CASE ELSE clause is there in case something else happens. It shows “Something went wrong”. Applying 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 should think about that if you apply it to your own work.
Now that you know what to expect, let’s give it a try.
How to run the event procedure using the data validation checker in Excel
Using the data validation checker to run the event procedure is the easy part. Simply click on the drop-down list and choose one of the items. Now do this and choose the first item Say hello. You should see the message box that appears in Digits E.
Digits E
Note that the formula bar displays the text of the selected item. That’s because the validation check enters the item into B2, and therefore the code can check the selected item. Close the message box and try again. This time, choose Goodbye to see the message displayed in Figure F. Note that the text in the formula bar is “Say Goodbye”.
Figure F
Let’s try again. This time delete the content. Doing so triggers the event procedure, 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 the procedures are enabled as follows:
- Click the File tab.
- Click Options in the left pane.
- 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 procedures, but allows this on a case-by-case basis.
The code is simple on purpose, but if you apply the technique to your own work, the code will most likely be more complex. The focus is on the data validation setting, which triggers the event procedure. That’s the piece of this technique you really need. It is simple and yet not so well known.
