How To Create A Fully Automated User Password Reset In Excel

hello and welcome this is ready with Excel for freelancers and in today's training I've got an amazing unique user password reset we're gonna teach you how to allow users the ability to reset their own passwords completely automatically so it's gonna be a great and unique training let's get started all right thank you so much for joining us today I've got a really cool training and this is gonna allow you to give your users the possibility to reset their own passwords of course as an administrator you would allow or disallow this we're gonna add this setting in fact we're gonna be taking off from one of the trainings that we had done previously this is gonna be the share and sync workbook and if you'll remember previously back on April so that was about what six months ago or something we did how to share and sync your macro enabled Excel workbook we're gonna take off from this workbook if you haven't seen this video we built this workbook from scratch allowing users to share and sink their macro enabled workbook from around the world with unlimited users simultaneously without using this shared workbook so if you haven't yet seen this video of course I'll include the link down in the description below it's a great software got about 13,000 views on it it's about average but I'd like to see a lot more because it is a really groundbreaking training and this video wheel we basically allow users to rebuilt a login information in this so the users can log in so it was a great training so I want you to go ahead and take a look at that if you do get a chance so we're gonna use that same workbook but in this particular training what we're gonna do is we're gonna give the user the ability to reset the passwords in fact I'm gonna build this live right with you right now so we're gonna take this workbook I have not made any change other than the icon in the title everything else is the same so we're gonna build it right now so I can't wait to show that you in fact we're gonna be using this same type of ability to reset in our mentorship program as we build the accounting application that is gonna be an amazing accounting application I am let's see let's take a look at the scope of this accounting application it is massive just so you can see what we have done so far I'm gonna teach you in this program how to build scopes so that is going to be really amazing as we build out these scopes right now this particular let's open this up so you can see the scope that I'm working on for you it's going to be really training going to show you how to do scopes so this is the accounting application that we're gonna be building out and it's a massive scope right now we are at 34 pages as I build this scope out and then I'm going to show you exactly the stages of how to build out scopes project scopes and of course you can use that for your own projects or for your freelance projects whether you're building customers for yourself or with customers so I can't wait to show that to you I'm gonna bring that to you I'm working on it should be done in a few weeks and then we'll launch that program could it be amazing training teaching you how to design develop and deploy your own excel applications alright let's get into the training so what I basically want to do is I've got a login system as previously trained and users have the ability to add an username password and login but as what is so common and you know in websites users tend to forget their passwords and this is puts a lot of additional when we have this in Excel then that we require them to message the administrator ask them to change their password so can if you're using all it's got a lot of users this could put a strain on your administrator but what I want to do is I want to give the user the ability to reset the password and I want to give the administrator the ability to turn this feature on or off so we're gonna add that in right now we're going to show you it's gonna be fully automated so that the passwords can be changed and of course this would require email addresses too so let's get into the admin and show you what we've previously well all we have is a list of users username passwords and whether they're admin I want to add on to this in order for the users to reset their own password of course that needs to be emailed and we want to add an email and I also want to add in the ability of course this is an ADD screen only the administrator would be able to see this so want to add in a setting that would allow the administrator to turn on or off this feature so let's add that in right now allow users to reset passwords okay so we wouldn't make that an option I also want to add an email on to this so we want to add the ability to email so users gonna have email so that's really important all right let's update this table I'm gonna format the cells and update the border accordingly with that thick border all the way around then I'm going to just add in the dotted line on the left you can see how we quickly format this table bring it up to look the way it should and then I'll put a top border here just as we have it here and we'll merge and center this unmerge it and then reemergence enter this so that we can alright so now we have the ability to add in an email address here along with it that's important if we're going to be able to giving users the ability to to reset their own passwords we want a send a key a specific key to them probably something like a temporary password or a reset key to them so we want to be able to email that to them if they don't have their email address of course they wouldn't be able to do that so that's the key that's why we need the email address and the admin sets the email so that's really important all right now we have a space for the email and I also have a space for them and I want to put in yes or no give the admin the ability to turn this feature on or off so we're gonna put into data validation here under data and data validation I'm just gonna give it a yes or no validation under the allow we're gonna create a list and then we're just gonna create two different options yes and no that's all we really want here so now we have the ability to choose yes or no we'll turn it on for yes and then I'm going to merge the center this update this table and I'll go ahead and color this just so we have that consistent color okay and put a border around it so it makes it look nice now we have the ability in the attitude to turn this on or off and I want that because sometimes you may not want it you may not want to give the user the ability to turn this feature on sometimes you so this gives us the control and what we're gonna do in the pop-up screen is we're going to have a little text that says allow the user to reset the password or not or forget pass or something like that okay so now we've got the email address and let's just go ahead and add in my email just so we have an email address under there so now you can see we have an email address I also want to add some more fields we need to know what the reset user pasture name is so let's put in reset user name because I want to know if they put in their user name we need to pull in so example we say do you want to reset your user name they say yes they say we say put in your user name because we need to make sure that we have an email address so we need to have a field for that so we'll put that here and then I also need to know the row that that's not for example if we put in Randy here I need to know the row of this end so we need to put in the reset user row here I need to know the row of this we can easily use a match but we'll do if air equals if air in case it's not in case it's blank and then we'll do the match what I want to do is I want to what I want to look up I want to look up this I'm gonna look up this value user name and I want to look it up into a named range that we already have under user name so if we look in this user name this is the list of user names and we have that here all automatically here on the list user names and then I want an exact match so we're gonna put zero comma zero and then that's gonna give us an exact match and then what if it's if it's an error I just want to put double quotes that way there's no error message so that means it's gonna be blank if there's an error so that means it's gonna tell us the row number but this is not really the row number this is the first value I want the row number how do we get them row number we add four since Randy's on the throw five and it's coming up one because it's the first value so let's do that let's add five plus four excuse me plus four is gonna get us five now we have the row number okay and of course if it's blank it's gonna be empty which is exactly what I want what else do we need well I need to know what the email is if we're gonna be sending it out I need to track the email so we can do that user email based on that all right so we've got that and I want to know what that email is I want to put whatever when they put into user name here I want the email to displayed here well how do we do that well we can again just use probably an index match and of course wrap better on it if Eric if air okay index what are we indexing wyman index all the emails so let's highlight those I'm gonna index all the emails and then what is the raw number we'll just use the match again because we need to know the match in the table match we'll just use that again because I don't want we could use five minus one we want one in this case so we're gonna look up again Randy because this is gonna be the rule and then we're gonna look up the array again we're gonna do user names and then zero we need a match type zero and then what is the column comes one again so one we've already won all right so then what if it's an air double blanks now we built that formula again let's look at that Eric if there's an error there's gonna be double blanks we're gonna index what are we indexing we're indexing the entire email column what row are we gonna find we're gonna find this from row remember we want to return one because we're starting our index than this or starting our index in five so if we were to use row five it would return the fifth one down we don't want that next up we want an exact match and we want the first column since we're only indexing one column and if there's an air it's gonna return place that's gonna get us our email address as soon as we remove that both are gonna go blank cuz its airs okay good because now I have their email ders what else do I want I want a space for the temporary password or temp let's just call it temporary key okay I'll call it reset key let's call reset key because what I want to do is I want to generate a random reset key and then I want to have that RESET key email to them then I want to take that reset key I want to plug it in I want to see if it's a match if it's a match give them the ability to put in a new password I'm gonna walk you through all that right now so this is the place we're gonna put that reset key right here let's highlight these a little bit differently so we know what we're working with okay and now we see the zero these are all the fields for the reset now that we have all those we've got our email address we have our then we can expand and now let's go is start working on our forms okay so we've got everything here in the admin that we need which is perfect so as we see we've got a login we click login we've got a form what I want to do is I want to add a text to this form so let's go into the VBA and I want that text at the bottom and I wanted to say something like forgot password and then with a question mark just like you would if you were to log into a website that's the what I'm trying to do so let's go into the developers tab and go back into the form and edit those forms those user forms if you don't have the developers tap open of course you can find it here under the options and then the customize ribbon and make sure you select the developer right here okay moving on let's go into that alt f11 will get you there and here we have this user form I've got just one form login form this is the form this is the form I want to make a small edit to so I'm gonna bring this down extend it a little bit and I want to add a text field to this form let's maximize this a little bit bare so you can see alright so this is the form that I want to edit now what I want to do is I want to add a small text field so we're gonna click a label here this is the one I want to add you know I put it let's Center it so let's make it all the way across and then we're gonna go ahead and Center I want to work on the properties here so let's click on the properties and I want to scroll down here and I want to first I want to Center it so we'll double click here I'm gonna go into the font and I want to make some changes here I wanna make it a little bit bigger it's currently 8 10 I want to make it oblique and I also want to underline it and then I want to give it a color blue so let's take a look at palette and then let's give it a blue color alright that's about right and I want to give it a name the first name just in case that we got forgot password link and then the text that I'm gonna sign I just want to say forgot password with a question mark so that's good there okay great so now we've got that and what I only want this to display if the user has said yes so that's gonna be on the initialized form so when we double click this form we don't need that all right so what do we want to happen when we initialize when we activate this form we want to run a check so let's go into the user form and then activate this is what I want to focus when it activates we want something to happen we want to run check if that field is yes we want to show that text if it's no then we don't we can write some code if sheet 2 dot range g2 dot value equals yes then and then what we want to do that would be show reset password text that's what I want to happen and if and then else so if if it's a yes then what I want to do on a display that text so forgot password link that's the name that we assigned to that dot visible equals true and what if it's false what if it's not yes then I want to hide it forgot password link table visible equals false all right so that's pretty much always loop and if ok so let's test it out and see how that looks all right go back into the login screen login forgot password ok that's showing up now go back into the app and change this to no back into login and we see that that text is no longer there so that gives user the ability if not so now that we have that let's focus on what happens when a user clicks that what do we want to happen well what I want to happen is I want to have first I want to give them confirmation so for example when they click and I want to say are you sure you want to reset your password so let's focus on that right now what happens when they click that so back in to our VBA code we want to go back in to the code here and see what they have what happens when they click on the link so here we have it so we want to find out that forgot password and what happens when they click it that's what we want to focus on forgot that so when they click it something's going to happen well what do we want to happen I want to first run a message box if message box what do I want to say would you like to have a password reset email sent to you sent to you and then this gives them the option , and then that says yes VB yes or no give them the option VB yes or no and we can close this property for now so and let's give it a title we want to give it a title so we can call it reset password reset password that's the title on our popup message box equals VB know then we're gonna exit the sub so the VB know then exit the sub then exit sub so that gives them the out just in case they want to see what happens and they don't really want to reset it so that gives them the option to get out okay so but what if they do in that case what I want to do is I want to hide this login form so let's hide it I want to hide that they're not gonna need login form that's the name of this form dot hide I want to hide that the first thing and next what I want to do is I want to create a brand new form and I want to display that form so let's call that send password reset dot show it doesn't exist yet so it's not gonna come up so let's create it now right click insert user form and I want to give it that name right so let's back into the properties and we want to give it a specific name let's call it send password reset that's the name we just aside and give it a color of let's say green so we're going to give it a back color click here and go to the palette and we'll give it a light green and we want to give it a name also so if we scroll down here we can give it a caption call it send password reset okay now we've given it a name I think that's good enough give it a title so clicking on here will give it a title again extended all the way over here bring that title and I want to Center that title and give it a name let's just call it the name send password reset password reset I want to Center that so the text aligned double click on that that's going to bring it to the center go back in to our font here currently Tom which is fine but I want to increase it to maybe 14 give it a bold and that should be okay send password reset okay that looks good now what I want to do is I want to have them put in their user name because that user name is going to check for an email we need to make sure there's an email so we want to put in there but let's make this opaque and transparent not get out of a pic now let's create a new label call it inter user name we want to prompt the user to enter a username so let's do just that and call this inter inter user so now we've got that we also want to make that bigger it's very hard to see so again with this back in to the font and we'll click on the font and make this bigger maybe let's say 11 okay that looks good and then I want to write justify that just so it looks better right justify all right so now we have that we can bring shrink it down a little bit in case you have a long user now we want to enter a field create a field so let's create a text box here and give it give it enough space and we want to make sure we always name this user name box okay just so we know and now that we have a user name box we want to also increase the font on this otherwise it's going to be too small so into the font again we'll go back to size 11 and we can also default this to make it align left' okay line left is good so right that's good alright so now let's create some buttons on that I would like to create a brand new button just that we have the ability so let's click on the command button and create a send reset key button so sin three set key so also we're going to increase the font make that look a little bit better we don't want to have that so going back into the font on this button I'm gonna make this twelve probably bold here let's see if it fits in that's good and give it a back color of dark green and then we'll give it a white font something lights a bit too dark and then now white font on that so it's easy something clear we also want to give the user the ability to send reset key that looks good and then we'll also copy that control C control V and then I'm gonna make a cancel button give the user the ability to cancel out of that so then we also want to make sure we name these buttons properly so cancel we can make that button small so now we have two buttons and we're gonna call this one cancel button and then I'm gonna make this send key button send key button okay so now we've got our form pretty much set it's not the prettiest form but it'll it'll work just fine for our purposes so I want them to enter the username and then send the rekey and we're gonna check to make a few things so now we've got this so now let's go into this particular form and see what we need to do before we do that let's go back into the login form I'm gonna go into the code and make sure view code so make sure we have now before we hide this I want to make sure that we unload the send password reset so that means this brand new form I want to make sure to clear all the fields so we can do that using unload unload send password reset what that's gonna do is clear the form that we're gonna be in case there was any form fields that were seeing so before we show this form we are going to clear all the fields unload San Pass will clear all the fields in them so we've done that now we're ready to go let's just test it out and see how it's working login forgot password alright sad let's rename this it's not I want send not sent said okay now we got it now let's play it okay forgot password and Dario that's working perfectly so now we have sin password wreaking and that's exactly the way we want it perfect so it shows up just fine test it again login forgot password would you like to have this password reset email to you yes and then send it okay so that's gonna work good so far we've got got it working good now let's continue on with our code and start building these features out so back into the sent and we want to view the code here here we have not much he'll score there's nothing going on here so we want to build out some of the coding or so we can get those features so the first thing we want to do is let's we can bail out the cancel button so if the user cancels it what do I want to happen I just want to hide that form so so we're gonna send password reset dot hi don't hide that form so if they press cancel it hides the form and does nothing else next up what do I want to do all I want to do some actions what if they click on the send key button I want to run some checks as soon as they click that by the first thing I want to do is check for a proper username so let's do that just now we're focused on sheet 2 with sheet 2 that's what we're gonna focus on check for okay so we check for the properties and how do we do that well we want to make sure to place the username I want to put that username right here b11 the username that they entered put it right there so how do we do that sheet blue dot range we've already putted be 11 dot value equals what is it equal it equals our name let's take a look at the field value we said we called it username box right so that's the one we want you to use your name box dot what do we want value so the value of that we want to put in to be 11 that's gonna take that username and put it in there and then I want to calculate the sheet dot just in case those formulas I want to make sure to calculate that that way this formula and this formula are calculated and as soon as it's a proper username those are gonna have values what if it's not a proper value they're gonna be blank so that's how we run a check as soon as we put the username in the eleven we get a proper row and we got a proper email so the first thing we're gonna do is make sure that b12 is not blank that's how we're going to test it out so let's do that right now if sheet 2 dot range b12 dot value equals empty then what do we want to do then we say we want a message box let the user know that it's an improper username message box please enter a correct username exit sub nothing else to do exit sub which want to exit keep the form open and just exit the sub okay so now assuming that b12 is not empty we can move on and so let's do this right now then what I want to do is I want to run a macro and I want to hide the username so let's row let's create a macro brand-new macro that's gonna help us insert okay we're gonna have brand new module create some modules here and we're gonna call this send password module send password email okay that's gonna be the module and we can create a brand new macro called send sub send reset key okay that's gonna be a brand new macro that's gonna send the key so we can use that macro name back in our sender so we go back into the view code so what do we want to happen I want to send run that matter which sends a key and I also want to hide this form this form that we're we no longer need to send password reset dot I want to hide that form okay so now we're done now that's now that macros done so this will run the macro although we haven't written the macro yet as soon as we know the usernames good and with the next one we'll double check on the email too so let's go back into this and the macro that we're now gonna work on this is the macro that's gonna actually send the email okay so let's start writing this macro that's gonna send the user the reset key if we're gonna dimension the password text we need to create a random text and I'm gonna show you how we do that as a string so we're gonna create that password text that's gonna allow us and we're gonna dimension the password count because I'm gonna create an eight character automated and random unique control key so I want to create I want to count the number of characters so I'm gonna show you how to do that password count as long and of course for the email we're gonna mention the Outlook application as an object in late binding and the email as well at the mention Outlook email this is called Outlook mail as an object I'm gonna focus on sheet 2 so with sheet 2 now we can start writing our code I want to make sure that the password text just incase password text equals empty just gonna can't be clear that out cuz we need to reset the password text and the first thing of course we can't set an email if there is no email address so we want to make sure that there actually is an email address so we check for address if dot range e be 13 run be 13 is where our email is located right in here B 13 want to make sure that's not blank so we can can actually send them B 13 value equals empty then what then then what message box let's look these in a message box there is no email address associated with this user okay and then exit sup so that way in case there's no email address there's there's nothing we can do okay moving on now assuming that there is we can focus on creating our new unique key so I'm gonna put it right here our eat our reset key is going to be located in b14 so I want to clear out b14 case there was any old reset keys so dot range b14 dot clear contents clear old reset key so that's important because that's we're gonna say now what I want to do is I want to generate a unique key and I want to make that key egg character so what I want to do is I want to create a random key all in letters okay all alphanumeric basically ABC and I want them all capitalized so now what what do we mean by that how do we do that well the first thing what I want to do is I want to find get a random alphabet so let's let's focus on a few things here so that that's gonna help us so what I want to do is I here's a character set in Microsoft web site here so the character character set I want to generate a character starting with an going to see well this character code is 65 so if I generate a random number between 65 and 90 that's gonna correspond to a letter right let's let's take a look at how that might work just so we can focus on just we can see exactly how that like what I'm trying to do here let's go back into the VBA code here and just write her a simple macro sub tests okay so a message box character alright let's just say 70 character 70 okay so now when we run this macro it's gonna generate F right F so what I want to do is I want to randomize this number all the way between 65 of course 65 is a and 90 is Z so if I'm going to create a random bunch of characters eight of them we run that we see that Z so what we want to do is we want to randomize this number on a random then I want to build up this text so if the first one whatever and I want to start building this 8 character so I need to make this number random well how do we use random let's take a look in the website and see what how we focus on random how do we use random this is a great set tech on the net and if we look in here random integer what do we need to do we need to use the integer we have an upper bound that means the top what it would be the top in our case or case it would be 90 because it's Z and the lower bound we in this case will be 65 that's a so this is gonna generate a random number between 65 and 90 that's what I want that's exactly what I want so we can use that in our code so what if we do that let's copy that and let's add that into our code let's update the code here and what we're going to do is instead of 90 we're gonna replace this I'm gonna paste that in here and then instead of the upper bound we're going to put in 90 and 65 and then again 65 the lower bound so we're gonna replace the lower bound and out brown with the actual Kony's now let's go ahead and run this macro and we see we have letter s and then we have the letter whatever it's random and so we get random letters perfect so that's one so now all we need to do is take this and build out eight different characters so we can do that with a loop right we loop eight different times and we generate a we generate this string of random letters and that's just what I have done in the code that's just what I want to do in the code so let's go ahead and see how we would do that so let's start our loop because we want to create a for password count equals one to eight I want to generate eight character random text next password count okay that's gonna be covered password text equals password text and right because we want to add on to it character and then integer double parentheses upper bound which is 90 minus 65 which is the lower bound plus one and then parentheses times the random random plus the lower bound that's the lower bound number that's the a and then double parentheses all right now I want to see what that might look like let's take a look message box and then password text okay so now we can get an idea of what that might look like if we run this knocker let's save that work so far and then run that macro okay that should be integer int okay let's run the macro alright so here we have our random text here let's run it again alright it's again random now we have random eight character so you can see a random all right that looks really good that's exactly what I want but what I want to do with that well I want to place that in a specific cell so let's take a look I want to put that in B 14 I'm gonna take that I want to place it right here in B 14 so let's do just that dot range B 14 equals password text so it's gonna put it places places the password key in so okay so now we've got it in cell now we're ready to write the email so we got all of it now we're ready to send the email so let's go ahead and set that email set the outlook a plate bind e equals what is it going to equal the create object want to create an object what type of my objects gonna be the outlook application object outlook dot all right so that's gonna create our application but now I got to create the email set the Outlook mail equal to Outlook app which is the object we just created out and then create item that's gonna create our email it's gonna be blank but it's gonna create an email with the Outlook mail what do we want to do with that we're gonna set the - dot - what is it - that's gonna be whatever's in right here take a look in b13 so let's do that - equals dot no we can't do dot range because we're already with the Haussmann so we gotta need to specify the sheet again because we're with it within the another with sheet 2 dot range b13 dot value that's the email address and then next one I hit the subject subject equal we can just put some text your password reset key is here do all in caps okay so now we good so now what about the mail now the body body we can also set to some text we could say hello and then maybe we'll put into user name and what is the user name sheet 2 dot range b xi b xi we have a user name b11 dot value BBC okay and that's gonna get a brand new line and what do I want please let's actually go to new one so it doesn't grow big new line please use the following password reset key to reset your password okay and then put a colon and then another okay so now we've got a brand new line and what I want on this new line I want to put in the password text okay so that's gonna give us a brand new line what do I want after that new line just something like thank you very much had just you can put whatever you want and now okay so now we've got that now let's take a look what do I want to do well generally you're gonna put send all right you want to send it you do not want to display it that's no need for that you want to send this to their email because you want to make sure it's secure but for our purposes we're gonna use display so I'm gonna put display and then I'm putting a comment you will you for sure you will want to change this to Dotson that's important okay make sure changes dot Sam we don't want that email displayed we want it sent all right that's it for now so we've got the end with this is the end with for the Outlook email this is the end with for the sheet and I want to I want to clear out the reset form I want to make sure because we're gonna launch the reset form and I want to show it but there is no reset form so let's create a brand new form that's the password reset form call it insert user form and we're gonna give this a brand new name I'm gonna call it password reset form that's gonna be the form that allows the users to reset their password password reset form so this is the form we're gonna focus on and it's gonna call it password reset form so back into the sent email what I want to do is I want to make sure that form is cleared out because we're gonna launch it unload password reset form that's the form we just created we will make sure all the fields are cleared even though we have an added a field yet and then I want to show that form password reset form dot show so that's important doc I want to show that form alright so we're done with that and now we can move on so now this email let's test it out see if there's any bugs or issues that we've created let's run this okay good it says to Randi or password reset key is here hello Randy please use the following Password Reset key to reset your password good now remember this is not gonna display normally it's just gonna send it because you're gonna use dots send all right so the email portion is working just fine you want to save your changes yes and it's gonna launch this user form of course we haven't worked on it yet so that's perfect that's what we want now let's build out that specific form into the VBA and we'll extend it down here and we want to give it a specific name let's call it reset password form and with a user form caption password reset okay and again I change the color on it because I don't like those grey colors move it to perhaps a light gray here like green here and we have it now let's also give it a title here so clicking on it we will give it a title call it password reset form now we have ant aligned to the center change the text going into the font probably back to 12 and bold should be good so now we have the password and I just want to make sure that this transparent background okay so we really want three fields here I want one that says reset key so I want to give them that reset put have them put that in and make sure it's correct then I want to give them two fields for their password new password and the repeat new password so let's create those three fields now so the first is we want to need some labels so we're gonna call it the reset ki reset key and we're gonna change that to a right justification also going to change the font once again and making this probably 11 as we have done before all right good so now we have this now let's create two more control C control C control V and control V okay so now we have three different labels alright let's make the modifications on those specific labels this one we're going to call this the new password and I'm going to give this one a specific name called repeat password' [Music] okay so we have those three now and let's make this a little bit larger here same here and the same here so they're all unique let's create our fields our here we have our brand new field so that they can put in this key doesn't have to be perfect you'll get the idea here okay and what do I want I want to put this is reset key box called box reset key box now let's copy that let's actually change the font on that before we copy that over into the font and make this a little bit bigger here and also I want to left justified here okay it's already on the left okay so let's copy and paste this field so we can duplicate it bring it down now we've pasted it down here and one more here and we can line those up on the left using the control and then on our line here left so now we've gotten the left okay so we've got tinned OHS and line it up a little bit there alright it's not perfect bring it down here so we've got the three fields now let's create some buttons for these so we can have that so we can set it up we want to create a reset password' a button that says reset password' and of course a cancel button so this one reset password' and I'm going to increase the font and increase the size a little bit going into our font bring it to probably 11 and also change the color just as we did just so that there's some consistency on that and then the font color we're going to change to white then we're going to duplicate that button for the cancel okay now let's copy that and paste that and I'm going to create a cancel button Kancil always give the user a way to cancel so they can close it because other words gets frustrating for them and bring this down right about here so give this the name cancel button give this the name reset button that way in the code it's very easy reset buttons very easy to recognize okay so now we've built out our form pretty good it's not the nicest form certainly but it'll it'll work make these transparent double clicking on that will do that just although it should be fine okay so now they're all transparent now we have a reset key we have a new password and we have a reset password we want the user to run through those so now we've built it now when we gotta just focus on the code portion of this the form is now built out so let's double click here and we have user form click we don't need that so now we're gonna build out the these form and there's just a little bit of code so let's focus on this and we can see that the first thing we want to do is if they of course if there's a cancel button we want to close the form so we have the cancel button here what do we want to happen when the user clicks that I want to hide the form and the form is called password reset form dot hide okay so now we've hidden the form in case they click the cancel button great well of course what happens when they reset now let's click the reset button where is that the reset button here and when they click that I want a few things to happen first thing I want to do of course I want to make sure they have the correct reset key check for correct reset key that's important we got to make sure that they got the email and they're copied and pasted that properly in if write what I want to do I want to check this key right here in b14 with the one that they entered into the field so how do we do that we can do with just this line of code dot range let's let's go over with sheet two because we're gonna be focused on sheet 2 so let's add this in with sheet 2 with sheet 2 and bring this back up so we can focus on that just sheet 2 what is our code b14 is where our key is going to be equals or let's just say does not equal so we can get out of it it does not equal what is it Emme which the form reset what what is the name we can't reset key box that's the name we give it a dot value if it does not equal then what do we want to do then I want to give them a message box then what I want to do if message box give them a choice message box the password though it's called the reset key you have entered is not correct and then give it a new line what I want to do would you like to try again would you like let's go into new line so you can see it what we're doing here would you like to try again give them the option VB yes no and give it a title incorrect reset key that's the title that little pop-up DB no then what what I want to do password reset form dot hot I want to hide this form if they don't want to try it again and I want to exit the sub after that okay and if we're gonna actually exit the sub regardless so exit sub because the key is not valid so there we've got that so if they give we're giving them a choice we're gonna hide the form no matter what we're gonna hide the form only if they don't want to try again if they do want to try again we're not gonna hide the form but we're gonna exit the sub we don't want to move forward because the password key is wrong okay assuming that it is correct we can now move on so let's continue on now we need to check for correct duplicate passwords check for correct duplicate password now we're ready to check for duplicate passage so let's check these boxes and double-check the names on those so backing to our form I want to view this form view the code view the form view object and now let's give it a name this one new password box have just a sign let's see new password box to it so we have both new so I want to compare the new password box with the new password box too so let's do that in the code right now go and right-click and then view the code if m e dot new password box 2 dot value does not equal m e dot new password box 2 then then what then do something we have to tell the user that the passwords do not match then the message box the passwords do not match please enter matching passwords to let the user know they need to Matt masking passwords and get rid of this loop that's just automated for the do OK exit sub and then and if ok so that way in case they don't match we can tell the user we need to match and boy Exeter ok moving on that means they do now we know that they do match if they've gotten past this point we know they do match so now we just need to update the new password so update the user password now all we have to do right ok so how do we do that we're gonna first we can hide the form because that we know we get password reset form form dot hide ok we can hide it because we're good and now we can continue on what do we want to do so I want to update the password what I want to do is I want to take column II I want to find the user right here call them E and whatever the user knows I want to update it with that password so that's what I want to do call it cheat to call an e whatever row here update the password so we'll do just that in the code so moving on dot range E and what E and what b12 value b12 the row and dot range b12 value this is our user row number this is where the password what does that equal equals simply M e dot new password box dot value there we go that's gonna update it now let's let the user know that password has been updated message box your password has been updated and then we'll just make sure please use this new password when logging logging in alright so and now we want to show the log in form log in form we want to show that show so they can log in properly and then we're good that's it so now we have we can get rid of this and if we don't need that alright let's test out this code let's double check we've got that all right good we've got n wit this is the sheet 2 and clean it up a little bit move on clean it up wishy to bring that out looks good let's run this let's see if there's any issues password we have the reset key we have that alright it looks good cancel all right let's run the full code and see how we're going to do that login forgot password yes I forgot my password yes I would like to have it resent to me into the username brandy send the reset key that's gonna launch the email let's copy and paste this carefully right copy that okay close that out paste in this reset key add in the new password one two three one two three you can also put these as password characters and they click reset the password okay we have and if one too many and if okay let's continue on okay your password has been updated please use a new password great alright so now let's log in Randy one two three with that new password I think it's the same as the old one okay perfect it worked good let's do it one more time forgot the password would you like to reset a password Randy send the reset key great got a brand new unique copy that go down paste in the reset key into the new password four five six four five six reset the password passwords been reset username Randy four five six click okay great and we look in the admin and we see that four or five six has now been updated all right thanks so much that's exactly how we reset password give the user the ability to reset the password thank you so much for joining me on this training if you like this video please share it like it and don't forget to subscribe thanks so much have a great day [Music]

Channel: Excel For Freelancers

Views: 21,973

Rating: 4.9732738 out of 5

Keywords: Excel User Password Reset, Reset User Passwords in Excel, How to Rest Excel Password, Multi-User Excel, share and sync Excel File, Excel Sharing and users, automate user reset password, User Passwords in Excel, Excel Multi-user, Multi-User in Excel, Excel username and password, Excel User Right, Excel user rights, Excel user security

Id: x80NpcBwwyw

Length: 51min 36sec (3096 seconds)

Published: Tue Nov 05 2019