Streamline Your Payments with a Proforma Invoice in Excel for Security

Proforma invoice in excel for Security

hello this is Randy with Excel for Freelancers and welcome to the Excel application protection training and this week I'm gonna go over 18 different methods on how you can protect your hard work and your code from others or from being messed up I'm going to show you every step on the best methods on making protections of your applications it's gonna be an incredible trendy you won't want to miss it I cannot wait so let's get started thank you all right thanks so much for joining me today we've put a lot of work over the years into building applications but today I'm going to put the focus on protecting all that work protecting your intellectual property protecting end users from themselves making sure that the application stays stable and strong so we're going to go over 18 different methods on exactly how you can protect your applications and we're going to do it all on this invoice application I created this a few weeks ago so if you want to pick this up of course I'll make sure this entire application along with all the protection methods and code will be available for you with the links in the description where you can use your email and name and go ahead and get that sent over to you absolutely free okay great way to support this channel is through our patreon each week I take these applications and I add additional features or I focus on an area or I fix something based on your suggestions so if you have an idea you want to feature added patreon is the platform to do it in fact last week we did this restaurant Cafe manager it was suggested okay maybe we want to add an additional receipt just for the bar and only to send those drinks so I did just that I added this feature we can now send just the drink receipts to any specific printer so we were able to do that and as you can see here we've got the drinks already on that also another suggestion was to add a kitchen view so that the kitchen can get the order so now we have horizontaling all the orders that go to the kitchen as they get made and sent out they're all this list is automatically updated and I added a dashboard in here so that's just for our patreon members so go ahead and jump on that it's just a few dollars a month and it's a great way to support this channel other ways to support absolutely free all I ask you to do is go ahead and click on that subscription and the notification icon Bell that's gonna help us out don't forget to comment below I'd love to hear your thoughts ideas and feedback I respond to each and every one every every single week so that's going to help us out all right great so let's get to the training how do we protect our hard work how do we make sure you've got over so much lately what I want to do is I want to focus just on protection this week because I haven't gone over that in much detail lately in these tradings so this week we're going to dedicate all to that now I have this little invoice application here this is what we're going to be building it up on it is not protected users can navigate previous to next invoices they can navigate through pages so this page has two pages on this so what we want to do is we want to add protection we don't want users to be able to select buttons and move them around we don't want users to be able to drag and drop or change things that can hurt the software we may also want to add additional administrative so that only admins have the right maybe we only want admins to be able to adjust these customize this invoices so we're going to go over all that today all right so the very basic first one that we're going to focus on we've got a list of 18 we're going to go going already is this cell protection cell protection how do we protect individual cells now if we let's say we want to protect certain cells and we want the user to enter into other cells what we want to do is protect the entire sheet so we go into review and we go into protect sheet that's going to protect these and we're going to go over in detail on that protection but what we want to do is we want to separate we want to the fields and cells that we want the user to make changes to we don't want to protect so what we're going to do is I'd hold down the control and I'd highlight these and all the fields that I want the user to edit I am going to keep those unprotected now that total column is of course a formula so I'm not going to click that and then I also want to make sure that we do the tax we do want the users to be able to so I'm going to hold down the control and highlight this and what I want to do is I want to make sure that these cells are not protected so I'm going to right click that and I'm going to go in to anywhere the format cells you can see the protected cells here so we're going to go in to the format cells here and we'll look up under the protection it's the last tab here and I'm going to make sure that those are not locked they're unlocked now generally in every sheet when you protect the sheet generally every sheet by default is already locked so what I mean by that is if I add a new sheet here's a brand new sheet if I right click that and go to the format cells we can see that inherently this cell is locked so generally your cells are going to be locked even on new sheets so if we want the user to make change to that then we'll unselect that so that's it for new sheets we want to make sure that we also undo now let's go ahead and delete that sheet I'll delete that don't need that sheet back into the invoice what I want to do is so that's going to have no effect once we unprotect it users can still make changes to individual cells in the sheet so how do we do that well what we want to do is we want to go into review and then we're going to protect the sheet we don't need to add a password to this so we can protect the sheet we can allow users to select lock cells or we can unclick that we can allow them to select unlock cells only we can allow them to format cells format columns format rows so we're not going to let them do that insert rows certainly we wouldn't want to do that insert hyperlinks perhaps delete columns certainly not you would probably almost never want you to do that delete rows sorting certainly we could using Auto filter perhaps using pivot tables yes editing objects now and this is selected allow users to of this worksheet to edit objects now this means for the shapes so when I unselect that users will not be able to change the shapes and edit scenarios and I'm going to click ok ok and now you see the pop calendar is not exist on the worksheet okay so that's fine so that means that that pop-up calendar obviously is not available so we want to make sure that that becomes available when we select the object so what we're going to do is go back into review and do unprotected sheet and now what we can do is when we protect it so when we select on edit objects now the pop-up count founder can be available and I'll show you a great trick on how to do that but also at the same rate we don't want them to have that ability so how do we do that we're going to do that through a macro and I'm going to show you so basically what we want to do is only when we user select on here only then should I allow the users to display that calendar by editing objects but not during that so we're going to go over that that's it so now you see now that's protected I cannot select on any cell I can only select on the cells that have this ability so that's basically how we do General protection of a cell simply by doing that so the users can now and notice the total I cannot click on the total if I tab over it it's going to tap just to the opposite cell and that's because our total is protected I can't select it I can't do anything I can only select before and after so that's General protection and we want to do that okay and of course I'm going to show you how to do that within VBA as well so basically but however there's comes with a few issues here's what happened so when we make the protection there are also cells like these here that I don't I'm going to hide these columns obviously and I want VBA to create to be able to change those so if I do previous text it's going to create a bug so if I do previous you're going to see something like this application defined or object defined error when I debug that I'm going to take a look at the line code here and it just says all we're saying is B4 equals invoice so there's nothing wrong with this code here however before is a protect itself and so now we can unprotect B4 if I reset that and I decide to unprotect B4 so what I'm going to do is going to go back into review I'm going to drop this down here and show the tabs if I unprotect this sheet and I then go ahead and take B4 which is the invoice number and I unprotect this sheet so I'm just going to go to the format cells here and I'm going to unselect that now there are other errors that'll probably be created but not that particular one now if I go back and protect that that sheet again clicking OK with all the same variables and I do next right now we see we debug now it's different now it's B1 right because we didn't unprotect viewing so here's what I really want to do what I really want to do is I don't want necessarily to unprotect every single cell that I want db8 to change what I want to do is I want to unprotect cells that only the user can make changes to so only those now the ones that VBA I still want protected so how are we going to do that well what we can do is we can do that inside the code I wish there was a feature here where we could select that but that's actually a VBA so basically inside the protection what we want to do is called user interface only meaning that only users can change that VBA is allowed to make changes to cells that are protected once again VBA is allowed to make changes to cells that are protected but the user is not only the user your interface okay so that's what we want so what we're going to do is we're going to go into VBA and write code that's part of the protection so basically when we click here and do some protection we add a password we can do that same process in VBA so let me show you how to do the exactly that same thing but inside VBA so what we're going to do is we're going to go into the developers we'll go into Visual Basic alt f11 it's also the shortcut there so you can get to that and I've got a module here called protection now it's called protect worksheet that's the one we're going to go over now there's all these commands now if you want to know what each one is I've put them right here inside protection so this is what we're going to focus on password is the first one it's optional so it's got all these details a string that specifies its case sensitive password for the worksheet or workbook that we have drawing objects contents scenarios so we're going to go into the one that I'm really going to focus on so these are the same options almost the same except for user interface only you see that right here user and interface only this is the one I really want to put focus on so it's optional it's a variant it's true to protecting user interface but not macros if this argument is emitted protection applies to both macros and user interface that's why we got that bug we got that bug because VBA are not allowed to make changes to protected cells however I want to tell Excel yes please allow VBA to make changes to protected cells but not the user so what we're going to do is user interface equals true true to protect the user interface but not the macro so that's what I want to set that to true so inside the code that's exactly what we're going to do first of all we're going to specify the sheet we want to protect it we're going to put a password in I'm just leaving that password blank for ease this is where you would put a password in drawing objects equals true right if you want to allow them to edit things if we change this to false our calendar won't show up but what we can do is inside our calendar we can make that appear we can run a macro to unprotect the worksheet and then re-protect it again so if there are shapes that you want to show up you un during this during that macro you unprotect it just very quickly then you re-protect it so that's a great way to do that contents right we want to protect the contents scenario so each one of these is defined here scenarios protect scenarios contents to protect for a chart this would protect the entire chart I don't need to read each one of these you can download this and go over to it but basically they're exactly the same one this is the one that's the most important user interface only true so that means when I run this macro VBA will be allowed to make changes to protected cells that's the most important thing so allow formatting false and the rest is kind of exactly the same allow false allow inserting rows false allow inserting hyperlinks I add that true a lot of deleting columns false deleting rows false sorting true filtering true allow pivot tables uh true and so that's it so everything here so the idea is that as developers we always want to make sure that this code runs now here's the thing that you also want to remember this is very very important it's a lesson I had to learn this protect the user interface only once I save the work rate let me show you this is very important so I'm going to run this okay so I just ran this macro okay so now it's protected and now we're going to go into our invoice screen and we're going to see that it's particularly I can't click on anything here other than those unprotected cells if I do next and previous notice there's no bug let's take a look inside here let's take a look at uh the invoice number B4 and invoice load B let me remember we had an issue now B1 goes to and I go to let's say search Row one okay so we're allowed to basically do whatever we want however this is all protected so B4 let me protect this I want to protect B4 because it changes so I'm going to go into review and then I'm going to unprotect it temporarily and I'm going to then protect this cell out so what we're going to do is we're going to go into the format and I want to re-protect the cell now we know that VBA is making changes to this clicking ok now what I'm going to do is I'm going to run that code one more time here running that code we see that it is not protected right I cannot select B4 however let's take a look at B4 notice oh we're on the first invoice let's go to the next one notice that B4 changes to 2 B4 changes to three so it's changing VB is making those changes but it's locked so that's very important now what I'm going to do is I'm going to save this workbook I'm going to close it out and I want it and do you want to save it just put yes so we're going to save it we're going to close it out and then here's all the details also on the the protection here that's the Microsoft website I'm going to open it up again and I want to show you what this issue is and then how to fix it of course so what we're going to do is we're going to go back in here and I didn't change anything right so let's go previous look we have that bug again why did that come up why did it happen now we protected it it's protected but why did we get this bug what is the bug let's look at let's the bug it's gonna be the same bug that we rented you B4 right we're trying to make changes to B4 here's kind of the unfortunate but it's super easy to fix although we did protect it properly and we ran the user interface we need to run this macro every single time we open it remember so that so this protection here this protect worksheet we will need to run this when we open up the workbook every single time so if I go into the workbook so that means this one here is not remembered it's not remembered I'm not there must be a reason why I should ask Microsoft this is not so it changes to false even though the worksheet is still protected but for some reason this goes back to false I don't know why but it's easy to fix all we need to do is take this macro go into this workbook go in the workbook open Where's the workbook open you can also find it here workbook and we've already created a macro for it and then just click open so here's a workbook open Event what I'm going to do is I'm just going to uncomment that out okay I'm going to save it so let's go ahead and save our all we did Saving I'm not making any changes I'm going to close this workbook out once more saving it again there's an action on that okay now we're going to go back in I'm going to open it up the same thing again here and we're going to see now it's running on that open workbook open Event so we'll open up Excel open up the same application and we just know that that macro is going to run on open okay so we didn't do anything else now if I do previous look it's working just fine there are no bugs because that macro ran on open so it's very very important anytime we're using user interface only as we did we have to make sure that the macro runs on workbook open again it's this workbook and this is where we're going to I'll go over these macros in just a bit that's kind of important but I want to make sure that so protect works you run that macro on opening that will ensure that your workbooks are protected your worksheets your individual worksheets are protected and we also want to make sure that this thing runs every single time because we know that this part is not remembered when the workbook is closed okay great so we've gone over that that's a really important part of it that's really important because that's going to allow VBA a lot more power than the users and that's what we want we don't want to give them the ability to to mess things up with formulas so VBA has that ability so I like that so that covers number one cell protection protected cells and worship also we can do a range protection if we want to set a specific range we can do that so how are we going to do that there are times we may want to protect individual lists or of course ranges and we can do that with the feature so let's take a look at the invoice list and now if we go over inside the review tab we see that we have allow edit ranges and so we can protect that with a password so how are we going to do that we'll just click on new here and then we just give it a name so we could just say invoice list we prefer and we give it a specific cells if we want to refer to and we give it a password so we can do one two three four five and then setting the permissions we can see that we if we want to give specific groups permissions we can do that we can add an edit group so it's kind of helpful although I haven't personally used that too much and then click OK and now also we enter re-enter the password and this is going to make sure only when the sheet is protected that range is now protected so we have that there and it's going to say range is unlocked by a password when the sheet is protected so even though this sheet is protected this range can be unlocked when the user has that password and we can just delete that and that's going to allow us so that's a great way to do that so we can protect individual ranges or lists if we want to based on the user password this is nice if you have admin rights you don't want other users to be able to edit ranges but you want to be able to do that you want to unprotect the sheet you can do that with that little feature here allow edit ranges okay very good continuing on our list here worksheet protection of course we have the workspace section we went over that we went over that with VBA right we understand that inside the review when we protect sheet we can go over this list we also know how to do a VBA you to sign a password also you can say Protect worksheet and contents of lock cell so you can do that as well adding that in and there's all those kinds of objects there also the workbook structure that's very very important most likely you do not want to unless it's very specific inside your type of workbook you do not want users to be able to add delete or edit existing worksheet names so we have the workbook protection so we can protect the Integrity of the workbook and the structure using this so once we assign a password here it's going to say the structure of a window so one two three four five we can do that and then what that's going to do is going to make sure that users can no longer add it so if we reset that password there enter it again now we can no longer add a new workgrade if I tried notice there's nothing going to happen when I try to add a sheet if I try to change a sheet name rename it I'm not going to be able to let's move that back up there I want you to be able to see this so if I right click I cannot rename it notice there's nothing I can't rename I can't insert and I can't delete that is protected so now the Integrity of your workbook is protected and we can just unprotect it with the password here okay very good so we understand that also protecting that workbook very important so you want to make sure that the workbooks you release is most likely you would not want them to be able to change that when using word especially worksheet names sometimes the code that you write uses these worksheet names so if you users are allowed to change those names it could create issues for your workbook although I usually try to use the code names when I'm working notice that invoice this is a code name so this particular there's two names so if we take a look inside this invoice sheet right here and we click on the properties we can change the code name this is the code name now if you use this name inside your VBA users can't change that unless they have access to the VBA model so we try to work with these code names and not the sheet name so code names are better the sheet game of course can be changed through this one right here inside the tabs unless you've protected the structure of the workbook so also very important there okay this is the sheet name right here this is where users could change that inside the sheet so if I change it on the worksheet here to say invoices here you'll you're going to see that that change is then reflected in the VBA model right here so notice the change here changing it back will also change it on the individual invoice so that's another way to do it so what I tried to do inside the code is use the code name only within the code and not necessarily the name but however using the workbook protection that we just went over will allow users to we'll make sure the users cannot change the name of the work as well as adding or deleting existing ones okay so that's going to cover our protecting the workbook the workbook protection and also what I want to do is we can also set the workbook open we can encrypt the workbook open with a password and that means they need to actually use a password to open the workbook so how would we do that well that's going to be under file and we take a look at down here info we can see that we can protect the workbook and we have the encrypt with password open we can make it read only so that means users can make any changes that's usually rare but if you're giving somebody a report and you want them only to look at it and not make any changes that might be helpful so that prevents accidental changes by asking readers to opt into editing so that might be helpful so you can secure that encrypt with password this requires a password to open the workbook so if I set a password one two three four five make sure I haven't used this too much I've used other security if you lose or forget your password it cannot be recovered and it's advisable to keep lists of passwords okay so keep in mind that can be kind of scary one two three four five clicking okay what that's going to do we'll enter that one two three four five one more more time clicking OK and now what we're going to do is going to go back and we're going to save that workbook I'm going to close it out and we're going to open up and now you're going to see that it's going to require a password to open it so that's another way that you can protect it so if I were to open this again here and we see that it's requiring that password to open it so we're clicking OK and putting in our password so that could be another helpful type if you have a really secure you want to make sure that only people and then what we'll do is we can encrypt with passwords so we see we can encrypt so we can clear that out now clicking ok so we do not want a password so now there's no password that's been attached to that we just cleared that out okay very good so that's how we protect the workbook open and we can encrypt it with a potassium we can also protect it through VBA now we went over that work protection through VBA that and that's the one that you usually want to use because that's going to allow us to use so generally when you protect the workbook because it's going to allow us to do that user interface only and that's so important so that that's how we protect workers through VBA we did go over that method so there are two methods one via the worksheet here and then one via the workbook code so this is the one via the worksheet and we saw the differences that we can do and the more power here because we can use interface interface user interface only when we'd use it through the code okay very good so next up what we want to do is I'll show you already exactly how to allow VBA to make changes of the code you'll also want to hide sheets there may be certain sheets that we don't want the users to see so if we have let's say an invoice list this is not really going to be helpful for an end user we may want to hide it now we can easily hide a sheet simply by clicking here and then just hiding it right here and what that's going to do is hide it however that's limited protection because the users depending upon the type of security can also unhide it so there's another option Friday so notice invoice list if I click on here it's going to unhide it so now we can see it again so we can hide and unhide that's kind of basic how hiding but what if we want to hide it so that end users cannot unhide it and that's going to be called very hidden very hidden we can do that through VBA okay so back in VBA we're going to go I want to show you a few things here we can do that either with VBA there's a few ways let's take a look on this invoice list here that's the sheet that we were just on if I go into the properties here we can see that here under the visible it's visible so one way to change it is through here we can make it hidden now that's the same way that we just did there so changing it there is also going to change it here so if I right click on any sheet go to unhide sorry about that we can see that I've just hidden unhidden two sheets invoice items and invoice list so that's the same way that we can hide it so it's the same value hidden so there's two ways to hide it hiding it through right click as we had or unhiding it just so we have so we can click on here holding Knight control if I want to unhide both clicking OK is going to unhide them it's the same exact as we did here however there's another one that's called very hidden if I change this invoice list to very hidden we see that it is no longer available so now it's no longer available if I right click here notice there's nothing unhidden I can't unhide it because that's not available so even if I were to hide this one let's take a look at hide I'm going to hide invoice items and now I unhide it if we take a look there's only one sheet that's available invoice list is not available because that's very well hidden it can only be unhidden through the VBA model so only can we change it to here so when I change it to again back to Hidden now we will be able to unhide it from here now we will so if we click on unhide we can now see that it's available so again that's a really nice important thing when you never want end users to see a sheet you will want to use very hidden we can also do that through VBA through here so for example we can hide a sheet called sheet hidden it's same thing like this cow pop-up so we have a sheet right here let's take a look at this cow pop-up through VBA and I'll pull that up here so this Cal pop-up is just used for the calendar so we certainly don't want to see that now we can make it hidden by running this macro that's going to hide it notice it's not there anymore but again like I said that's just the basic security but if we want to make it really well hidden we can see now inside VBA this particular sheet here clicking on the properties we see it's hidden but through VBA we can also make it very well hidden if I click on there and run this macro it is going to be very hidden so now again we cannot unhide it from here if I try to scroll up here and again unhide it I cannot unhide it and that's exactly what you want you want for sheets that the users will not have access to or never have access to and you don't want them to see you want to use this property very hidden so I've shown you two ways to do that through VBA here or again through here inside the properties here selecting on the sheet and then changing this property here so there's two ways to do that okay let's go ahead and make it visible now I've shown you both ways inside the macro so hiding sheets a very important part of the protection so we've covered that all right so we're halfway through our list now disabling drag and drop now this is really really important because let's say I've got this sheet here and we've got a drop down if I allow users to drag and drop this it could be very helpful what do I mean by that well it's helpful if I allow users to drag this over it's not going to be good even unprotected sheets I'm going to protect this again through the code even if it's protected let's go I'm going to run this macro that's the protection that we want if I take a look here I've got a drop down list I don't want users to be able to drag and drop these cells anywhere along notice that that square is available there it's still available to be drag and drop and I really don't want that okay so how do we write we don't want them to be able to drag and drop these features that's going to be an issue so how do we disable the drag and drop well again we can do that through a macro very very easily called disable drag and drop and application cell drag and drop false if I run this macro right now and we take a look on here we see that that we can no longer drag and drop anything let's go ahead and try that if I try that you see we can no longer that plus notice if I hover over that nothing's happening I want to show that to you one more time again so you'll see that that little plus is not here however if I change this back to true we're going to see the different icon up here so I'm going to run this macro here and now when I select on something you see that that little plus those little four arrows there when I click on those flares allows me to drag and drop those four arrows do not appear when I hover over line let's zoom in I want to show you that one these four arrows do not appear when I hover over select something else okay so taking a look at this now you see that we are unable to do that there we go this is the right zoom level I want okay so you see that right we now we can running this macro changing it back to false and running it again is going to disallow us from doing that and that's exactly what you want you almost never want end users to build drug so notice those when I hover over that those four hours don't appear just the plus line okay so sell drag and drop very very important let's continue on on our protection so we covered cell drag and drop we've covered all of this let's italicize that so we know we've been over it and now what I want to do is I want to disable copy and paste this also can create issues for end users if we allow them to copy and paste if I were to allow them even though you know to right click copy this if I they paste it somewhere else it's going to create major issues right we really don't want that what we want to do is we want to disable it but we have to be careful because one they can do it through right click copy what they can do it through here inside the home they can copy it here and they can also use shortcut keys Ctrl C and then paste it right I want to be able to disable all of that and all three of those instances so how are we going to do that well first up we can use obviously with a macro so that's what I'm going to go in a different module on here called cut copy and paste macros this is a little bit more involved and so the first part of it is basically disabling that toggle cut copy and paste allow so it's a toggle that means if this is true it's going to allow it if it's false it won't allow it so how do we sit toggle so if this true it means to allow it it's just a little test I run if it's false it won't allow it so let's take a quick look inside here I'm going to run a little sub and then we'll do is toggle cut copy and paste and then let's just do toggle CC okay so now what we'll do is I'm just going to basically take this copy it in here paste it in here and I'm going to change it to false meaning they're not allowed to I'm going to run that macro here then we're going to see how it differentiates so I'm going to run it okay if I right click notice is how Cut Copy are disabled here and if I do control C it's going to say sorry copying pasting or not if I do control V also again not allowed so how does that macro work so let's take a look inside this toggle right here okay so let's take this macro apart toggle cut copy and paste allow as Boolean so allow us the variable and it's Boolean so that means it's going to be true or false if it is true we will allow them to copy and paste if it is false we will not allow them so the first thing we want to do is we want to enable these menu buttons now 21 19 22 and 755 are the numbers that are associated with these menus so every single menu has associated with you so we want to make sure to disable that and we can do that through this macro right here allow allow allow so this is either going to be true or false we're going to either allow so we can do this so we're calling enable menu so this is a Mac macro so if we take a look inside this macro call enable menu item this is the integer this is the number of the ID of the menu so every single menu item has an internal ID so we're calling those numbers up and then what we're going to do is we're going to enable so again this is going to be true or false so that's what we're going to be doing so we're going to in this particular macro we're going to Dimension the c bar as a command bar we're focused on those menu bars and also it Dimension the c bar control it's a command bar control so we want either both the command bars and the controls so this is a command bar this is the control so that's exactly what we're after and basically what we want to do is we want to disable them so we're going to go through all the command bars in the application command bars if the name is clipboard right it says as long as it does not equal clipboard then what we're going to be doing is we're going to reset the command Mark control equal to find we're looking for that particular control ID one of those four num numbers here 21 19 22 or 755 and we're going to make it recursive equals true so we want to go back and make sure it's true if not is nothing then we just want to make sure it's enabled so we're checking to make sure that it is if it's there right not and nothing cancel each other out so it means if it's found then we're going to make sure it's enabled now we can make that disabled if we want to so that's very important if we make that disabled it's going to then be disabled great so now we understand this portion of the macro so that's how we go through we're either allowing or disabling it we can also activate or deactivate drag and drop through here I've already showed you that here so we can also make this Dynamic here so it's the same thing that we just showed you on the other module okay also what we want to do is we want to activate or deactivate the cut copy the shortcut keys remember the shortcut keys also do it if I do control C we want to make sure to let them know that it's not allowed so how are we going to do them well we can do that here so again we're going to use select case and it's going to be based on whether the allow is true or false so this is our variable here this is going to be either true or false so if it is false we are not going to allow them the application we're going to clear the clipboard if there's anything in the clipboard I want to clear that out and if the user presses Ctrl C this the upper arrow is control this is C so that means on key when they press Ctrl C what do we want to happen we want to run this macro called Cut Copy disabled and all that macro is is just a simple message box Cut Copy disabled the message box sorry cutting copying pasting has been disabled in this workbook so that's all we're going to do is just run this macro to let them know so that means if they click Ctrl C Ctrl V Ctrl X or again shift delete also we are going to make sure cut cop faces disabled also trying to insert anything using the insert again this is so all those functions are going to be disabled however if it is true all we are going to do is return it back to this original location again nothing's going to happen that means we're not running any macro that runs on this and that's going to basically reset it so this is all we need to do is to reset those shortcut keys back to their original by not putting any macro that's associated if we wanted to put a macro we would put the macro procedure here but we're not that's going to return it back to its default so that is how we take care of our Cut Copy at least for the right click portion however if we notice we still have the ability here here and here so we also need to disable that our toolbar here up the top allows us to cut copy and we don't want that to happen so how are we going to do that well our toolbar is controlled by a little bit something different called a UI editor so to do that what we need to do is download a free software called UI editor because we want this portion of the toolbar disable so how do we make that happen and well again first thing what we need is a separate software called the UI editor and we can get that directly in here so we can go to this website here let's take a quick look at this it's called better Solutions VBA ribbon custom there's a few different places that have it but this one has it and it's just basically called office custom UI editor here I'll include this link in the description as long as I remember if not please remind me I'll make sure you get it I'm going to put it right here too I also put it inside the workbook right here called customers so the link is right here inside the workbook okay you can get that from here okay so once you have that what we want to do is we want to go ahead and download it so we'll just click download and what that's going to do is going to download for us and create a zip file what you want to do then is you want to run it and it's going to create this application here called custom UI editors look looks like this when it's running I'm going to run it right now and you'll see it's running it's a very basic called custom UI editor from Microsoft Office okay so what we want to do is we want to open up our existing application open up so what am I on I'm on this workbook here this is the one I'm using Excel application protection so this is the one we're going to be focusing on this one right here so I'm going to click open here and I'm going to open it up inside here now to do that I want to make sure that also if I'm going to save this notice there's nothing in here there's nothing in here what I want to do is I want to add some code to this now I've put this code inside the workbook it's not a macro but I put it here for convenience only right in here if we I think I put it under the protection macros here all the way down here okay scrolling down here I've put it here I'm going to comment this out I'm going to copy this and I'm going to comment this out I want to make sure that it doesn't run so use this code in the custom XML editor okay so that's what we want this is the XML code so all we need to do is just copy this make sure you don't include the comments so what you want to do is just uncomment that out copy it and then recomb it out we don't want this to you know create any issues for Excel because this is not VBA code so we have the code there then what I'm going to do is I'm going to go back in our UI edit I'm going to paste this in and basically what it's going to do is going to run this now we also want to make sure when you try to run it what we're going to do is just going to check validate and you're going to see something probably it says unknown namespace 20061 must be 20907 so what we want to do is just make a little bit of a change to this changing this to nine and seven of course the one that's the one I'm going to give you 97. so if you see that because when I downloaded the first I'm going to make that change to right here because it depends on your version so I'm going to change that to seven but if it's anything else just follow what it says and it'll make that change I'll make sure to save that okay so that's gonna now if I try to validate it again this is called validation it'll just be fine okay it's well formed now what I'm not going to do is I'm not going to save this right now what I want to do is I want to just save this workbook and I want to close it out before I save any changes so it's going to save that and now what I want to do is save it here and so this is what it's going to do it's going to look for the this particular macro RX shared so basically what we're doing inside this unload of the ribbon what we want to do is when the user selects cut we're going to run this macro when they select copy we're going to run this macro Pace it is the same macro so this is the macro RX shared get enabled so that's what we're going to put a focus on inside the VBA code so that means when the user clicks this something's going to happen so what I'm going to do is now I'm going to save it now that the workbook's closed okay I'm going to close it out now just to make sure that we have it open I'm going to open that UL editor again just to make sure it's saved so we'll open up the program here it's going to launch again I'm going to open up the workbook here we're looking for the workbook here this is it clicking open and we want to make sure that the code is there and that's exactly what I want okay so we've copied and pasted our coast and then we can close that out we can reopen the workbook up and now we can take a look inside the code and we'll make some adjustments to the code because I've disabled the code temporarily just so you can see it so Excel application protection okay very good so you see nothing's happened with the but it's still there so we're going to go inside the developers inside Visual Basic here we're going to take a look inside the modules here I'm going to focus on this cut copy now this is the one that I want to focus on remember we covered this already but this is the one I wanted to focus on public grx UI ribbon cut copy paste as ribbon UI so this is the one we're going to focus on this is the macro that's going to be running set grx I ribbon UI company as of equal to ribbon okay so now what I'm going to do is this is the macro Public Sub RX shared get enabled by ref as I ribbon so this is the control and this is the return value so I'm going to change each one of these two false false right because I just had a truce I wanted to show you the difference false and false okay now when is this going to run if we take a look inside our this workbook I want to run this on workbook open so workbook opens where I want so you see workbook toggle Cut Copy post toggle the worksheet jobs and we have that but also if we take a look inside and now what that's going to do is going to run automatically when we open it because it's running that toolbar however what happens when I don't when I switch to another worksheet I need to make sure that it doesn't work and that means that when I change to a brand new worksheet so let's let's go ahead and save what we have now I'm going to close it and reopen it we're going to take a look at some of the changes that were made okay so we'll open up the application again and we'll take a look inside here and we see that we now have Cut Copy post everything has been disabled see I'm not allowed to do that and that's exactly what I want so everything's now been disabled just the way I want it however we want to make sure that it does get enabled when I select another workbook notice that we still have the availability here cut notice copy and paste so if I do tests right in here and I want to copy that here and paste it here it works just fine when I go back to the existing workbook nothing is working that's exactly what I want so that we have the ability in that so how does that happen well that happens inside the developers right on that worksheet deactivate so if we go back in here and go let's go not this one Visual Basic here going back in to this workbook is what I want to focus on here the workbook here and you see sheet deactivate that means when we when the sheet is deactivate what we want to do is we want to make sure we invalidate that so that users are able to copy and paste inside other workbook but not the current one great so that's I'm glad we covered that so now we see that we can disable Cut Copy in the better we can actually disable it here inside and we can disable it within the shortcut okay that was a long one so stabling copying paste very very important that we've been through that okay what about if you don't want to let the user to save it as maybe you only want them to save save the work but but not save it as another workbook so that's another feature that we can do disabling the save as feature another way to protect it we can do that relatively easy so let's go back inside our protection here and we can say inside actually let's go to the workbook and I'm going to uncomment this out so here we've got our code right here I'm coming up and it's going to be on the before workbook save we can find that under before save we want to run some code if the save UI then right if they're saving ads if that's the one they've collected let these you know save as function has been disabled information save as disabled cancel equals true means we're canceling not allowing them soon so I can save the workbook no problem if I try to go into file and click save as right next try to change the name just any other name and I click save you're going to see that the save as function has been disabled we cannot now save this as another name we can only save it as a current name and this might be helpful if you only want the users to save it as a specific name in a specific location it gives them a little bit more security so you can do that so again saving as is a very very helpful feature if you want that okay so we covered also save ads now what if you have users right multiple users and you want to hide their passwords also be able to give certain users certain features notice when we created this this particular edit mode this allows users to customize this but what if I only want certain users to customize it and not others then what we do is pre-create some additional users so let's say I can turn this off or on but I only want users with certain administrative passwords to be able to edit this I can do that through multiple users so how are we going to do that well we can create a form when they log in that will allow them to put in their username and password and I've done just that here inside our forms here we see we've got a login form I've also created a screen called start screen now this start screen is basically the type of screen that we want to see right when the user logs in so when they open the workbook I want this screen to show up so how are we going to do that that's going to give us the ability to log in now if I log in Fred frederers and I'll show you what and then I believe one two three four five clicking OK that is an admin password it's going to log all the sheets and we can have that so how do we do that so how do we give certain users ability and certain use not well of course we need some kind of list of user names so I've got that right here I've got a list of application users so notice David Davis is a user not an admin so when I log in how would that help how does that change so let's take a look inside going back into the start screen and I'm going to go into user login of course I'll show you that I'm to log in under David Davis this time David Davis and the same power so run two three four five clicking ok ok but now notice the edit Mode's off if I try to turn it on it says you do not have admin rights to make changes to this invoice please check with your administrator so that means that we can assign different rights to different users based on the user type so here inside we have user ID we have username user type and user password now we can also hide these passwords if we go into back into the protection then next up on our list is called hiding user passwords your mask and now there's several ways to hide them so let's go into a few different ways first of all you already know how we can hide this sheet we know we probably want to this particular sheet which contains all the username and password information which we'll be getting into we want to certainly make sure that we hide the sheet however another way to do that is merge the center and we can just basically hide this entire column so there's a a few ways to do that obviously there's multiple blades you'd hide the sheet you could hide the column just by hiding the column and then there's another we could mask it so we could obviously hide the column and then protect the sheet we could hide the sheet very well hidden we can also maybe we don't want to have any background like we could do this no fill and then we could do no no borders so we can do format the cell here and then I'll do no board it's just to kind of make it look another way to hide this is basically like an additional way to hide it I'm going to take out the borders here and then what I'm going to do is I'm going to give them a specific format then what I'm going to do is I'm going to go into the custom format more number formats here go to the custom and I'm going to give it three semicolons one two three clicking okay okay so now we click now it's see you can't even see the password you don't even know that there's a password here so it's an extra layer of security so very very good so we see that we can see how we hide passwords now we can also hide passwords inside our user form so fireworks go back into start and click on login and we see Fred freder's we can also do it here notice the password is not hidden right we can see it but what if we want to hide it or mask that password we can do that as well inside the form details so we're going to go inside this login form here I'm going to select on the password and we can give that a very very specific password character here in the properties I can give it any character I want so let's say I use star let's say star here okay so now that I've given that asterisk or Star when I go to login again here we put in the name and then the passwords you're going to see are automatically masked one two three four five you cannot see it so that's another way great way to hide the password using masking to do that okay very good so we've gone over that now let's take a look inside let's take a covered hiding user passwords that's good coverage save disable save as feature we got that covered user rights and protections selective adminator right right when I remember when I said when I when freder's logged and he was able to edit the invoice when Davis logged in he was not able to so how do we do that well that's through the macro so let's take a look inside our protection here and we also have the login form here so I'm going to actually let's go ahead go to the view code when the user presses that okay button this button right here okay I want something to happen if I double click that here let's click let's go out of it and double click into it again we'll go directly to the macro that is associated with this so basically what I want to do with this is I want to validate their password to make sure it is the correct password and the correct user and how do we do that well we can do that with some named ranges and some formulas what I want to do is through VBA I want to put whatever the enter is the username I want to put it right here whatever the password they enter I want to put it right here inside B2 one two three four five then I want this formula to let me know if it is correct or not so we can do that with some named ranges and so I've created some named ranges right here so let's take a look inside the formulas name manager and we go all the way down here I've got three different name grains one for the username it's using the offset formula so it's going to Encompass all the usernames I've got another one for the user passwords it's there right remember we've hidden the user password so that's correct I'll probably change that because that will confuse people who don't know they'll be looking for the password so I better reverse that end user type so we have all those into the name ranges again so listen all we have to do is go into the home and just change that back to General just so people don't get confused okay because otherwise that could be a real problem for people that some people don't watch the training videos and they get there it's like where's the password I can't I can't copy and paste so we'll paste the format there so here's a password here now I've got three named ranges one for the username one for the user type and one for the password so we can use a formula to determine if it is correct so we're going to wrap it in if error we're going to do if index we're going to be indexing the user password that's what I'm looking for I'm going to match that I want to find the row that's associated so I'm going to find the row based on B1 now B1 is our username and so we're going to look for it within the usernames and we want an exact match so this is our math this will get us the row this is the column that we're looking for so what we're going to do is this will get us the password now I'm going to check if the looked up password is equal to whatever's in B2 if those are equal then it's true we know if it's a wrong username we won't get a match here so we know that either the password or the username is incorrect it's going to get us false if they're both correct it will get us true if I make one change here it's going to go to false if I make one change on the password field again it's also going to go to false so that means either one either the username or the password is wrong this will go to false So within VBA all I need to do is check if B3 equals true if it is they have internet correct username and password so let's take a look inside this okay we're going to Dimension the worksheet as a worksheet if the users I think we don't need this but we do need it if we're going to Loop through the worksheets as a way so if the user's B3 equals true we know it's a correct password let's do correct password okay very good so how do we get that so we're going to make sure that it's correct but what I haven't explained is how do we get B1 and B2 populated with the user forms and that's what I want to go into just a bit inside our code so how are we going to get that so let's take a look right down here if the user made we've got some names for that let's take a look inside and I'm going to view the app just so I can you can familiarize yourself with the name of that so we're going to go into the view the object that so this field is called user name let's shrink this a little bit so we can see it username and bring them a little bit closer together so this Field's called username this field is called password so basically what happens is anytime a user makes a change to this field I want to place whatever they have put I want to put that directly inside B1 the same thing for password if the user makes any change on the exit of this I want to put whatever they've entered I want to put it directly inside of B2 so that's exactly what we're going to do inside this code right here private sub exit so that means when they exit this field when the user exited whatever is in that field I want to take it and put it into B1 me is the form username value so the value of the username I want to put directly inside users B1 same exact thing for the password on exit any change they make going to take the password field value and we're going to put it directly inside B2 so that means any changes they make so now that we understand that we know that automatically if B3 is true because it's now check B1 and B2 we know it's the correct password so what I want to do then is I want to set the current user I want to put that current user directly inside B4 Fred Frederick so that's what I want to do inside the code forever I want to put that and the reason is because inside I want to check to see if they're an admin but let's not get ahead of ourselves let's continue on okay so B4 is going to take on only if it's a correct user I'm going to take on B1 meaning I want to put that username copy it then we're going to hide the form and now this is where the worksheet comes in what I want to do is I want to hide the works if the admin look go ahead and this is we have an admin sheet here admin we want to make sure that to check to see if it's an admin I don't want let's take a look at this year we've got an admin sheet here oh it's hidden now let's let's log in here so it's unhidden Fred Fredericks we're going to log in as an administrator one two three four five and click ok so what that's going to do is log Us in and it's going to show this admin sheet I only want this sheet displayed for the those who are admins so how are we going to do that so we can do that through here what we can do is we're going to Loop through every single sheet in the worksheet in the workbook using for each worksheet in this workbook worksheets if the worksheet name we're using the code name here equals admin then what are we going to do I want to double check to make sure that it is an administrator how do we know if it's an administrator back into the users I'm going to use a formula if this is true we know it's an admin how do we know that here again we're going to use if air and we're going to use if and index but this time we're indexing the user type I want to look up the user type I want to match based on whatever username is based on B4 so if it is admin we're going to look it up we're going to index I'm looking up this user type I'm going to find it based on the row that's associated using the match username B4 we'll want to find the row this is the row this is the in index and this is the column column one if it's admin if we find them we know I'm going to mark it as true otherwise it's false