How to create User Login Form in VBA and Excel (Step by Step Guide)

hello everyone welcome back you're watching the data laps my name is Dilip in this tutorial we are going to learn how to create a login page for a spreadsheet or excel based automated application suppose you have developed any application or complex excel spreadsheet and you want to apply a restriction so that only authorized users can access your application then this feature will be very very useful so here I am going to utilize an existing data entry form and in this data entry form we will add the feature of login form so that whenever user will open this application it will ask user credentials to proceed with a data entry job if user will provide the correct login and password then it will open the application for further uses otherwise it will get terminated if you want to learn how to develop this data entry form then you can refer the description box I have already provided the link of tutorial so to add the login form we just need to move to the visual basic application window so let's click on Developer tab and then in under code group just click on visual basic application button now you are in a Microsoft Visual Basic for applications window here we need to add a blank form so that we can design the login form so let's click on insert menu and insert menu we just need to select on the user form just click on that and once you click on user form it will insert a blank user form you can see that right so on this form we need to design the login form basically so let's start designing so first of all let's select this form and in properties window this is the properties window we just need to change the properties of this form so first of all the so first of all we will change the name of the form so let's change the name frm login okay and the back color would be white so let's select the back color white and the caption should be login to data entry application okay and let's change the height and width of this login form so the ideal height would be 198 and the width would be 385 okay so we have done with this form and let's insert a frame on this form so to insert a frame just click with the tool box and in the tool box just click the frame here just select this frame and draw you from frame over here okay so now we have added a frame on our user form let's change the properties of frame just select this frame and go to properties window and here we are need to change the border color so let's change the border color to blue highlight and the border style should be single so this right and the caption would be login credentials okay let's increase the font size so phone size should be 10 click ok so now we need to insert an image over here so let's select emails control here and draw an image right and let's visit 2 properties window and select the picture here select just click on ellipses and let's select this the user icon click on open and now let's change the properties picture size mode that should be a stretch and the picture language should be centered the border color should be today there should not be any color border over here so just select none now it's done let's insert two text box one is for user ID and one is your password so let's select this text box and draw a text box over here copy this text box and replicate this for password ok and here we need to insert icon for user ID and one is for password so select for icon let's insert image control and just draw this image control over here reduce the size let's change the properties so let's keep the name as it is like image 2 because we are not going to refer this encoding and the border style should be none and here let's select the image so picture and this should be user ID okay click on open now the picture sizemode should be a stretch and the height would be 24 that is as it is like 24 and the which should be 26 just copy this and paste it and replicate this for password just change the picture so just click on lips this icon and this is the first password okay so now we have done with the user icon and the password I get let's select this text box and change the name as txt user ID txt user ID okay and the control tip text and that would be and third user user ID okay and the which should be 144th okay let's select the text box for password see in the name that would be txt password right and the height would be 18 as it is control tip text should be enter enter password okay and the weight should be 144 so here basically whenever user will enter password we do not want to we do not need to show the password we just want to show the star so in that case we need to set the properties that is password character so the password character should be a star okay so now we have done with the user ID ID text box and password text box the pending part is we need to insert - or command button ovaries one is to login and one is to cancel clear basically right so let's - come on button over here just like the command button just insert it over here just copy this paste over here this would be for clear okay let's select the first one and the name would be CMD login and the accelerator Q should be L okay and the back color let's select any of the color maybe we can click this one this color and the caption should be login pull clip text click to login okay now select this the second common button and the name would be CMD clear okay and accelerator key should be e and back color let's select any of the color maybe this one and the caption should be click to reset okay and the caption sorry at least this is this should be the control tooltip text and the caption should be clear so now we have done with all those required controls it means we have completed the designing part let's start coding so before moving ahead and start writing the code let's move to model one window our model one basically and let's declare a global variable so public login instance as interior so basically we are declaring this variable the global variable that is login instance to account the invalid instance attempted by user so if the total attempt will be more than three times then it will terminate the window user will not be able to enter the user ID and password in that particular session right so in that case you just need to restart that application the Excel application and then try with the login ID and password ok so now we have done with the declaration the global variable let's move to frm login and let's start writing the coding so let's select this form click on View code and just delete this big note I need to write any code on frame 1 under script click in general from this drop-down just select this user form and from events and just like the initialize we do not need to write it on click events just read this now we need to write the code to initialize all those controls so let's start writing so me dot txt user ID dot value is equal to blank me dot txt password dot value is equal to blank okay and let's focus state focus on txt user IDs mean dot txt user ID dot set set focus ok so it means the cursor will be available in txt user ID text box now we have done with the user form initialization it means whenever this form will get loaded into Ram the mem in memory the entire the controls basically txt user ID and texty password will get cleared and the cursor will be available in txt user ID so that whenever you start typing you will enter the user ID in TX dividual and then you can move through tab or maybe with mouse okay so this is done for initialization let's move to frm login and let's start writing on the coding on login or the CMD logging so just double click on CMD login let's create some room over here so here I am going to create two different IDs one is for admin and one is for user so that you will have a clear idea how can you create a multiple user ID and give the permission to other users so that they can access according to their the access level basically right so first of all let's undeclared variable deem our user as a string okay and then second one is dim password and string now let's assign the user the user is equal to me dot txt user ID dot value okay and the password will be will be me dot txt password me dot value so now we have assigned the value user ID in user and the password impossible let's start writing the conditional code so if user is equal to admin and password is equal to a pin admin so this is the first user let's close this line here and start with or and just copy this line from here to here and this is for different user that would be user ok user and the password would be use it then so basically here we have created two different user ID one is admin and one is user so either one user can access with these credentials like admin admin or user or users okay so if like suppose user has entered correct password and you know correct ID then what we need to do we need to unload this form it means we need to close the login form and we need to show the application basically Excel application application dot visible is equal to true suppose user has not entered the correct password then we need to go with else condition and here we need to enter so if someone is not entering the correct password then there are two scenario one is like the first time user will enter the incorrect details credentials basically or the second scenario would be if user will exceed the limitation that is three times the attempts basically so in that case we need to handle in two different conditions so as we have already declared a go global variable so let's start with if login instances is if R is less than three then MSD box invalid login logon credentials please try again okay okay only plus really critical let's reduce the size so that you can see the entire coding and invalid login details okay now we need to increase the count of login instances so I'll login instances is equal to log in instances plus one so let me check the spelling I think I'm missing some okay logging instance so that should be logging instance and lowliness stance okay support user has exceeded the limitation that is total three attempt in that case we need to pass is different message so the message box will be message to the Box you have exceeded the maximum number of login attempts okay and we okay only plus really critical and that's and the caption would be title would be invalid credentials now we need to come out from this application because we need to terminate this so unload me and then in this workbook thought closed Save Changes is equal to false and the application not visible is equal to throw and token instance is equal to zero so this is the code how we have done with the CMD logging click let's move to the frm login just double click on it and just click on clear button and let's write the code to clear so first of all what we need to do we just need to copy the code available in form initialization so just copy this and just which the same quote in cm declare underscore click event we have done with the CM declare code let's move to the frm login now here you can see that there is a close button available in this particular form so what we need to do we need to just disable this particular closed form so that user will not be able to close this form after clicking on this button okay so let's move to the code window and here are just select user form and from here query closed okay and to disable the close button which is available on user form we just need to identify the closed type whether user is closing this form with the help of close button or visually is closing this particular form or with the help of coding or some other method right so here we need to identify so that is the method would be if closed mode if the closed mode would be 0 closed mode is equal to 0 then what we need to do we need to just cancel the pros basically so C a and C a cancel is equal to 2 so this one line 9 code will disable the close button which is available not in the form so now we have done with all those coding is required for login form let's move to the this workbook here and just select this workbook and on workbook open event we just need to write the code so the code would be first of all let's initialize the variable the global variable which we declared so login instance would be 0 now we need to hide the application the Excel application so that user will not be able to see the actual application once user click on a macro enabled button right so application or visible is equal to false and now what we need to open the frm login so frm don't show press ctrl s to save this and go to debug compile so I think we have used incorrect our variable name so this is login instance not the logging instances let's debug again and again the spelling is incorrect here again debug everything is looking good now save this close this and now save this file and close this file let's move to the folder where this application is available like here you can see that this is automated form in Excel just open this application now I can see that this application is open now but still we have not enabled the macro as well as we have not enable editing so first of all let's click on enable editing and now it will ask enable content so once we will click on enable content our macro will get fired right so let's click on enable content now you can see that Excel application is hidden and you login credentials window is appear so let's enter the ID that is admin and the password that would be admin click on login now I can see that it's working so this is all about how to create a login window in Excel and you can add this feature in any of the application or any complex spreadsheet where you just want to restrict restrict the access so that user can access only those data which has been granted by you ok so thank you for watching please like share subscribe and comment have a great day bye bye

Channel: TheDataLabs

Views: 73,347

Rating: 4.9274049 out of 5

Keywords: create application in excel vba, Create a login form in Excel, Create a Login Window in Excel and VBA, How to make your own simple and beautiful Login form in Excel, Coding of Login Form in Excel and VBA, How to make login form in VBA and Excel, Login system using VBA code and Userform in Excel, Utility Tool by TheDataLabs

Id: sjmvT51vDYs

Length: 23min 1sec (1381 seconds)

Published: Mon May 25 2020