Excel VBA Forms Part 4 - Validating Controls

welcome to part 4 of this tutorial series on Excel VBA user forms this one's all about validating controls on form making sure your users have entered valid values so a quick overview of what going to cover here once about validating individual controls in this video so looking at individual text boxes we'll start with a quick look at how you can define your validation rules so you know what you want to achieve which is quite important before you start writing code we're gonna have a look at the events that are triggered by entering data into a control and the sequence of those events is quite important we can then show you how you can test for numeric values and for date values that have been entered explain how you can cancel an event if the values entered are not valid and then just a quick comparison of using message boxes versus formatting controls at runtime as a way to indicate to the user what's gone wrong there's - a quick little summary the advantages and disadvantages there it's quite a lot to doing this one let's get started before you start writing any validation code it's a pretty good idea to decide on what validation rules you'd actually like to apply and when you'd like those to be triggered so just as a quick example what we're going to do with our form by displayed on screen is we're going to make sure that when the user clicks the add to list button that all the controls have already been filled in so we can't click Add to list if we haven't eyes in a title or if we've deleted the default parties for the gross and release ticked we're also going to make sure that when a user types values into the forms if I typed in a different number into the air into the gross text box here if I try to move away from this control if I haven't typed in a number first of all it's going to prevent me from moving away also if I have typed in a number then if I typed in a negative value instead of a positive one that will also prevent me from moving away from the control until I've entered a valid value we'll do something very similar with a date if I don't type in a valid date if I try to move away from this control they don't be I will prevent me from doing that and if I enter a date that is in the future so if it's greater than today's date I'd also like to trigger some validation code there as well there's quite a lot stuff to do there in fact we're going to start with the quick basic idea of testing if the value of this gross film gross text box is a number when I try to enter a value and then move away from it I'm going to cancel out of the form and then when we go to the visual basic editor and now you can start adding that sort of code now before you can start writing effective validation code it's really important to understand what events actually get triggered when you enter a value into a text box so this next part is completely unnecessary to actually successfully validate the form this is purely to demonstrate the principle behind the events involved so feel free not to not to do these next few steps I'm going to change the design of my forms but I'm going to label here that allows me to list out what events have been triggered when when we entered a value into this gross text box so I'm going to give myself bit more space in the form and I'm going to add a simple label I'm gonna make it quite tall so it's going to add quite a lot of different lines of lines of text into this label I'm going to give it a sensible name as well so I'm going to call this one event list and then what I'm going to do is add some code to the events associated with this text box before I do in fact I'm going to add a quick little default caption to the label as well so rather than calling it label one I'm going to say this is a list of events so the default event associated with a text box we've seen things like the click event for a button and the click event for a form and the initialize and terminate event text boxes have events too if I double-click on the film grossed text box I'll go into the code tree of the form and along with all the other code we added in the previous video we'll see we get the film gross change event so the change event is triggered every single time you change the value of that text box so every time you enter a different value or deletes a value it will trigger this change event there are several other events associated with it without with changing values in the text box as well so I'm going to go back to the drop down to the top right hand corner I'm going to show you in order if I enter this this text box the first event 11 is the enter event I'm going to select that one I'm also going to go back then and we've already got the change event and we're going to change the value and I try to move away from the text box what happens then is that before update event is triggered so I'm going to set the before update event I'm also going to select the after update event which happens immediately after the data is updated and then finally I'm going to select the exit event so that's a sequence of events now having selected all of these you'll see it they all sit together in the in the code view they're all they'll start with a name of the control so film grow so it's um not listed in the order that they happen they're listed in alphabetical order of it of the event names so we're going to do is just add a little bit of code in to first of all the after update event that adds a little entry into my label and I added so I'm going to say what was it called list of events or sorry event list wasn't it we call the event list and changes caption so that it's equal to event list caption so it's equal to itself and concatenate a VB newline character and then concatenate the name of this event which is after update and then we're going to do exactly the same thing for each of the other events so it kind of makes sense here just to copy and that's line and then for the before update event I can paste that in change them with the event there so it says be for update and then again I can paste it into the change event and call this change and then the enter event and again call this enter and then finally into the exit event and call that exit okay so having added this it's fairly basic bit of code really is just a lot of a lot of effort to add to capture every single individual event what we're going to do now is go back into the into Excel and test what happens when we start changing the value of this film gross text box so I can launch the form in the usual way by clicking any of my three buttons and then you'll only see that the other caption of my label has changed once there's there's an energy there this has change so that it then there has been triggered when the code that we added to the initialize event of the form changes the value of this text box so the these events can be tricky not just by you or a user physically changing the values in text box but also by code changing it as well so I'm going to go through now and I'm going to activate this text box by hitting the tab key to navigate to it and you'll see that we've now entered the text box let's trigger the answer event and I'm going to start typing in some values so I was having seven three one you see every single time the value changes if I backspace an entry as well that triggers a change event so every change to the value in that text box triggers the change event what I'm now going to do is hit the tab key to navigate to the next control and that's going to trigger a sequence of three events before update after update and then exit it's important to note that the change event in the app the update events only get triggered if you do physically change the value in the textbox so I go back to the title text box I'm going to tap into the gross text box again so the enter event then I'm just going to immediately tab away from it into a different box so they're going to just get the exit so I haven't triggered a change or before or after update so now that we know which list of events happens and what order they occur in we need to choose which event we add our validation code to it doesn't really make sense to use the enter event because the value might not have a change so there's some seem silly checking the value has not changed we could use a change event I suppose and trigger our validation code for every single individual character change of the value in the text box that's probably a little bit inefficient and might get a little bit annoying to the end-user as well we could use before updates after update or exit so these are the before after so the before and after update events are triggered only if you have changed the value in attacks box exit is triggered every single time so again it might might be a little bit inefficient to use the exit if and investor get every time you maybe were just passing through the text box so before update or after update I think the two that make most sense if we go back to the visual basic editor and sorry background never close down the form first and then go back to the visual basic editor if I look at the code behind the four months you can hit the f7 key to go to the code view the one we're going to pick for our validation is the before update event and the sole reason for doing the before update event is because this one has an extra parameter called cancel which can be set to a boolean value true or false so before update can be cancelled whereas after oblate can't the affect of that if I choose the before update Avenger is going to add a single little bit of code here that's going to demonstrate what canceling this event does so I'm gonna set cancel equal to true then I'm going to go back into Excel and I'm going to load the form again and I'm going to tap into the gross text box so I get the enter event I'm going to type in one two three and I'm going to tap away from it by pressing the tab key so you can see is triggering the before update event but before anything else can happen I'm immediately canceling for a place event so that prevents both the after update and the exit event from happening so then resolve the user is there stuck in this text box now ideally we do this with a some sort of conditional test to check if the value that was spin and it was not a number now if I try to do this these are can't possibly move away until they've entered a valid number so now that we know what again we're going to choose we've just got tied in the correct code to make it work properly so I'm going to close down the form and then head back to the visual basic editor and then start adding some code to the before update event of this text box so the important thing here is that we only cancel the event if the value entered is not a number so to test if a value is a number you can use a function in VBA called is numeric so can write an if statement and then we'll check if not see me if not is numeric then we're going to pass in the value of the the film grossed text box so and as a film gross dots with value then and only then I'm going to cancel this event and don't forget to add in the end if statement just at the end okay so just to test this out now if we head back to excel and we choose to show the form and we can enter some some random details you can say the prisoner we enter a value if I do enter a number here so if this is number six then I can happily tap away from this control and everything works normally if I get back around to this control I understand that's not a number so for instance the text not a number if I try to move away from this text box now I'll find that I can't so every time just matter I tried to click away from it or tap away from it I can't move at this control because they're before update event gets canceled every single time the problem with is what we've done so far is it's not very obvious to the user what they've done wrong so what going to do is make sure that the user gets presented with a message to tell them what's happened and what they can do to put it right so I'm going to close down the form head back to the code view in the VB editor I'm just going to double click on the gross text box there I'm gonna add a bit more co2 here to this a if statement to make it a bit more obvious to the end-user what to do so one simple way to display information to the user is to use a message box so inside our if statement either before or after the cancel equals true line doesn't really matter which I'm gonna add in my message box before that line we're going to display a message box which says you must enter a number and then let's have a quick little symbol on here as well we'll have a VB critical symbol sorry for stopping in place of the other comma V be critical is what we'll have here okay so just to give this a quick little test we can go back to excel again and we show the form and have straight there and try to type in a bit of text and then try to move away from that text box there we go we see our message box pop up I can hit OK or or a tensor on the keyboard then I'll be stuck inside this gross text box until I change the value into a number one slightly disappointing thing here is that I have to manually select all the text I've using the mouse or just manually delete it using the backspace key or the keyboard is quite annoying that I have to do that myself manually you'll be nice if the if the code selected the text in the text box or indeed just deleted it clear these contents out automatically so that all I would have to do is start typing and a valid number so I'm going to close down the form and then go back to the visual basic editor again and then back to the same event we're going to add in a little bit more code inside the if statement to make things a little bit more user friendly so we could do a couple of things here and probably the easiest is just to clear the contents of the text box if the users entered an incorrect value to do that we can head back into the if statement and then we could just change its value so that it's empty so we could say film gross dot value equals an empty string would work it might be more appropriate in this case to change it to be equal to a number maybe the value 0 is meant to be a numeric text box so we could set it to its original default value of 0 the other issue here is that the user might have just done something symbol by mistake for instance they might have entered a valid number but then accidentally hit one of the letter keys right at the end and what we're doing here is clearing out the contents of everything they typed in which could be quite annoying so rather than delete everything what we're going to instead is is select all the text in the text box for the user so that if they did want to replace it they can just start typing otherwise they can just still edit what they've typed in already so to do that we're going to change to other properties of the film grossed text box the first one is called self start this indicates the position of the starting position of the selected text in the text box we're going to make this equal to zero which indicates the very first character the very first position the leftmost character in the text box the next thing we're going to do is change the length of the selection we're going to make this equal to the length of all the text that's in the text box so when we say film gross dot cell length selection length to make this equal to the length of whatever's in the text box we're going to use the Len function LD n and then if you open the parentheses we can pass in and the value of the film grossed text box so we say film gross dot value so it will calculate the length of string and select all the text that's covering the ended so to give this one a quick test let's head back into Excel and then again we'll show the form on screen and I'll head straight to the text box and start typing in a value if I tap away from this now I'll click on to a different text box I'll be prevented from doing so but when I click OK or press Enter I'll go back to the text box and all of the text will be selected as long as I type in a valid number I can tap away and everything will work perfectly now one small problem with this style of validation while we use message boxes to indicate issues is that it's quite intrusive to the user they've got to read a message and then either click OK or press Enter and kind of just interrupts the flow of entering data what would be an alternative approach and one you'll see fight quite commonly use on websites these days is rather than displaying a message you have to click on they'll indicate an issue by changing some formatting property of the controlling question so what we're going to do instead of showing our message box is we're just going to change the background color of this this text box to indicate some sort of issue so to do that let's close down the other form first and then we'll head back to the VB editor and we'll head back to the code behind the ER that the ad button already text box and here we're going to remove the line that shows our message box on screen what we're going to do is replace that with a line which simply changes the background color of the text box so we can say film grosse dots back color and then I'm going to use one of the RGB colors I'm going to make it RGB pink Society subtle more subtle color them than RGB red which is a bit too heavy I think so simple little effective if we go back to excel now and then show the form again on-screen and we type in something that's not valid into the sex box as soon as we try to tap away the other text box sounds pink one thing we do lack though with this technique is any sort of valid information that this explains it is what's gone wrong so that's what the other labels come into play what we're going to do is modify the caption of this label to indicate what - these are what they should do to fix a problem the other issue we've got as well as if we do enter a valid value inside if I now type away from this control this the text box still remains pink so to solve both of those problems what we're going to do is head back into the air the VB editor so I'm going close down the form head back to the VB editor and then go back to the code-behind near the text box so we can change properties of the label in just the same way we can change properties of the text box at runtime let's refer to the label fortunately I created quite a sensible naming convention when we first created the form I've got my text box called filmic gross I know it's associated label it's called film gross label so I can find that in the intellisense fairly easily and then we can change that so let's change its caption property so it reads a different message is going to say something like numbers only or you must enter a number and then the the four-color will also change so that it stands out let's say film gross label and we'll call this one also changed the other four color so we'll change this one so it's equal to RGB red I think red will be a fine for the four color so to give that one a quick test again we can head back into Excel relentless testing is the key to making your sure your validation code is working and let's go back and we'll change it to a bit of text again and this time we'll get not only the background of the text box changed to pink the font color of the late all changes to red and it makes it hopefully kind of obvious what we're supposed to do here now we've still got the issue where if we change this to a valid value and then tab away from the control we've got everything looking as though there's still an issue so when we get to the point now where we change everything back when the value invented is valid now a sensible place to change the formatting of the text box and label back to their original status is in the after update event and let's just have a quick look at why that's the case so if we were to just tab through the controls without changing anything we took at the enter and the exit event for this text box will just go back to the text box shifting tab and then I enter an invalid value and then try to tap away it triggers the change in the before update event if I now enter a valid value change and then tap away it triggers the before update then the after updating the exit event so basically the after update event will only be triggered if I change the value in the text box and it is a valid value so that means that I can use that too to reset the the formatting of both the text box and the corresponding label so let's just close the form down here and we'll head back into the visual basic editor and then we'll head straight to the after great event of this text box so let's start by having a couple of extra blank lines and then we can add in some more code and change things back let's start by changing the back color of the film grossed text box so I say film grossed back color equals I know that was that was just set to white sometimes a RGB or white that's a nice easy change make we can also change the labels caption back to its original so rather than just a Paul that out I'm going to cheat and copy and paste and change the caption back so that it was just called which I said gross and then the dollar sign in parentheses after it the last thing to do is change the labels for color back to its original color and this is a little bit more fiddly to do so we originally changed the other four color of the label and we're designing the form in the first place we change the forms for color so one way to find out what the full color of the label actually is is to go back to the design view and then have a look at their the the label if I select the gross label each of the four color is this hexadecimal code which isn't the most useful thing in the world really we want the RGB value or just the other color number of that color so one way to find out what that is would be to use the immediate window to ask the question what the Erb or the color is if I go to the View menu at the top and choose immediate window we'll just press ctrl + G I can use the immediate window to ask questions what the values of properties actually are so I'm going to ask a question by typing in a question mark then I need to refer to the form itself which is film details then the Philmont grocery label and then the for color property this will return if I press enter at the end it's going to return the number of the color of that label so what I could do is just use that number in place of the actual color name or the RGB function again that's kind of like a hard-coded constant now so the issue here is if I change the design in the form later on to have a different four color I've got to come through and change all these settings as well so what might be a slightly better solution is to rely on the father as well as changing the design the for color of the individual labels when we design form in the first place we actually change the four color of the entire form and in fact the label text changes to match because they inherited the the values or the properties the formatting properties of the form itself if I drew a new label on the form it will inherit the same four color property so another approach here rather than changing the label color specifically what we can also do is change this to be equal to me dot four color so the four color property of the entire form so a couple of different different techniques you'd use there let's just test that they all work if we go back to the form now in Excel and we launch the form again and we start by typing in some text into the textbox everything changes color and we get the error message if I finally replace it with a number and when I tug away this time everything reverse backwards which original settings fairly nice screen lined way for the year for the end-user to to work they'll clearly see if something's gone wrong and as soon as it fix the problem everything returns to normal indicating that everything's okay again the last thing we'd like to check for the film grossed Xbox is that the user hasn't entered a negative number which lets Ilham handle that part yet so it's up into the text box and type in a negative number and then sub away from it that's seen as a perfectly acceptable value so to make sure that's not allowed anymore we're going to close down the form head back to the VB editor and we're going to add in a bit more code to the before update event of that text box all we're going to do here then is add an else clause the if statement inside the before update event and this is only going to get triggered as long as the value entered into the film grossed text box is a number so if you say else we can then nest another if statement to check that that number is greater than or equal to zero in fact what we're going to do here we're going to check if the film gross value is less than zero then we're going to do something I'm going to close up my if saver button and if essentially all we want to do here is basically repeat all the things we did when the body wasn't a number in the first place so the important thing is that we cancel the event I know it seems a little bit silly to a duplicate code like this but for the time being just in the interest of getting this done we're going to copy and paste that entire set of code and we're just going to then modify maybe a caption that the label displays so I'm going to call it what we say we'll say positive numbers so make a nice quick simple little change which is just a more descriptive more specific message for this particular error so if we were to then go back into Excel and once again test that the validation works we should be able to tab to this text box we type in text that's not going to work if we type in a positive number that will work it would have been a negative number that won't work but again we can replace it with a positive number and then everything works again so um that's the basic idea I think that's pretty everything to do with their film grossed Xbox the next thing is to validate the release date to essentially check the same sorts of things before we do get on to validating the film bait textbox what we should probably do is tidy up a little bit with a film gross so we've added in a whole bunch of extra code that was purely to show you the list of events that were happening so let's say let's just delete those lines of code that were indicating the event that was happening so for each of these after updating before update events we can just delete the first lines of code in there I also added in all these extra event handlers to show you the sequence of events so the film grossed change enter and exit we don't need that that happen anymore so I'm going to just delete those event procedures altogether we can just remove those entirely that means that the form doesn't really need the big event list label in there anymore so going to go back to the design view the form of the object view and then just select this list of owners label and delete that as well then we can resize the form so it looks a little bit more sensible again the very last thing we're going to do to tidy up it's kind of associated with the last thing we did to validate the film grossed text box we replicated the same section of code in the same subroutine which is a little bit silly to do what we would do ideally in the real world where if we duplicate code like this we would separate this out into a separate subroutine and then call it whenever it's needed and the set needs professor is modularizing code or writing modular code so it makes things a bit more efficient it makes things easier to change because we would only have one place to do it so what we'll do is we'll write a separate subroutine sometimes referred to as a helper method that will allow us to to change the formatting of the film and grouse text box and label but only writing the code once so we're going to declare another subroutine inside the form itself it's going to be a private subletting because we only want to be able to reference this from within the form it's going to create a new private so it doesn't really matter where in the sequence but for me it makes sense that it's somewhere nearby the events that are going to call it so I'm gonna call this one a private sub just to make it fit in with the M with the names of the M the existing procedures and we'll call this one film grouse show error so it just starts the name of the the control that were modified now one thing that is different about the two separate sections of code we've we've written here we have a separate caption for the film grossed label depending on exactly what sort of error has occurred so I'd like to be able to handle that slight difference in their subroutine itself so I'm going to open up a set of parentheses and declare a simple parameter here that we're going to call what I'm going to call it we're going to call it error caption or error message there a caption I think will do as a string so the subroutine itself is going to essentially just do all the things we've done here so I'm going to copy and paste this section of code one more time apart from the cancel equals true cancel is a parameter of the before update event so I can't make that happen inside the other film gross show error subroutine so let's click cancel where it is but copy all the other lines of code out and then paste them into this procedure and then the one thing we're going to change here is rather than specifying this exact error message here in numbers only or positive numbers I'm going to reference whatever value we pass in through the error caption parameter so I say error caption instead of the exact specific message now what we can do is start tidying up the before update event so I'm going to first of all deal with the if not is numeric section let's get rid of these lines of code here the things that changed a for color back color and select the text and then what we're finally going to do is take away the section and says film gross label caption equals and then replace the entire thing with a call to the film gross show error procedure so there's a couple ways we can do that simplest is just to call or just state the name of the procedure you want to call so film gross show error the the promise we're passing it all the argument that we're passing to the parameter is numbers only this time so you'll see you type in a space after your your film gross show error method you'll indicate you've got a compulsory parameter error caption as a string so we're going to pass in the the the the phrase numbers only this it's worth mentioning good again we've covered this in input that's part of the main excel vba series you can optionally write the call keyword when you call another procedure the only difference that makes is that if you want to pass arguments at the procedure you'll see currently we've got a syntax error we must wrap up the parameter list or the argument list in a set of parentheses and then it's perfectly valid again until your call if you do I'm sorry no pun intended there until your choice for the you are whether you do that with the air with call keyword or not once again then we can just modify this for the the second if statement again leaving the cancel equals true statement in there but we're moving all the other lines of code and then replacing the final one there with a call to film grouse show error and then this time passing in the positive numbers arrow caption so there we go it just IDs up the before update event a lot it means that we've only got one set of code that we would need to update so if we decided that we wanted to change the colors we're using to indicate the the errors we've got one single place that we can do that now everything else will just work in exactly the same way and if you wanted to prove that we can add back to excel and wants more tests the whole thing out so I type in a bit of text doesn't work says the numbers only if I tab away from this with a positive number in there it will revert back to it the way it was before go back and type in a negative number and it will say posit I haven't made quite made this label wide enough a by he's not showing the full caption I should probably change that in the design view maybe shorten my little error caption here but the important thing is it does work with a single subroutine rather than repeating the same section of code again and again and again so it's just a quick idea for you it takes a little bit of effort to do that but I think in the long run it's worth it writing these separate helper methods to make sure that you can them you can edit the code more easily later on and call the same procedure multiple times so now that we've seen the general approach validating any other control in the form is pretty simple and straightforward it follows the same pattern so let's validate the film bait textbox I'm going to do this directly from the VB editor this time rather than go back to the design view the form I'm going to use the drop-down list at the top to select the film they text box that'll give me the default event which is the change event thorn that I really want here is the before update Avenger so I can select that from the list of the top rights and then delete the film they to change procedure I shall be needing that okay so similar approach again what we're going to do first is check if not this I'm going to the is date function rather than it's numeric and I'm going to check the film date dot value if that must be the case then we want to a whole bunch of different things the things that we want to do we're going to replicate later on so what I'm going to do now is write a separate a help a matter that we can just call the really important thing there of course is that we cancel this events when is a cancel equals true let's write the separate helper matter then we're going to pull this one a private sub sorry private developer that private sub film date show error and again we're gonna have a separate caption property so an error caption parameter that we can pass in different messages to as a string so the things we're going to change here we're gonna say film date dot back color equals RGB pink and then we'll say film date label so from the label dot for color equals our DB red and then we'll also say film date caption sorry filmed a label dot caption back on film a data label dots caption equals whatever the error caption is that we passed in so I'll refer to the error caption parameter will also say film date dot cell start equals zero and then we'll say film date dot film beta dot cell length equals le n film dates dots a value so I'll select everything in there then what we can simply do is call that procedure from inside the if statement at the before update event so going to say call film date show error and then we'll pass in a specific error message this time we'll say um dates only okay so that should handle the basics of changing the font color and the for color and the back color of the other label of the label and the text box what we haven't done yet is change everything back to its original state so we need to use the after update event of the text box to do that so while we're here we can choose the after update event from the drop-down list at the top after updates there it is and here we're just going to do essentially the reverse of what we've done in this section here so I'm going to take just a quick copy/paste of this and put that into the after update event so I'll change the back color of the film Bates text box so those RGB white the film they label for color I'm going to make that equal to the forms for color so again I can say me dot for color and then the caption of the film date label will go back to being whatever it was originally was something just something simple like release date okay so that's the absolute basics of making sure that we enter dates and dates only into the film date text box so again we should check this validation code works by heading back into Excel and then showing the form on screen and trying to enter anything that's not a date into the release date text box so clearly we haven't dented a date if I change this back so it was 22 Mar 2016 and then tear away that is suddenly triggered or detected as a date the nice thing is that it will detect different values as dates as well so if I if I change this so that it was let's see let's say 22/3 slash 2016 this will be detected as a date quite happily it'll do us formatting so 3/20 - slash 2016 that's the touch as a valid date so there's a variety of formats we can enter here that will be detected as valid dates it's essentially anything this this there's not a date there you go you get the validation code being triggered so that's one thing done the other thing we want to do here as well is check that we don't to a future date so if I entered for instance the 25th of the third 2016 I wanted this to be invalid as well I only want to enter films whose release dates have already happened so let's add in a bit of extra code to check that the release date hasn't passed yet we can close down the form head back to the VB editor and then back to the release date text box code now this sounds like it should be a fairly easy problem to solve in a similar way to the way we solve this year with the negative numbers for the film gross value we can add an else clause to the if statement in the before a plate event of the film date text box so we now if we do that and then we can simply check else if film date value is greater than the result of the date function then what we'll do is we'll say end if and we'll call film beta show error and then we will pass in a caption like no future dates something on those lines no future dates probably home got quite enough space to show that mess filled caption on the M on the label but never mind will clearly see that it's working and of course the important thing is that we cancel equals true so having done that I want to give this one a quick test and show you a slight little issue with what we've got here if we go back to excel and then let's launch the form and we'll try adding in let's see let's see adding in a date this in the future so 25th of March hits at the list and clearly that that works that's fine if I change this back to let's say the 21st of March 2016 minutes add to list the annoying thing is that still triggered that still detected as being later than today's date and clear that's not the case doesn't matter what bait I had in 2nd of March 2016 2nd of March 1902 matter here anything that I try to do to tap away from that text box thinks that the value in that text box is greater than today's date the basic reason for this without going into too much detail is that the value in that text box is not yet being evaluated as a date so although we're comparing it against the date function is not treating the value in the text box as an actual date value so the solution to this to make sure that the value in the text box has been converted into an actual date or we attempt to compare it against the results of the date function so let's close down the forum versa ball and then batch the VB editor and then back to the same code behind there the the app before update event of the film date textbox so to convert the value of the text box into an actual date we're going to use a variable and then a conversion function so I'm going to declare a variable at the top let's call it actual date so we know what we're dealing with and the dates of this will be a date we want to make sure that the value we've typed into the textbox can be treated as a date so we'll write this as part of the else clause ganz will only reach the else clause if the value entered can be evaluated as a date and as long as it can be evaluated as a date we're going to convert the value we're going to store the value in the actual date variable we'll make it equal to the result of converting the values in the C date function so C date and then pass into that the value of the film date textbox so film late dot value we can then just modify the if statement ever so slightly to say if the actual date is greater than the data function then we'll call on our validation code so once again a quick little test back into Excel and show the form again and if we just change this to a future date first of all so let's say 25th March and then click away from that no vg8 so we can't leave that control yet if I change it back to the 22nd March or the 21st or anything else then we can happily now tab away from that text box it's worthwhile maintain just again just quickly the idea that you can enter in different date formats so for instance but if I put in the first of the fourth in UK format so day month year and because the original settings are my computer setup to use UK formatting for dates then it triggers the first of the fourth as being later than the the 22nd of March if I change that around so it was a 4/1 and then that streeted is the 4th of january so that will be earlier than today's date so that part's just out just based on the original settings of your machine just in case you were wondering ok so we've got to the stage where we've got these two individual text boxes validated quite space quickly as soon as we enter valleys into them that check the valid data type and that the valid data type is within a certain range as well the one thing that we haven't done yet in this video is we haven't checked what happens if you just clicked the add list button blindly without entering anything or indeed removes values from existing text boxes the numbers only thing works quite neatly there for this because if I try to enter an empty string that's treated as a not number that's quite neat likewise for dates if I try to remove that and then click on that check box that's treated as a not date so the one thing we haven't done yet is checked what happens if we haven't entered about you at all in a text box and we're going to deal with that in the next video in the series we're going to add a lot more code that's going to check every potential object to make sure that it has a value entered into it so hopefully this one's been useful so far next one kind of is a companion video to this one so join us next time and we'll find out how to test for empty text boxes if you like what you've seen here why not head over to the wise our website where you can find those more free resources including these videos some written blogs and tutorials meeting some exercises that you can download to practice your skills thanks for watching see you next time

Channel: WiseOwlTutorials

Views: 44,283

Rating: 4.923913 out of 5

Keywords: vba, user forms, excel vba, forms, validation, isnumeric, isdate, wise owl

Id: eovrxqu3dHE

Length: 41min 1sec (2461 seconds)

Published: Thu Mar 31 2016