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.