In my last post, I talked about methods of validating a U.S. state using Excel’s data validation feature. At that end of that post, I suggested that you could add more sophisticated validation by writing “a small bit of Visual Basic code.”
Jokes on me. When I sat down to write about that “small bit” of code, I realized that it’s a lot more involved than I realized. It’s something I often do, so it seems easy to me. But for those of you with little VBA experience, it’s a lot.
So, rather than get into the specifics of validating a state, I’m going to cover the infrastructure that you can add to VBA to facilitate any type of validation.
Why?
Why would you want to add validation using VBA? Good question. Excel is already capable of performing quite a bit of validation on your worksheet without having to write VBA code. So why bother?
Because you can do more with VBA. Here’s an example:
I’ve got an Excel spreadsheet where users are typing in mailing addresses. I want to perform the following validations on the State column:
Must be exactly two characters
Must be letters only
Must be upper case
I could accomplish all of these validations in Excel without code, but there would be some short-comings that I won’t go into here. Read my previous blog for one example.
These tasks are all simple in VBA. What’s more, I can add additional features. Let’s say, for example, that I have some special requirements when shipping items to California. I can have VBA code that triggers a warning message whenever the state is “CA”.
What I’m going to talk about in this post is setting up a structure that will support VBA validation. Once this structure is established, you’ll be able to add any validation you want, usually with very little code. I’m calling this a “Validation Harness” – code that you can plunk into any Excel file, and customize with applicable validations.
Let’s see how to do this.
Event Procedures
The first thing we need to talk about is something called “Event Procedures”. If you already know what they are, feel free to skip to the next section.
Event procedures are bits of Visual Basic code that will get executed automatically when certain actions are taken in Excel. There’s an event called “Workbook_Open”, for example. As the name implies, this procedure will execute every time the Excel file (a.k.a. “Workbook”) is opened. The workbook also has a BeforeClose event, an Activate event, and a Deactivate event, among others.
Each worksheet also has event procedures. Two of the ones we’ll be looking at are the Change event and the SelectionChange event. The “Change” event takes place whenever you change text in any cell. There are many scenarios that cause this, including:
When you type a value and press tab
When you type a value and press Enter
When you paste a value into a cell
When you type a value and press Ctrl-Enter
When you type a value and click the "Enter" button on the formula bar
When VBA code (or a macro) changes the value in a cell
When you erase the value in a cell by pressing Delete on your keyboard
When you undo a cell change
When you use the "Clear Contents" button on the Home ribbon
There are probably other scenarios that will cause this event to fire, but the key to today’s discussion is to know that whenever you make a change in a cell, this Change event will fire.
How does the “SelectionChange” event differ? This event fires whenever you move from one cell to another. Some scenarios that cause this are:
You press tab, moving to the next cell to the right
You press enter, moving to the next cell down
You use your mouse to click on a different cell
You use any arrow key on your keyboard to move right, left, up or down
VBA code (or a macro) activates a different cell than the one that is currently active
Note that in this case, it doesn’t matter if a cell’s value has changed or not. The act of moving from one cell to another causes this event to fire, even if the value of the cell has not changed.
Also note that you often get both events firing one after the other. If I type a value in a cell and press tab, I’ll first get a “Change” event, and then a “SelectionChange” event.
Bet you didn’t know that Excel was such a busy bee, did you? There’s a lot going on behind the curtain.
Arguments
So, on to our problem. We’d like to be able to add some validation to our Excel sheet. If you were thinking ahead, you probably realized that you can utilize the Change or SelectionChange events to perform your validation. Let’s take a look at these two event procedures.
To see them, go the Visual Basic window, and view your Project Explorer window. If you’re not sure how to do those things, google will help you. In Project Explorer, you’ll see a module for each worksheet in your file. Double-click one of those to view its code window.
Once you’re in the code window, use the two drop-down boxes at the top to select “Worksheet” and “SelectionChange”. Repeat for “Change”:
We now have the skeleton of the two event procedures in our code window.
Take a look at the argument for each, which is inside the parenthesis. They both have a variable called “Target” with data type “Range”. Every time these event procedures get executed, the “Target” variable will automatically contain a reference to the cell in question.
For the “Change” event procedure, the “Target” variable is always a reference to the cell whose value has been changed.
For the “SelectionChange” event, “Target” always refers to the cell that has become active. If cell A3 was active, and you pressed tab, “Target” would contain a reference to cell A4.
Let’s Try Validating with the Change Event
I’ve got a simple worksheet that contains vendor data, including the state. It looks like this:
I’m going to add a simple validation to the Change event to see if my State is 2 characters long:
My code checks to see if the Target is in column 2 (a.k.a. column B), and if so, it checks the length of the value. If it’s not 2 characters, I display an error message.
So far, so good. But in testing it, I find a problem. After I type in the invalid value and press tab, the error message pops up, but the program lets me continue on my way, without correcting the problem. You can see in the above screen shot that the active cell has already been moved to column C. Once I click “OK”, I can continue on my way without fixing the problem:
The solution seems obvious. After displaying this error messages, I’ll simply move back to the cell that’s causing the problem, so that the user has to type in a new value before continuing. My revised code looks like this:
This is better, but still not quite right, because if I simply press tab without fixing the problem, there’s no further error message. The program still allows me to continue on my way without fixing the state.
Why? Because the Worksheet_Change event only fires if I change the value in the column. If I simply tab away from the cell without changing it, there is no validation.
Well, the SelectionChange Event Then
You might think you can just change gears and use the SelectionChange event instead of the Change event. Remember that SelectionChange takes place whenever you move from one cell to another, weather that’s because you pressed tab or return, or because you clicked the mouse on a different cell.
In the scenario we’ve been discussing, that seems perfect, because it’ll execute when I tab off of the State column, whether it’s been changed or not. Let’s try it. I’ll move my validation code to the SelectionChange event, like this:
The problem we’re going to have now is that the Target variable is going to refer to the wrong cell.
Let’s consider some examples:
You are positioned on cell B3 (the state), and you press tab. The “Target” variable refers to cell C3 (the newly activated cell)
You are positioned on cell B3 and you press return. The “Target” variable refers to cell B4.
You are positioned on cell B3 and you click cell E10. The “Target” variable refers to cell E10.
In all of those cases, you can see that the Target variable has nothing to do with the cell we were previously on. Which means we don’t know which cell needs to be validated.
So, What’s the Solution?
There are many ways you can deal with this.
Option 1: Workbook_BeforeClose
You could add code to the “Workbook_BeforeClose” event which would loop through all rows, and validate the state on each. If you find any errors, you can highlight them, and then cancel the close operation. The user would then have to correct any errors before closing the file.
I like that this method prevents the user from closing without correcting, but it can be frustrating for the user. Let’s say they entered 30 new vendors, and there were mistakes on several of them. Instead of getting notified on each row, they now may have an overwhelming number of corrections to make. Generally, it’s more user friendly to tell them about mistakes as soon as they’re made.
Another concern with this method is that it requires validation of rows that have not been changed. If your list has hundreds of rows, you will now be requiring the user to wait while you validate all rows. How patient are your users?
Option 2: Live with the Change Event
Back when we had the code in the Change event, the error message was displaying at the appropriate time, but the user was not forced to fix the issue before moving on. This is a user-friendly solution, and can work just fine if you trust your users to make the corrections.
My issue with this method is that it’s very common for people to dismiss an error before they’ve actually read it. If that happens, there’s no visual cue for them to correct the value. This might be acceptable in some circumstances, if the stakes are low. But if you need to be sure that the value gets filled in correctly, this might not work.
Option 3: Add code to track which cell is being changed.
In this scenario, we’re going to add a little big of code that will keep track of which cell is being changed, and then use the SelectionChange event to act on that cell. I’ll start by adding this code:
I’ve set up a variable called “LastCellChanged”. Every time a cell is changed, I set that variable to the cell.
Next I’ll edit the code in the SelectionChange event to use this new variable:
The first “if” statement catches the scenario when you open the file, and then switch to a different cell without first changing the value in any cell. In this case, the “Worksheet_Change” procedure won’t fire, and therefore, the “LastCellChanged” variable will have no value. In that case, I’m skipping the validation.
When I test this change, I find that the error message is displayed twice in a row. Why? It’s because I’m changing the active cell with my VBA code. That last statement in the If statement – the one that says “LastCellChanged.Activate” – re-activates that cell that was just changed, which triggers another SelectionChange Event. In other words, the procedure is recursive.
To correct this, I’m going to add one additional variable, which will let me know if the SelectionChange is being triggered by my code. If that’s the case, I won’t repeat the validation.
In this version, I’m setting the “ValidationInProgress” flag to true before activating the LastCellChanged, which is the line of code that will re-trigger the SelectionChange procedure. You can see that I’ve added an If statement at the beginning to see if this flag is true, and skip the validation if so. After I’ve completed the LastCellChanged.Activate, I turn off the ValidationInProgress flag.
And, we’re done! I’ve now got all the pieces in place that I need to perform validation on my Excel worksheet. In this code example, I’m only checking to make sure that State in column B is 2 characters long. But the structure is in place, making it easy for me to expand this validation.
I hope this gives you some ideas about how you can implement validation in your our Excel file. Next week, I plan to expand on this by talking about parsing out a phone number and extension. If you’d like to be notified when that post goes live, join my mailing list by filling out the form at the bottom of this page.
Did this work for you? If so, share your experiences in the comments below.
As always, I’m here to answer your questions. Call (978-582-9550) or email (kmartin@northportsoftware.com) to discuss your Excel or Access issues. I’m always happy to talk over your issue with you. If I can’t steer you in the right direction with just a short answer, we’ll schedule a one-on-one session to work together on it, or I’ll prepare an estimate for a longer project.
Kim
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of IT experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
#northportsolutions #learningneverstops #dataanalysis #smallbusiness #excel #microsoftexcel #msexcel #excelvba #vba
© North Port Solutions, LLC, 2022. All rights reserved.