The AMAZING TRICK To Add Different User Security Rights to ANY Excel Sheet and User Login

hi this is Randy with Excel for freelancers and today we've got a great lesson for you today where we're going to be using user login and security rights to show you how we secure our sheets and users and giving them specific identities and specific security for each sheet and for each user so stay tuned it's gonna be an amazing lesson alright let's get started happy to have you here the idea of this training is that we wanted administrator or somebody who owns the workbook or is administering the workbook to be able to give certain rights to the user and those rights would be to either view a specific sheet and bill to edit it or view it only without being audited or not even be able to view it so basically we want to assign we want to give the ability to assign three different types of rights to a user and we can do that with this specific table is gonna help us what I've done is I've set a one column simply with user names and then you can change the user names add to it and then the second column has a password okay so we've got a password we're gonna be able to hide those passwords a little later and then the idea is what I want to be able to do is I want to be able to assign individual rights for each sheet so for example if we want James to have rights only viewable rights to jitu but not editable I want to be able to double click and do that so what we're gonna do is we're gonna use symbols or icons to denote whether what type of rights that has on it so we can do that by adding some different types of symbols and we can do that in the symbol area so we're going to go ahead and insert symbol and under the font of webdings webdings you're gonna get a whole array of different icons and shapes and one of the ones that we're gonna be using is will use a unlocked will use this unlock here I also have it down here I want to use that one for when the sheet is open when if she can be used so we'll go ahead and insert that and we'll need one of those and then I also want to enter something when it's locked when we can when a user can view it but not edit it so I want to find a symbol for that so we'll go ahead and use this locked one here also webdings and we're going to insert that okay and then I also want to give the administrator the ability to assign the restricted access which means that they cannot even view the sheet and will assign a different symbol for that so here we're going to go ahead and put the net and we'll in also found on here about midway through you can find basically the icon that we're going to use for unable and it's going to look just like this here okay so we'll go ahead and insert that okay so those are the three types of access we want so the unlock is going to be for full access the lock will be for viewable access but not editable and this icon the band icon or whatever you'd like to call it we're going to use for not even viewable okay and so remember these uses the web ding font so what we want to do is we want to chain make sure the font is set for the entire table cuz we're gonna use that throughout this table so we'll just go to home here and then we'll change that font to webdings okay and now we've got the entire so now when we use this it'll be this font and I also would like to assign a color I want I want it to stand out the differentiation between the icons so I want a different color for each type so what we're gonna do is we're gonna assign three different conditional formats so we can highlight the whole table okay and then what we're gonna do is we're gonna go to conditional formatting and we're gonna go to a new rule and we want cells that only contain a specific value specific text and what is that text well for the unlocked it uses a special code for the locking uses a special code and for this one they use a special code so we want to know what is that special code so let's cancel that out and the unlock uses this d shape okay this D shape so we've copied and pasted that and now let's go ahead and select the table again well back into the conditional formatting and we'll go ahead and add that new rule format that contain a specific text and that text is going to be that D shape okay now if that's selected that's our unlock so why don't we go ahead and color that perhaps let's go ahead and change the font to a green color okay Green that way it's gonna stand out because it's green okay and next we have our locked okay and that's that little high with the two dots so we're gonna copy that okay and once again select the entire table go to conditional formatting new rule format cells that only contain a specific value okay and then we'll go ahead and paste that I with the two dots above it now we're gonna set a color for this one too okay and let's go ahead and put this I'd like yellow or maybe orange wanna go ahead and put it Orange okay so we'll go ahead and click OK okay and next we want the the band color we're gonna make that red so that way it stands out we can easily see the different security rights highlighting the entire table let's go ahead and copy this it's an X so that's an easy what I'm gonna need to copy and paste that so that is an X and then a new rule format cells that only contain a specific text and what we're going to put in X here okay and we're going to give this assign this color the font color to be red okay great very good now you can see we've got the three different colors for each shape so it looks good and what I basically want to happen is when a user double clicks on a specific cell I want it to change I want this icon to change I want it to go in a process I wanted to go from unlocked to locked to banned okay or hidden let's just call this a hidden because she's going to be hidden so I want that process to go on and we can do that through VBA so why don't we go ahead and go through the developer and click visual basic alt 11 if you want use a shortcut keys if you do not have the developer simply go into the file and then go ahead and the options and you'll go into the customized ribbon and make sure you select the developer here and then that will get you your developer tab here so go back into the visual basic and now we have our visual basic up here and the idea is here once again that if we double click on any and anything here we want something to happen so let's go ahead and invoke our double click and now we have to do it for a specific range we're gonna start with g5 actually we're gonna go through let's go ahead and start with H 5 G's gonna be for a different purpose we're gonna use it for selecting all it's gonna be great but we'll keep that separate so H 5 through m 24 H 5 through m 24 is the range that we're gonna be working on so we go ahead and click on the admin sheet that is the house sheet that we're working on and what we'll do is we'll go ahead and select the worksheet and we're going to use before double-click and we couldn't use we could use a selection change but I'll show you the differences so you can see them so we'll start with before double-click which basically means when a user double clicks something that we want something to happen okay so let's go ahead and race that so we're gonna use it so we're gonna now we want to tell Excel hey if the user makes a change when there's a double click this is what happens so if not if not intersect okay and then target comma range and what does that range again remember it's H five through m24 H five through m24 okay then in parentheses is nothing then okay and now now we said okay if they double-click we want something to happen okay but what do we want to happen okay well I'll tell you what let's go ahead if if it's nothing okay if if it's know if it's this right which is the D then we want something to happen and we wanted to go to the lock screen if it's this we want to go to this so basically we're gonna go in the process of that order all right so let's go back into our VBA editor will open this up a little bit we can close this for now and what we want to happen is we want to say if it's D then change it to I so it's pretty simple so all when you do is copy this little little text there and we just want to say if target dot value equals then we'll copy paste that D then target dot value equals and we're gonna paste that I in there okay let's go ahead and copy that okay and then we'll paste that between the quotation marks in the VBA and paste it right there all right so let's go ahead and take a look and see how it's working now if we double click on there it changes perfect okay so that's what happens and now we can now we can write some additional code so we want to say if it's this let's change it to two this so if it's eye we're gonna change it to X okay because that's going to change it for us so we just have to copy and paste this code again and we'll change the we'll change the text if it's going to be this eye so let's copy the eye right then change it to X okay there so now if it's this it's gonna change it so double-click perfect okay and now once again if it's banned we want it to go if it's this hidden icon we wanted to go back to unlock so we wanted to basically loop so we can do that under with another copy and paste so what we're going to what we're saying is that if it's X this time then we wanted to change it back to the D okay and there's one other condition that we have to write which we will do and that is if it's blank okay so we'll go ahead and include something for that as well and so if it's blank I would also like to write something that basically states if it's blank let's go back to D so if target dot value equals empty okay then I wanted to give it that unlocked then target value equals and then we'll just say let's say it's we want it to be unlocked okay there so now we've we've gone through every so now if it's blank okay we can go ahead and now we got the unlock of its blank so because of that last line of code and we do have to write a little bit more one issue we will face if we don't write a little bit more code here is basically in this when we change this to I write automatically it's going to then see this and change its gonna keep looping through and looking through so we just have to put things in a certain order and then exit so so we want to make sure that if it's I let's go ahead and put it if it's D then we're going to exit the sub okay the reason we we doing that is is it's gonna loop if it doesn't in other words if it changes to automatically that's going to go to this column say oh yeah it's I then change to six so it's going to go automatically from because it's going to go from this line it's gonna change it to I right and then right away it's going to say oh it's I want to change the six so we we basically want to say once we've made the change let's exit out of it okay and we can we can switch the order if we switch the order from x2i right if we switch this order bring it up here okay now we know I will come last so that's gonna that's going to take care of itself for us so that that will that will keep them from looping it's kind of an ordered thing that's really going to help us okay so let's go ahead and take a look now I have the code alright we can check to see how that's working double click on this great it goes to locked and then it goes to hidden and then back to lock perfect and if it's blank should go to unlock all right great so now it's working now I would also like to add one other ability it's it's uh takes a little bit of time to see I mean especially if you have a lot of sheets to have to double click on every single sheet so what I'd like to do is give the user a ability to select simply select them all and it will change all of the sheets if we use this here so that I want to do that that's going to give the user a lot of big time-saver and we can do that relatively easy with just a little bit more code and in this case in this case it's not going to be range h5 it's gonna be a different range okay so we can just copy this one although the range is going to be different alright in this case where we're working on with just column G only column G right so we're gonna change this to G five through G 24 okay and make sure we close that with an ENDIF okay now what do we want to happen well what we want to happen is if a user clicks anywhere in column G we want the entire row all the sheets within that row to change based on the value as well in column G so we can do that almost the same as we did above except changing the range so we'll go ahead and copy and paste okay copy and paste what we've done before and we'll just make some slight changes okay if the target value equals D then we're not going to change the target value we're gonna change this entire row so we can do that with some code range G okay and the target row because we don't know what the row is so it's going to be whatever the row that the users clicked on target dot row okay and the colon marks is it's gonna be a range and through all the way through M and the target dot row okay and then we're gonna close that out and that should do it we've got to put the beginning quotations here okay so basically we're saying is change the entire row g3m changed to this and then we're gonna put a dot value in there because we want the value to be that okay so it's the value the value in that range every value in that range we want it to change and we can copy and paste this okay do the same thing for this instead of target value we want the entire value of the range same thing here and the same thing here okay so what we're doing is basically saying if now if it's blank our first one is going to be empty then we're gonna change it to all unlock so if we double click here now they're all unlocked if we double click again they're all go to lock if we double click again they all go to hidden and then back to unlock okay so that's working really great so that's gonna that's a great way to change the security on all the sheets to the same and then of course you can go individually and change each one but it's a nice way of changing the security for all the sheets at the same time great all right now we're gonna go into writing the another macro for this alright so when what happens is when the user logs on I want the macro to run through all of these sheets right and hide some lock some and show some so we can do that with a macro so let's go ahead and right-click anywhere insert a module and then we'll go ahead and click the properties for this and we'll change the name because I always like to name my modules and then we'll just call this workbook security or any name you want as long as it's not the same as a macro then you're okay okay and we can close that out to save space right now and so what we're going to do is we're going to start a macro here and we can call it sub check user because we want this macro to do two things we want a 1 once the user logs on we want to check to see if that's a correct username and to see if it's correct password and if both of those are true we then wanted to hide or show sheets according to their security so we can go ahead and do this first we're gonna have to dimension some variables we're gonna need to use a user row user row and we'll define that in there sheet column right we want it to go through each of these sheets so I'm going to define these sheet columns okay so we're gonna go through that as well and so she column is going to be another very one those are both long those are both whole numbers and also sheet name sheet name as string okay and then what I want to do is I want to specify we're really only working with most of the things in sheet 1 which is the admin ok sheet 1 here you see admin so we're gonna go with with sheet 1 and just for your reference when we're working directly on a sheet when we're working directly on a sheet you notice we don't need to reference you see we just start with range we don't need to reference the sheet because we're on the sheet but we're wearing a macro we always have to reference what sheet so in this case we're gonna do sheet 1 and then anything we do so the first thing I want to use dot calculated okay and that's gonna calculate some formulas that we're gonna put in right now okay so and I will go through that and basically what what we want to do is we want to set formulas for our users so that we know what user and if password is correct so we can do that we'll just slide over here into our workbook and I have just some open names and we're gonna put the username and the password here for now but we're gonna shortly we're gonna create a pop-up form to handle that for now we're gonna go ahead and put in James and password 1 2 3 ok temporarily and basically what I want this field to do is to tell me is this password correct okay I need to know if this password is correct so we can use a formula for that but first let's define our table username and passwords let's go ahead and define them okay and we can define them we'll call that user table so we can just enter I've highlighted the entire table user table okay and the next one we can define as user name so that's going to be e through 5 through 24 okay so let's go ahead and assign that a name okay we're going to call that user username so basically we're referencing this table and now we can use those references in a formula so it makes it a lot easier and it's also clear so the first thing we want to do is we want to use a we'll use an index match which is a very common formula accommodation to find specific feed variables so what we want to do is we want to say index okay what are we going to index okay what are we gonna we're going to index the the entire table first so we're going to call that the user table okay so we're gonna index this entire table use your table okay and now we have to specify a row number well we want to know what row is the username on and we want to know if that password is correct okay so we're gonna use match match what are we matching we're gonna look up James okay and we're gonna look it up in what array what table well we just created user name so user name so we can reference it so that's the table and the Z we're going to use this zero because we want an exact match it's got to be match exactly so we're gonna use the exact match and then we're going to go ahead and close that with parentheses okay and then we need to specify the column right now remember the column in this table this is column one and this is com2 so we're gonna use column two okay column two okay so now we'll check it okay so basically all it did is return the password so basically what I want to say is hey if this password matches this password it's right okay we can check that and we simply have to add an if statement there so we can say if Dennis value is equal to this entered password then true otherwise it's false okay and right now it's false but if we change this to the actual password by changing this to a W that'll change the true so now we know we've got the correct password okay the next thing we want to do is we want to define the user row okay we want to know what is the row of the user because we're gonna need to use that row when were when VBA is is going through we can get that row in VBA but I don't want to show you here on a formula - so there's a few ways to do it and we can do that basically with a match statement match and then what are we matching where we're gonna match James and then what are we matching it with we're gonna look up the Ray username okay and then also we want an exact match on that so we'll go ahead and use that zero and then remember if we just entered that it's gonna enter one right but we don't want one one is the first row in the table which is correct but actually it's the fifth row of the table but it's the first row in the array so we'll always want to add 4 to that okay so plus 4 is going to give us the correct row in the table and we also want if it's wrong though if it's wrong I don't want an error message to show up here so what I'm gonna do is I'm gonna write if care okay and then go to the end I'm just gonna create an empty space double quotes so if there's an air if it's wrong I just wanted to show up I don't want to show an error here because I'm gonna have vba read this so that way if it's wrong it'll just be blank and we can and when we write code in VBA then we can test to see if it's blank or if it's not blank so back into our VBA we can continue with our macro that we're writing now so what we'll do is we can we now we've calculated a sheet and the reason we've calculated in the VBA is because we want those formulas to make sure after the user has entered the username and the password we want to make sure that she calculates so that both of these formulas can calculate and now what I want to do in this macro is we're gonna use this macro when the user presses okay on the login form I want to test okay but I'm gonna run two tests one I want to see if the username is correct - I want to see if the password is correct and we can do that with two if statements the first of which is if dot range okay b8 remember that's our user row b8 dot value equals empty remember if there's any kind of an air it's gonna be empty then okay incorrect we're just going to make a comment incorrect username so we know then what do we want to do well first I want to tell the user that it's not correct message box okay please enter a correct user payment okay and then we're gonna exit out of the sub we want to exit it we don't want to continue any further until they put in a correct username so and okay so we've got those two things happening if it's own and now if the password is wrong we want to write an additional gift statement if dot range okay and in this case b7 remember it's that true right we want to make sure this is true right b7 and if for some reason they entered an incorrect password and you will get an error or when I get a false so what we want to do is we just want to say if it's not true in this case if b7 does not equal true right so true is the only condition that we should continue on if it's anything but true we want to end it then incorrect password' okay in this case message box please enter a correct password okay and exits up okay and if okay so now we've got this so let's go ahead and test this out and we'll just run it okay no problem no errors because we we we do have a correct but what if we change this to let's say James to a non-user okay and now we'll run it okay please enter correct username good okay and now let's go ahead and enter an incorrect password okay and we'll go ahead and run that and then make sure we get that police enter a correct password okay great so that's working well and let's go ahead and put that to a correct password now we can continue with our VBA now if they get to this point we know that they have entered everything correctly so we can continue on at taking care of hiding or showing or locking or unlocking sheets okay and to do that we need to define now we should define the row because we need to know what row we're going to be working on so use a row remember we've dimensioned it up here equals right b8 right b8 here okay dot range b8 okay dot value that's the user row alright we want to make sure because that's really important and then what we want to do basically is we want to we know the row now we want to run through every single one of these columns and based on the icon here we want to either show and unlock the sheet we want to show and lock the sheet or we may even want to hide the sheet completely okay so those are the three dishes that we're going to test for now and we're going to do that for each sheet in the workbook so we can do that with a four next statement and what we want to do is we're going to go through the columns right but but we want to know what columns they are right so G is the all pages so we don't need that we're gonna go through columns H through column m okay well I need to know what column it is equals so just as this will help us column okay this is gonna tell them that this is column eight okay and if you drag this over there we want to go through eight from from column eight to column thirteen okay so we want this macro to run through all all of these columns so we can do that with a four next statement and we can start out for sheet calm remember we've defined sheet call them up there column equals eight to thirteen okay and then next column all right so now within this loop all of our action of hiding or locking or unlocking it's going to take place the first thing we want to do is we want to define our sheet name and I've done that through a formula here where we basically define the sheet names through this formula and this formula that I've added basically takes whatever sheet name in a one or a three so if you change the sheet names they'll automatically change to okay and now you can see here in this that the sheet name has changed so that's going to help us it's just this simple formula basically it's going to take the sheet name of a specific cell for each of those things so if you add more it's very simple to add more columns and add more sheets just with this formula here so I hope that'll help you out so now that now that we know we're going to define the sheet names now these sheet names are in write row four and we know the columns okay so let's go ahead so the first sheet name is going to be let's go ahead and define that sheet name equals and in this case we're gonna use dots cells normally we've been using dot range but dot cells allows us to use a variable for both row and column right in this we don't we don't need we don't need because we know the column it's B but in this case when when you want variables for both cells and for both columns and rows will use the the cells option so in this case cells we're gonna start out with a row user row okay cuz we know the user row we've just defined it above and what is it column sheet column okay dot value okay and that basically that's gonna tell us the sheet name so what it's gonna do is I'm this loop it's going to go one the sheet name is admin remember row four okay and it's going to tell us and we don't need let me go ahead and change this you should be a four so because we're just getting the sheet name and the sheet name is only in row four so we've done that now what we can do is now that we have this sheet name we can go ahead and run our if statement and we're going to run an if statement based on the type of security we have for that so let's go ahead and write that code right now if okay dot cells okay now we can into the user row user Rho dot sheet column dot value okay equals and then remember we have okay let's start out with that D okay if it's unlock which is this D if it's that what do we want to happen well in that case we want to make sure that it's not protected right and we want to make sure then okay okay so we'll go ahead and write an end if so we want to make sure it's not protected so we'll go ahead and sheets okay sheet name remember that's the sheet naming that were referencing dot just it may have been protected before so we always want to make sure it's unprotected I'm in this case unprotect and then we'll just go ahead and we're gonna use a password protec we're going to use a password of let's just say 1 2 3 ok so and also it may have been hidden before if if for a night if another user you know when we open this workbook we're gonna have all of the worksheets hidden because it's important we don't know if the user is gonna have all the security rights so it's important to hide them all in the beginning so we want to make sure in this case that it is visible so sheets again she I'm gonna go ahead and spell that one above right add another either okay sheet name dot and in this case visible okay equals x L she visible okay and what that's going to do is that's gonna make our sheet visible which is what we want because it's if it's has that D it's going it's going to be completely unlocked okay so she named so basically if it's unlocked we want to do two things we want to unprotect it and we want to make sure it's visible okay but what if it's what what if it's locked and visible okay what if it's this one here okay which is locked but visible so we're gonna copy that icon there and then we're gonna go back into the VBA code and we're gonna write some we're gonna write some code that allows us to basically keep it visible but log it so let's go ahead and paste that little eye there and I'm gonna copy and paste this code here okay and then copy that again and now I'm going to say basically in this case if it's eye which is that eye which is locked I want to protect it right we don't want we want this protected and visible okay protect it and visible that's for our second condition now we have one more condition okay and that is we want to make sure that if it's the X remember the X right that we don't we don't want that visible at all so we can write some code if dot cells use a row sheet column dot value equal X and then in that case in that case we can just make it very well hidden so and I'll explain the difference in that then right and then let's go ahead and copy that and then in this case we're not gonna make it visible we're going to actually make it very hidden okay and I'll explain the difference to you very hidden there's actually three states that a sheet can be if it's let's go ahead and in the admin screen let's go into properties okay if you look on this sheet it's visible okay you see our admin sheets visible but if we if we hide it if we make it hidden sheet hidden okay it's not there anymore however if I right click drag it down so you can see a little bit there we go and so if you right-click you can see on anything that here it's unhide right and you can see it's right here so anybody can unhide that she's just by clicking unhide okay but you sometimes you don't want your users to be able to manually unhide sheets like that so what we can do in that case is make it very hidden and I'll show you how to do that and in this case we can make it very well hidden very hidden and when you right-click on that it won't be there won't be any way to unhide it the only way you can unhide it is by going into the VBA directly clicking on the sheets here and then changing the properties here to visible okay so in our case for this but we probably you we probably want to make a cheap very hidden otherwise it'll be too easy for a user to unhide it so what we wanted to make a cheat sheet unhidden so we'll go ahead and write some code for that and we'll close out the properties now and in this case what we want to do is go back into the security here and we want to make it very hit okay and that's the only we don't need to worry about the password although if you want you can certainly protect it and make it hidden as well so that would be no problem but it's a bit overkill so now we've written our code okay now we have we can go back into our admin screen let's go ahead and unhide that and make that visible okay we go back into our admin screen and here we have our code so if our if James and our password is correct we're gonna have a run-through and we can lock these let's go ahead and lock lock these okay okay right now right you can see you can click on these sheets no problem and review there let me unprotect that just so we can set that so that you can see how they're protected and then so now you can fully select and once we protect them you can you can choose it'll remember so let's say we don't want to lock so so let's go ahead and and unprotect that but it will remember what you did before so when we protect it with the password it will also remember that we don't want to select the locked cells okay so back at our admin so basically I want our admin to be editable and I want our and let's go ahead and hide cheat for all right so let's go ahead and see how that works okay pull up the VBA and we'll go ahead and just click anywhere in here and click run the code okay and it's been run and now you see sheet 4 is gone okay and sheet 3 has been protected to see how it's unprotected and I cannot click anywhere in here so that works good and we have this is also protected with the password 1 2 3 that unprotected so that's working good well as well okay so our code is working good and sheet 4 is gone now let's go ahead and make sure for active and run our code again and let's go ahead and see if that works so ok ok we just ran it and there's sheet 4 again all right so that's working great and now what we want to do is we want to work this in with our login security so we'll do that now and what we'll do is we'll create a user form to handle that for us so back into our VBA and right click and we will insert a user form okay and we'll go ahead and give it a name into the properties and we'll call this login form and then we'll go ahead and give it a caption here login form okay and we'll change the background a little bit give it a little bit of a look a nicer look and we'll go ahead and add some text to that let me expand this there I bring that over so we'll go ahead and add labels so what we want is wanted username and we'll type that in here under user name that'll be the caption let's increase the font as well so that it'll make it easily readable today we go to 14 on this okay and then we will make it right justified double clicking here twice let's go ahead and copy that and we'll use a similar label label for the password ctrl-c ctrl-v okay and now let's go ahead and call this one password okay and next up what we want to do is we want to actually add in the label fields we can do that here it with a text box so these are the fields for the labels and let's give that a name also we'll call it user name field and let's also make that 14 so that it's similar as the others change the font okay and we'll go ahead and copy that and paste that and we're going to give that one as well a different name and we'll call that password field so let's go ahead and add that in and now what we want to do is we want to link these fields with the fields in our workbook so that we always know where they where they're set so let's go ahead and align these to the right and then we'll go ahead and align these two I'm using the control to select both of them and the left okay and what we want to do is we want to assign this field to this cell here b5 and we can do that under the control source here the control source so we'll go ahead and type the page name admin and then the exclamation mark and then b5 okay once we've done that James will appear so we know it's linked properly we'll do the same thing for here under for b6 admin b6 okay and then don't forget the exclamation mark there if for some reason your page name has spaces right you'll want to add these quotation marks these marks here to separate that so that's very important if you're Chi names have spaces all right so we've added that and we have them linked that's great let's go ahead and add a button so that we can log in and we'll go ahead and click on button and then make a nice okay button will have the caption saying okay we will also make this font size of 14 okay and now what do we want when we press okay what do we want to do well basically we want to run this macro here right we want to check the user so all we need to do is copy and paste this okay go back into the login form double click on the okay and paste it in so when they press ok we're going to basically check to see if that user okay and that's all we have to do since we've already created the macro we know that it's going to work right when when the user is checked all right great so that's it for the user form and what we want to do is we want to show and hide this user form basically on login right however when this workbook is closed we want to actually hide all the sheets because we don't know if the user if the new user is going to have access or not so it's important that we hide all the sheets and then based on the new users rights that we show the sheets or not and so what we want to do is we want to pick a default sheet you know just one empty sheet to start on and so I've created a star cheat there's nothing here right and what I want to happen is I want this sheet to appear when the workbook is opened and then on top of that I want the user form to pop up okay so to do that what we'll do is we'll write a macro that basically hides all the sheets and that except for the start okay and we can do that with with a very easy macro let's go ahead and write a macro for that and we'll just call this closed workbook because we want this to happen when the workbook is closed so let's call it closed workbook okay and the first thing we want to do before hiding any other sheets is we can't hide a sheet unless they worked for it before actually on it so we want to we want to go to the start we want to activate we want the user to go to the start so we can do that and the start as you see is sheet 6 I like to use the sheet numbers and not that she names cuz that she names tend to change so she six dot activate okay and all that's gonna do is send us to shoot six and make it display it's just going to display it so so that's fine and then basically I want to write some code that I'm gonna say I want to say for all the other sheets for all the other sheets in the workbook please hide them okay so we can do that first let's uh dimension a worksheet right worksheet as a worksheet cuz we're gonna go through every work sheet so we want to make sure we dimension and then we're gonna write some code basically that says for every worksheet in the workbook hide it unless of course it's the start we don't want to hide the start okay so for each work sheet in this workbook dot worksheets ok forever basically for all everyone right and then next worksheet so everything we're gonna write is in between there and we want to say if if the work sheet dot name does not equal start right we don't want to do this first start okay then work she dot visible equals Excel and then in this case we're gonna go very well hidden because we don't want them to easily unhide them so that's we're gonna run this code okay when we run this code it's gonna basically hide everything except the start so let's go ahead and save our work as I always like to do and then we're gonna run that macro let's go ahead and run it okay and you can see all the other sheets are not there except for the start so we want that before the close to do that because when the workbook is open we all we want them to see is this Start screen so that's gonna help us so now before the workbook closes and also there's one more thing I like to do there's often a problem when you close the workbook if changes are made you're gonna get a prompt saying do you want to save this workbook so what I always like to do is I was like to save the workbook after so we'll write one other code that says this workbook dot save okay and that's just going to save it so now we can copy and paste this code we could have written this under the close work pic as well but I wanted everything on the same module for you as well so we'll copy this close workbook and then under this workbook right we're going to put it in the workbook and then not when it's open but when it's closed right before clothes before closed that's what we're gonna paste it okay on open we do want some things to happen though what do we want to well it's pretty simple what we want to happen on open we want to show the login form right we want to show the login form so all we need to do is write in one line to say login login form dot show okay and that'll change it to capitals just like we did so basically when we open the workbook this is going to show okay great so we can run that and you see when we run that it shows the password and click OK perfect and you see how that worked the only thing we need to do is we need to close this form on successful on the successful registry we need to close this form so we can do that through the macro as well and all we need to do is go into the macro and this is the check user macro so remember after everything is done okay we can after they've passed the two tests in Craig username and encrypt password after they've passed those two tests we know they're good so we can do login form dot hide okay and that's gonna hide the form so perfect so that'll hide the form because they've already passed those two tests and one also very important code that you'll want to write is you want to clear out the username and password after it's been hidden so that they don't show up next time under the admin screen and we can go ahead and take a look and pull up our code and basically what we want to do is we want to clear out the cells b5 and six oh we can do that here dot range b5 and comma B six dot clear contents okay and that's going to clear up both the username and the password there may be times when you want to keep the last username if that's the case just clear out B six only and can not be five then it'll remember the username so that's also a very important thing that we want to add as well however for your purposes in the workbook that I'm gonna get you I don't want you to have to worry about the login screen when you first receive the workbook so what I'm going to do is I'm going to click on this workbook and I'm going to put a pasta feed before that and put a pasta before that that means those those macros are not going to run okay they're not going to run because I want you to see the workbook I want you to play with it I want you to learn it I don't want I don't want you to have to worry about the login if you're like me you will forget the password always so all you have to do let's say let's say you're on the Start screen and there's no sheets and you forget the password you're like oh boy okay so all you have to do is go into VBA go into the Visual Basic and then just click on any sheet like admin and click on the properties and then go ahead and make the sheet visible here okay however you there's a one other level of protection that's very important that's VBA protection and this password you do not want to forget okay there's a way to protect the PAL to protect VBA by right-clicking on the project going into VBA properties and going into protection and you can set a password here but please do not forget that this will lock VBA okay and nobody will be up it's an important level of security but but don't forget I I can't help you if you forget the password so make sure if you do set a password here you remember it and that's an important step because otherwise people can get into your workbook and get into your code so if you want a higher level protection set your workbook password here alright I hope that helps you I think we I've had it great you can also hide these columns here on your admin as we always do we'd put them in gray so you could have a great a workbook and I hope this helps please like please share and I thank you for all of your support you're really helping me out and give me a lot of motivation thank you so much please don't forget to subscribe to our YouTube channel and select these send me all notifications options so you can get our videos to you right away as soon as we create them

Channel: Excel For Freelancers

Views: 237,276

Rating: 4.938561 out of 5

Keywords: Excel User Login, Excel User Rights, Excel Workbook Protection, How To Add Excel Login, How to Add Excel Security, Excel User Protection, User Login for Excel, Excel Workbook Login, Login Pop-Up For Excel, User Sheet Security, Excel User Security, Excel Multiple User, Excel Multiple User Secirty, Excel Worksheet Security

Id: 0ahYoy5L3ec

Length: 52min 33sec (3153 seconds)

Published: Tue Sep 05 2017