All right, I'm going to show you how to do basic input validation for user forms in this screen cast. Input validation for user forms is quite a bit different than just regular subroutines where you're asking the user in an InputBox for something. Recall that when we did, in the first part of the course, we did input validation using do loops. And once the input was valid we exited the do loop using a exit do statement. However, for user forms, it's going to be quite different. We're going to do a couple different types of input validation. The example I'm working with is the tank example in the preceding screen cast. So we've got a couple problems with how it is right now. First of all, we want to make it such that the user cannot enter any negative numbers, so all fields should be positive. We're going to display an error message if at least one of the four input fields is negative. What I mean by this, is right now we don't have anything to protect the user from entering negative five and it's going to give us actually a negative mass and we obviously don't want that. There are also some other errors that can occur that once you give this to a client or co-worker you want it to ever open up. The editor and so we're going to try to protect against all possibilities such as that editor will never pop up and there will never be debug window. So, what I'm going to do first is I'm going to add in some statements into the code to verify that H, R row and the depth are all positive. All these input validation is going to go at the very beginning of the sub, right after we define what pi is. So, first thing I've said here is if any of those variables height, radius, rho, or depth. So I have or separating them, if any of those statements this conditional statements, is true then we're going to display a message box at least one of the inputs is negative. Please try again, and we're going to exit sub. In general, you shouldn't use exit sub unless it's for something like input validation in user forms. So let's go ahead and see if this is working, if I put in something like a negative five then it tells me at least one of the inputs is negative. Please try again. So it protected against the first type of input validation. The second thing we want to do, there should be an error message if at least one of the first four fields is left blank. It doesn't make any sense if we try to run this calculate button if we leave one or more of these fields blank. So what I've done here is I've just put If any of those variables is equal to just empty quotations, that means they left it blank. If any of those are true, then we're going to trigger a MsgBox, at least one of the inputs is missing, please try again and then we exit the sub. So when we try to do this, let's just put some numbers in here. I'm going to leave that blank. Then it says at least one of the inputs is missing, please try again. The third thing we want to look at is if the depth exceeds the tank height, then obviously that doesn't make any sense. So your sub should detect this, and you should let the user know that the depth can exceed the tank height. So I've put these lines here, if depth is greater than height, then that obviously doesn't work, so we display a message box and we exit the sub. So if we run it with something that doesn't make sense, the height of the tank is 5, yet the depth is 6, it's going to notify the user of that. Fluid depth cannot exceed the height of the tank! Please try again, and it's going to exit the sub. So that's how we can protect against the user inputting something that just doesn't make sense when the depth is greater than the height of the tank. By the way, I should warn you, sometimes when you're doing comparisons like this and you're adding numbers and multiplying, these depth and height, they're actually all strings. So if for some reason and I'm not exactly sure why it happens or when it happens? But if sometimes you're not getting what's expected, always troubleshoot, always stepthrough to make sure you're getting what you expect. But if it's not working, remember these are strings and most of the time it can do a comparison of the numbers that are in the strings. But if it doesn't work you can always put like a 1 times and a 1 times which is sort of a trick to convert depth as a string into a number. That's just kind of a trick that I've learned over the years. The last thing that we're going to do is we're only going to accept numbers that are entered into the text fields and we're going to enter an error if text is entered. And I'm actually going to put this a little bit higher in the code. I've placed it up here, and I'm using this isNumeric. IsNumeric of a number will be true, so if any of these height, radius, rho or depth are not numbers, then what we're going to have for example if the radius is a string isNumeric of a string is false. But we're taking not of that, so not false is true, so if any of these is not a number then we'll have a true and we'll trigger this error message and then we're going to exit this up. So this is a good way to check, to make sure that the inputs are numeric. So I've got a string here. And when we do that, it says at least one of the inputs is not a number, please try again. The idea here is we've validated the input. If we leave it blank, we're detecting that. If we put in a negative number, we are protecting against that. And in no way do we end up bringing up the Visual Basic editor or that debug window and so this is very professional. You don't ever want to scare the user with bringing up that debug window because they don't know what what's going on, and it's not very professional. So, hopefully this gives you a good idea of how to validate input in VBA User Forms.
