how to create a vba login form in excel vba

so in this tutorial I'm going to show you how to create a VBA login form and what we're going to do is we're going to be doing it with a practical application what this application is going to do is it's going to grant users access to a particular part of the spreadsheet so we're going to have three tabs on the spreadsheet namely build sheet John's sheet and tons sheet and so these particular worksheets are areas which each one of them is only allowed to go into their own worksheets or maybe this could be a consolidation file that you're distributing to various users and they have to put in their numbers for their departments and you don't it's confidential you don't want the other users knowing about when user knowing about the other users confidential financial information within the department the way this has been set up as you can see there's a config sheet here for Bill John and Tom that's the there are passwords and these are the sheets that they get each of them get access to now this particular methodology could also be used to log into a sequencer of a database or any kind of other system an Access database or just giving you access to the particular program that you've written it works for all these but I just thought I'd use this as an example okay let's get to it so step one is we need to add the form so let's do that right now insert user form the form is there so first of all I'm going to size my form so let's just give it a name form login and I'm going to set its width to 240 and it's set to that I'm going to set its height to 1 to 9 let's just say 130 ok and it's caption is going to be log in to test system now it's time to put some controls on the form so again gotta go find my two box keeps moving all the time now let's put a text box on the form and let's put a command button on the form so here we go textbox let's give it a name I'm going to call the text username and I'm gonna make it left twelve and I'm going to set its height to 18 and I'm going to set its width to two or four okay so now the quick hack is rather than drawing another form just another text box rather just copy it so ctrl-c and ctrl-v and we've got another text box so now it's inherited all the properties are set with the other one so I just need to make sure it's top position is 42 which I want and top is 42 and that's pretty much it actually it's taken up all the other positions that are needed it to have the only thing is I need to set its name so its name is going to be text passwords and importantly we need to set its password character so if we go to if we look at password character here if I don't set that when the user types in their password it will be visible to anybody looking over their shoulder so you actually need to tell the VBA form that this is going to be a password text box and the easiest way is just put in a password character and in this instance I'm just going to put in the standard oestrus password character so that's that good to go so now let's set up our login button so I'm going to call this button command login and I am going to give it a caption of login and it's left position is going to be 1 3 6 and its height again needs to be 20 and it's a 24 at the moment and we will set its width to 80 and also we're going to set the default property of the button to true what that means is when the user presses their Enter key or the return key the button is automatically pressed so it just makes it a bit more easier for users of it to use it basically so that's that and set so now we're going to set up the cancel button so I'm just going to copy it again and I will just name it command cancel and with this button I'm going to set its caption to cancel and just like with the default button on the login button on the login button I'm one of the default property rather I'm going to set the cancel property to true on this that means when the user presses the escape button key this button is all not automatically pressed ok and now I need to set its top position to 72 and its left position to 12 now there's just one more thing that needs to be done with this form and this is the icing on the cake so if I run the form to launch it and I press the tab key you see the tabs going all over the place so if I start for initially it's in the this button here in this text box here which is the user name but when I press tab again it goes to the login button but really I wanted it to go to the password button I press tab again now it's that password now it's a cancel that's not what I want what I want is a smooth movement from some username to password to cancel and then to login so let's set that up right now and the trick to do that is first of all highlight the last button that you want to be indexed and so if I go to tabindex I'm going to set the tab staff the tab index to zero so that's it's set to zero then I set the cancel button to zero then I set the next button in the world text box in the row which is the password to zero then finally the username to zero so let's see what what's happened as a result of that the tab index automatically keeps adjusting so now if we look at it we've got the username with a tab index of zero the password has a tab index of 1 the cancel has a tab index of to and the login has a tab index of three so now if I learned about the form and you see the username has the focus I press tab again now the password tab again cancel login so it's working you intuitively the way users would like it to work so now it's time to code the form so how we do that is we we make sure the form is selected and we click on the view code button and we then get this module we don't want to use a form thick we don't need that so this form is being designed in an object-oriented Nano that's the best way to get information in my opinion in and out of a form like a login form shall we say so what we first got to do is given the form as an object it's going to be communicating with a calling routine and so the calling routine is going to launch the form but then it's going to query the form and say hey what password the user put in what username did he put in or she put in and did they click cancel or enter okay so the way we do that is we create what's known as properties and these are quick properties are not going to set up what are known as property let's and property gets so please don't complain about that you guys will know about us we just want to simply get the message across here so what I'm going to do is public AAA username as string public AAA password as string and then public AAA cancel clicked as uh lien so these are the three messages that we want to convey to the calling routine so the next elements that we're going to have to do is we're going to have to hook in the cancel button and the login button and you can do it via this drop-down up here so cancel so what happens if you user presses cancel or we need to let the calling routine know that the user pressed cancel so I'm going to type me dot now obviously vba has intellisense and you can now see why I prefixed my properties with treble a because the form has a load of built-in properties and you don't want to have to go looking through them each time that you're trying to access one of them so they all sort to the top that's why the way-ay-ay so cancel clicked which I can see are spelled wrong but that's not worry about it now equals true and then we're not going to do unload me because that's not the object-oriented way we're going to do me dot hide and me dot hide makes the form still exist it's just no longer visible now okay so that's the cancel button clicked now we need to do the log in and log in is just going to be simply me to hide because we log in the person's filled in the information you know we could put in extra validation buttons which say hey you know you haven't put in a password or a log in but for simplicity if the person doesn't put in a password are a username and they click login the verification is just going to fail them okay so me dot username equals me dot txt username me dot password equals me does text password and I've had so many takes of this video I don't have to come back and do it again so I'm just gonna I need to cut this in or let you see it now okay so let's try it again okay so let's handle the code to call the form so step one I'm going to insert a regular module and I'm going to call this module m-may in next we need to put in some code to initially just make the phone make the login form work so what I'm going to do is I'm going to create a function login user and that's it and then Tim Oh F login that's the name I'm going to give the login form as far M login and of' object and form login next I'm gonna set oh f login equal to new form login that's just the way you do was going to do it from an object perspective so Oh F login don't show VB modal means basically make the code stop until the for user press ok or cancel so because we need to continue being able to work with the code after the form goes back to the user so Oh F login that show VB modal so now we need to capture the username and password don't worry about this optional buy less s-said username reason I'm doing this is I want to be able to later on I want to be able to use this log in form to pass the use of name back to the calling routine them said password as string so now I said username and this is where the object orientation comes in equals Oh F login dot username and s-said password equals Oh F login dot password and you see what this does is it avoids the need to use local variables local variables are terrible and mess up your code what you've now got is a shall we say you've got an elegant solution because what I can now do is login set Oh F login equals nothing so what we've done here is we've declared the form in that line and that line I've then shown the form with this line then so when the form is shown well let's just give a demo of where we're at right now subtest and call login user okay so let's just run the code this way okay here we go Sean and no I'll password and now I click login and so there you go what's happens is the code van to login then because it was VB modal it displayed the form but didn't go to the next section so now if I f8 over this we see user name is Sean and the password is no password which means I would fail but that's the an example of the old element of the form so if I were to go for example or if login and you can check it in the immediate window as well dot username Sean but now when I f8 over it the form no longer exists so if I try this again I get a message an error message because the object variable no longer exists so ok sorry for landing of it but I think it's useful information to know so what we've demonstrated here is we've grabs the information out of the form and we no longer need the form so we got rid of it so now let's test the username and password so I can go if let's see mu tells this is a function I've written that's in the downloadable spreadsheet that comes with this dot username and password good to go and s-said password then else and if i then failed past ok so that's where you put your code if the log use of past OS user failed okay so that's the basic boilerplate so let's just test that message box passed message box failed so let's test it again and this is where when you're using more than one language you can make mistakes ok so that's just a tough one out let's do the test again so if i go tom and it's tom pass login past ok so now let's just for the demo what i've been talking to about this actual sheet thing that i was talking about so you know now how to create a login form that works but that's demo the actual demonstration i told you about at the beginning of the video so if i just paste in some code into the past section and into the fails section so just to keep things a simple login user equals true login user equals false get rid of this so this is a function so now the next step is to paste in just for speed my function called system lock on that was used with this so if we just take this out and put in this we've got system logon so I've earlier created a module called mu tells which has got display allowed worksheet username and password good to go the code for that and get found range and so it's you it's utilizing quite a few things that are in this spreadsheet which again you can download okay so let's just demonstrate this and see what's going on here so if I step through it well I put a breakpoint here okay so that's test it now one so the f8 over this Tom and Tom pass so notice that the code has stopped now when I press login we're back in the code again and I'll check out this break point and we effe it out and the result is true so I am an authenticated user Tom whoever is is an authenticated user so if result equals true we now get the allowed worksheet so the allowed worksheet that Thomas allowed to use is the sheet called as Tom and all of that is in this code here get found range and it does a it does a search on the hidden sheet on the hidden cons of favorite cheese so let's go so now we do the despair that worksheet so what does and you can see Tom's worksheet is displayed and if I go to format hide and unhide on hi cheese is still grayed out however you can see here at there our number of sheets available so now if I log out we're back to the main sheet and you know the sheet exists but it's not displayed so there you have it that's it that's how to create a login sheet so if you find this video helpful please don't forget to Like and subscribe it helps me and it encourages me to continue building these videos and you know leave a comment let me know what you use VBA for and maybe give me some ideas for other videos that you'd like me to make to help you out on your Phoebe a journey

Channel: Sean Johnson

Views: 3,182

Rating: 5 out of 5

Keywords: vba login form, excel vba login userform, excel vba userform, vba form design, vba form design excel, excel, vba tutorial, vba basics, vba login form design, login form in vba excel, creating excel data entry forms, visual basic for applications, vba, excel vba, vba login form tutorial, microsoft excel (software)

Id: gQzJCK4rhI8

Length: 18min 19sec (1099 seconds)

Published: Thu Jan 30 2020