How To Secure & Protect Your Excel Application | The Ultimate Masterclass

0 0

Hi this is Randy with Excel for Freelancers and welcome to the Excel Application Security Training and this week I'm going over 18 different ways on how to protect your static working and your code from others or from being tampered with I'm going to show you every step on the best ways of making your applications safe It's going to be an incredible fashion you won't want to miss it I can't wait so let's get started thank you all right thank you so much for joining me today we put a lot of work over the years into building apps but today I'm going to focus on protecting all that work protecting the end of intellectual property protection users from themselves Making sure that the app stays stable and robust so we're going to go over 18 different ways on exactly how to protect your apps and we're going to do it all in this invoice order creating this a few weeks ago so if I wanted to pick this up of course I would make sure this whole app with all Protection methods and code will be available to you with the links in the description where you can use your email file and name and go ahead and get that sent to you absolutely free great way to support this channel is through Our client every week I'm doing these apps and I'm adding more features or I'm focusing on an area or I'm fixing something based on your suggestions so if you have an idea you want to highlight the added patreon is the platform to do that actually last week we did this restaurant the manager of the coffee shop suggested ok maybe we want to add An extra receipt just for the bar and for them to only send the drinks so I just added this feature we can now send only the drink receipts to any specific printer so we were able to do that and as you can see here we have the drinks already that's also another suggestion was to add a kitchen display so the kitchen can get the order up to date We have a horizontal of all the orders that go to the kitchen when they're made and sent out who they are all this list updated automatically and I added a dashboard here this is only for patreon members so go ahead and jump it's just a few bucks a month and it's a great way to support this channel other ways to support it's free Absolutely all I ask of you is go ahead and click that subscribe and the bell notification icon that will help don't forget to comment below I 'd love to hear your thoughts and feedback reply to every single one a A week so that goes on to help us okay so let's get practice how do we protect our hard work how do we make sure that I 've been over a lot lately what I want do I want to just focus on protection this week because I haven't gone into that in great detail lately in these trades so we're going to do this week I dedicate all that now I have this little invoice app here on what we're going to build on it it's not protected users can navigate before next invoices they can navigate through pages so this page has two pages on this so what do we want to do do we want to add protection we don't want to want to Users can select buttons and we move them from place to place we don't want users to be able to drag and drop or change things that could harm the program also want to add more admin so that only admins have the right maybe we only want admins to be able to edit these customize this invoices so we'll review Everything is that today everything is fine so the very basics the first that we're going to focus on we have a list of 18 we're going to go really go is protect this cell protect the cell how do we protect the different cells friendly now if we say we want to protect certain cells and we want the user to enter in other cells what we want to do is protect the whole sheet so we go to review and we go in the protection sheet that will protect these and we will go in detail about that protection but what we want to do is that we want to separate we want the fields and cells that we want we want the user to make changes for us we don't want to protect what we are I'm going to do is I hold down the control and I'm going to highlight these and all the fields that I want the user to edit I'm going to keep those unprotected for now After that the column total is of course a formula so I'm not going to click on that and then I also want to make sure that we're doing the taxes that we want the users so I'm going to keep control and highlight this and what I want to do is I want to make sure that these cells are not protected so I'll go to the right click on that and I'll go to anywhere you can see formatting cells protected cells here so we're going to go to formatting cells here and we'll look under protection last tab here and I'll make sure those aren't locked They are now generally unlocked in every sheet when you protect the sheet in general every sheet by default is already locked so what I mean by that is if you add a new file a sheet here a completely new sheet if you right click on it and go to format we can see that by nature this The cell is generally locked your cells will be locked even on new sheets so if we want the user to change to that then we'll uncheck that that's it for the new sheets we want make sure we also backtrack now let's go forward and delete that sheet I'll delete that You don't need to put that sheet back into an invoice What I want to do is that it won't have any effect once it's unprotected Users can still do changes to individual cells in the sheet So how do we do that well as we want to do is we want to go into revision and Then we will protect the sheet we don't need to add a password for this we can protect the sheet we can allow users to select lock cells or we can unlick we can let them choose only open cells we can allow them to Format Cells Format Columns Rows So We Won't Let Them Do That Insert Rows Definitely We Don't Want To Do This Insert Hyperlinks Maybe Delete Columns Definitely Won't Do It Probably Never Want You To Do That Sort Rows Definitely Delete We Can Use Auto Filter Maybe Using Pivot tables yes edit objects now and this is checked to allow users of this worksheet to edit objects now that means for shapes so when i uncheck that users won't be able to change shapes and edit scenarios and i'm going to click ok now you see the pop calendar is not in the worksheet ok That's fine, that means that obviously this pop-up calendar isn't available so we want to make sure that that becomes available when we choose the thing so what we're going to do is go back to the review and do that without protecting the sheet and now what we can do is when we protect it So when we choose to edit objects now the founder can count the pop ups I'll be available and I'll show you a nice file on how to do that but also at the same rate we don't want them to have these ability so how do we do it we'll do it through a macro and I 'll show you basically what we want to do just when we select the user here only then I have to allow the to display this calendar by editing the objects but not through that we'll go through that that's it for now you see I'm protected now I can't select in any cell I can only select cells that have this ability so that's basically what we do global protection of the cell by simply doing this so users can now and notice the total I can't click the total if I toggle it it'll just click to the opposite cell and that's because our group is protected I can't select it I can't do anything I can just select before and after this global protection and we want to do this fine and of course I'm going to show you how to do it within VBA as ok basically but nonetheless There it comes with a few issues and here's what happened when we protected there are also cells like this here that I'm not going to hide the columns obviously and I want the VBA create to be able to change those if I do a previous text it will be generated wrongly so if I do that x stay you're going to see something like this app definition or object id error when i debug I'll look at the line code here and it just says everything we say B4 equals the bill as well nothing wrong with this code here but by protecting itself and now we can unprotect B4 if I was resetting that and decided to unprotect the B4 so what I'm going to do is go back to the revision I'm going to drop this here and show the tabs if I'm unprotecting this sheet then I go ahead and take the B4 which is the invoice number and I'm unprotecting this sheet so I'm just going to Formatting cells here and I'm going to uncheck the one there is now other errors that could potentially be created but not this one selected now if I go back and protect that sheet again clicking ok with all the same variables and I'll then now we see we're debugging now it's different now It's B1 right because we didn't unprotect watch that's what I really want to do really want to do is I don't necessarily want to unprotect every single cell that I want db8 to change what I want to do is I want to unprotect cells can t Gonna just use make changes to it so only those now are from VBA I still want to protect so how are we going to do this well what we can do is we can do that inside the code I wish there is a feature here where we can choose this but this is in Actually VBA well basically what we inside the protection want to do is called user interface only which means only users can change that VBA is allowed to make changes to protected cells again VBA is it allows changes to cells that are protected but the user is not just the user your interface is on okay so this is what we want so what are we going to do are we 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 the same process in VBA so let me show you how you do the exact same thing but Inside VBA so what we're going to do is we'll go to developers we'll go to Visual Basic alt f11 is also the shortcut there you can access that and I have a module here called protection now it's called protect worksheet we'll go now there's everything these commands Now pass if you want to know everything I'm putting right here inside the protection so that's what we're going to focus on the password is the first is optional so it has it all this detail is a string specifying the case sensitive password for the worksheet or workbook that We have drawing objects Scenarios Contents that We 'll go to the person I am I 'll focus on that Same options Almost the same except for the UI Just you see that here User and interface only That 's the thing I really want to focus on So it's optional It's a different wording Right to protect the UI But not macros if this argument is issued protection applies to both macros and UI that's why we get this we are getting this error because VBA are not allow changes to protected cells but I want to tell Excel yes please allow VBA to make changes on the protected cells but not the user so what are we going to do is ui equals true to protect the user interface but not the macro so that's what I want to set that to within the code that's exactly what we're going to do to him first of all we're going to select the paper we want to protect we're going to put a password in I just leave the password blank for ease this is where you're going to put the password file drawing objects equals true right if you want to allow them to edit things if we change this to false The calendar will not appear but what we can do inside our calendar we can do that show that 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 you through this during this macro you uninstall Protect it very quickly and then you re-protect it so that's a great way for that to do the contents right as we want to protect the contents scenario so that each one of these scenarios is defined here protect the contents of the scenarios to protect them for a chart this would protect the entire chart you don't need to read both of them One of those you can download this and go for it but they're basically the exact same thing this is one this is the most important UI just right that means when I run this it will let the VBA macro do the change AT on protected cells and that's the most important thing so allow formatting false and the rest is kind of exactly the same allow insert false rows false allow insert hyperlinks I add that a lot delete columns true false delete rows wrong sorting true filter true allow pivot tables uh right and that's all Something here so the idea is that we as developers always want to make sure that this code works now that's the thing that you also want to remember this is very very important it's a lesson I have to learn this only protects the UI once you save the work rate let me show You this is very important so I'm going to run this fine so I just ran this macro okay so it's protected now and now we'll go to our bill screen and we'll see that in particular I can't click anything here other than those unprotected cells if I do the next and the previous notice There 's no error let's take a look inside here let's take a look at invoice number B4 upload invoice B let me remember we have a problem now B1 goes to and I go let's say look in the first row okay so we're allowed to do it basically all what we want but that's it protected so let me B4 protect this I want to protect B4 because it's changing I 'm going to revision and then I'm going to temporarily unprotect it and I'm going to then protect this cell so what we're going to do is we're going to go into shape and I want to re-protect the cell Now we know VBA is making changes to this click ok now what I'm going to do is I'm going to run this code again here it's running this code we see it's not protected right I can't select B4 however let's take a look at the B4 notice Oh, We're on the first bill let's go to the next note B4 changes to 2 B4 to three so it's change VB is make these changes but it's locked so this is very important now what I 'm going to do is I'm going to save this workbook I'm going to close it and I want it do you want to save it just put yeah so we're going to save it we're going to close it and then here's all the details also on protection here is the Microsoft website I'm going to open it up again and I want to show you what this problem is and then how to fix it of course also what we're going to do is we're going back here I haven't changed anything right , so let's go previous look we have this bug again why did that happen why did that happen now we protected it it's protected but why did we get it a bug what's wrong let's take a look at let's the bug it's going to be the same bug we hired you B4 right we try it make changes to B4 here kind of Unfortunate but very easy to fix though we protected it properly and ran the user interface we need to run this macro every time we open it remember even this protection here we will need this protection worksheet run this when we open the workbook every time so if I went into the workbook that means this one here I did n't remember I didn't remember there should be no reason for me to ask microsoft this is not until it changes to false even though the worksheet is still protected but for some reason this returns an error I don't know why but than easy fix all we need is take this macro go to this workbook go in the workbook open where is the workbook open you can also find it here the workbook and we already created the macro file for it then click open this is the workbook open event what I'm going to do is I'm going to it uncom ment that's okay I'll do it save it so let's go ahead and save the file all we did save I'm not doing anything changes I'm going to close this workbook again to save it again there's the file working on this ok now we're going to do it go back inside I'm going to open it again the same here and we'll So now we see that it's working on this open workbook open event so we'll open Excel opens the same application and files we just know that this macro will run ok so we didn't do anything else now if you did a previous look it works fine and there are no errors because this The macro ran on open so it's very important any time we use the UI just like we did make sure you run the macro open the workbook again it's this workbook and that's where we'll go on these macros in a little bit kind of important but I want to make sure Protecting the works you run the macro on open will ensure that your workbooks are protected, your worksheets are protected, your individual worksheets are protected and we also want to make sure that this thing works every time because we know that this part is not remembering when it is n the workbook is closed okay we're gone that's a really important part of it that's really important because that's going to allow VBA a lot of power to users and that's what we want to not give them the ability to mess things up formulas so VBA has this ability so I'm like that so that covers cell number One protect protected cells and worship as we can do range protection if we want to put a specific range we can do that how are we going to do that there are times we may want to protect individual lists or ranges of course and we can do that using the feature so let's take a look at the billing list and now if We go inside the review tab we see we have allowed ranges to be edited and so we can password protect that so how are we going to do that just click new here and then we just give it a name so we can say billing list we like and give it a specific cell if we want go back to it and give it a password so we can do it one two three four five and then setup permissions we can see if we want to give specific groups permissions we can do that we can add a mod group so it's useful Sort of though I have n't used this much personally then click ok and now we go in also re-enter the password and that's going on just to make sure when the sheet that range is protected now so we have that there and it will say range open with password when the sheet file is protected so Even though this sheet is protected this range can be when the user has this password and we can delete that and that will allow us to do that this is a great way to do that so we can protect individual ranges or lists if we want based on the user password that's 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 what you want unprotect the sheet you can do that with this little feature here it allows modification ranges okay very good continue on here list protection worksheet Of course we have the workspace section gone We got over it with VBA it's true we understand that inside a review when we protect the sheet we can go through this list we also know how to make a VBA file for you to record the password Also you can say protect the worksheet and lock contents of the cell so you can do that also add that and there are all these object types there also the workbook structure is very important you probably don't want unless it's very specific within your workbook type that you don't want users to be able to add Delete or modify existing worksheet names so we have workbook protection so we can protect the integrity of the workbook and using this so once you set the password file here it will say window structure so one two three four five we can do that then what this is make sure users can no longer add it so if we reset the password there enter it again now we can no longer add a new work grade if I try to note nothing happens when I try to add a sheet if I try to change the name of the sheet rename it I won't be able to move that back up There I want you to be able to see this even if I right-click I can't rename it notice there's nothing I can't rename I can't insert it I can't delete it this is protected yet the integrity of your workbook is protected J and we can just unpassword protect it here okay very good so we understand that also protecting this workbook is very important so you want to make sure that most likely workbooks you launch you don't want them to be able to change that when you use a special word worksheet names Sometimes the code that you write uses these worksheet names if users are allowed to change those names that can create problems for you workbook although I usually try to use code names when I work notice this invoice this is a symbolic name so this particular there are two names so if we take a look inside this invoice sheet here and click properties we can change the symbolic name this is the symbolic name now if you use this name within your VBA users can't change that unless they have access to the VBA form so we try to work with these symbolic names not sheet name so symbolic names are the best card game of course it can be changed by this one here inside the tabs unless you 've protected the structure of the workbook so also very important there okay this is the name of the sheet here that's where yum Have the users change that within the sheet so if you change it worksheet here to say invoices here you'll see that and then the change is reflected in the VBA form here so you notice the change here changing it back it will also change on the individual invoice so another way to do that what I've tried to do inside the code is to use Codename just inside the code and not necessarily the name but using the workbook protection we just went through will allow users to make sure users can't change the name of the work as as well as add or delete existing ones well that will cover workbook protection workbook protection and also what I want to do is we can Also set the workbook open we can encrypt the open workbook with the password that means they need to already use a password to open the workbook file so how do we do it fine this will be under the file and we look at the information here we can see that we can protect the workbook we make the encryption with a password They can be made read-only meaning users can make any changes which is usually rare but if you give someone a report and you just want them to look at command and make no changes can be useful so it prevents accidental changes by asking readers to opt in to edit so that is useful so you can secure that encryption with a password this requires a password to open the workbook even if you set the password file one two three four five Make sure I didn't use this much other security use if you lose or forget your password it can't be recovered and it's advised to keep it it's okay with password lists so keep in mind that can be kinda scary four five clicks ok what's this we'll enter So one two three four five again clicking OK and now what we're going to do we're going to go back and we're going to save this workbook that I'm going to close we're going to open it and we're going to open it and now you'll see it's going to ask for a password to open it like this is another way you can protect it so if I'm going to open this again Here and we see that it requires the password to open so we click ok and enter our password so that can be another kind of useful if you have a really safe that you want to make sure that people just what we're going to do is we can ar encrypt with passwords so we see we can encrypt it so we do that we can clear that now by clicking ok so we don't want a password yet there's no password that's attached to that we've just made it clear okay so good that's the way we We protect the workbook with it open and we can encrypt it with potassium we can also protect it with VBA now we've bypassed protecting the work with VBA and that's the thing you would normally want to use because that would allow us to use that in general when you protect the workbook because it would allow us to do that user interface only and that Very important so that's how we protect workers through VBA we've overdone this method so there are two methods one via worksheet here and then one via workbook code so this one is via worksheet and we've seen the differences we can do and more power here because we can use the interface The interface is the UI only when we use it through the code okay so fine so next what we want to do is actually show you exactly how to let VBA make the changes in the code that you want also that might be here As sheets hide certain sheets that we don't want users to see if we have let's say list of invoices this wouldn't really be useful for the end user we might want to hide it now we can easily hide a sheet simply by clicking here then hide it here and what will that do hide it but this limited protection because users depend can also based on security type unhide it so there is another option friday until bill list notification if you click here it will show now we can see it again so we can hide and unhide this is basic kind of how to hide but what if we want to hide That users can't show that and that's it 'll be called very hidden very hidden we can do this through VBA okay so going back to VBA we'll go to what I want to show you some of the things we can do here that either with VBA there are a couple of ways let's take a look On this list of bills here's the sheet that we were just if you go to real estate here we can see that here under visible visible so one way to change it is from here we can make it hidden now that's the same way we just did it there so change it there also go to change it here if you right click on any sheet it goes to show sorry so we can see that I just hid unhidden two items of invoice and invoice list so it's the same way we can hide it so it's the same value hidden so there are two ways to hide it Hide it by right clicking like we did or just remove it so we can click here holding Knight control if I want to show everyone who click ok it will show them the exact same thing as we did here but there is one more thing called hidden too if you By changing this list of bills to very hidden we see it's no longer available so now it's not available anymore if I'm right click here note there's nothing unhidden I can't show it because this is unavailable even if I were to hide this let's take a look at it hide I'm going To hide the invoice items and now show it if we take a look there is only one sheet available the list of invoices is not available because of this it is hidden very well it can only be unhidden through the VBA form only can we change it to here even when we change it back to Hid den now we'll be able to unhide it from here now we'll do that if we click on show we can now see that it's available again this is a really nice and important thing when you never want end users to see a file you'll want to use a hidden sheet too we can also do that Through VBA here for example we can hide a sheet called hidden sheet it's the same thing as this cow popup so we have a right sheet here let's take a look at this cow popup through VBA and I'm going to pull that over here so this Cal popup is only used for the calendar So we definitely don't want to see that we can now make it hidden run this macro that will disappear I notice that it is no longer there again as I said this is just the basic security but if we want to achieve it really well hidden we can see it now inside this VBA sheet by clicking here on the properties we see hidden But through VBA we can also make it cool well hidden if you click on there and run this macro it will be very hidden so far again we can't show it off here if I try to scroll up here and again I can't show it and that's exactly it i what you want for sheets that users won't have access to or access to and you don't want them to see what you want to use this property is very hidden so i'm showing you two ways to do it through VBA here or again through here inside the properties you choose here on the sheet Then change this property here so there are two ways to do it fine let's go ahead and make it visible now I've shown you both directions within the macro even masking sheets is very important part of protection so we covered it that's fine so we're halfway there our menu is now disabling drag and drop Now this is really important because let's say I have this sheet here and we have a dropdown list if you allow users to drag and drop this can be very useful what do I mean by that ok useful if you allow users to drag this over it won't be good even unprotected sheets I'm going to protect this again other through the code so if it is let's go the protected I'm going to run this macro is the protection we want if you take a look here I have a drop down menu I don't want users to be able to drag and drop these cells Anywhere along note this box is available there is still available to be drag and drop and I really don't want this good so how do we write we don't write I want them to be able to drag and drop these features that are going to be the problem so how do we disable drag and drop well again we can doing this through a macro very easily called disabling drag and drop application cell drag and drop error if you run this macro right now and take a look here we see we can no longer drag and drop anything let's go ahead and try that if trying you can see we no longer able to plus a notice if you hover over that nothing happens i want to show that to you again so you can see that little addition is not here however if you change this back to true we'll see the different icon on top here so i'm going to run this macro Here and now when I choose something we see that little plus these little four arrows there when I click on them flares that allow me to drag and drop them four arrows that don't show up when I hover over the line let's zoom in on the picture I want to show you that one of these four arrows doesn't stay cat when you hover over select something else ok so look at it this now you see we cant do it here we go this is the correct zoom level I want you to be ok so you can see that we now we can run this macro change it back to false and run it Again it's going to stop us from doing that and that's almost exactly what it wants from you you never want end users to build drugs notice that when I hover over that it doesn't show those four hours just a plus line okay so sell drag and drop is very important let's continue our protection So we covered dragging the cell and we covered all of this let's make that italic so we know we were over that and now what I want to do is I want to disable copy and paste this also can create problems for end users if we allow them to copy and paste if you are to allow them even if you know that right click copy this if you paste it somewhere else it will create it the main issues we don't really 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 right click copy What they can do with it here inside the house they can copy it here they can also use the hotkeys Ctrl C and then paste it right I want to be able to disable all that and everything three of those cases so how are we going to do that well first we can obviously use with Macro so that's what I'm going to go into in a different module here called cut copy and paste macros that's a little bit more involved and so the first part of it is basically disable that cut copy and paste allow so is toggle that means if it's true it will happen allow it if it's false It won't allow it so how do we swap if it's true I mean let it it's just a little test run if it's wrong then 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 transpose copy and paste then let's just swap CC ok So what we're going to do now is I'm just basically gonna take this copy here paste it here and I'm going to change it to false meaning they're not I'm allowed to run this macro here and then we'll see how it differentiates so I'll run it okay if you right click Above notice is how to cut copy is disabled here and if I do control C it will say sorry copy paste or not if I control V.

Also again it doesn't allow so how does that make a macro so let's take a look inside this switch here okay so let's take this macro away from toggle copy cut and paste allow like Boolean so allow us variable and it's a boolean so it means it will be true or false if true we will let them copy and paste if false we won't let them do that first thing we want do we want to enable this list now 21 19 22 and 755 are the numbers associated with these lists so each list contains your associated so we want to make sure that you disable that and we can do that through this macro here allow allow to allow so this is either going to be true or false we'll do that also allow that So we can do that so we call 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 menu id number so each item in the menu has an internal identifier so we call those increment then what We are going pon to it are we going to enable that again that's going to be true or false that's what we're going to do in this particular macro we 're going to dimension the c bar as a command bar we focus on those menu bars and also dimension the c bar control it's a command bar control so We either want command bars or controls so this is the command bar this is the control and this is exactly what we're after and what we basically want to do is we want to disable them we're going to go through all the command bars in the app command bars if the name is clipboard its right say As long as it's not equal to the clipboard then what we're going to have to do is we're going to reset the command marc control equals to find we're looking for the given control id one of these four digits here 21 19 22 or 755 and we'll do recursion equals true so we want to go back and make sure that right if it's not nothing then we just want to make sure it's enabled so we're checking that make sure if there's a right no and nothing cancels each other out means if it's found we'll make sure it's enabled now we can do that's disabled If we are very important A if we make that disable it will then disable in a big way now we understand that part of the macro so that's how we go through either allow or disable it we can also activate or deactivate drag and drop from here I've already shown you here so we can also Doing this is dynamic here so it's the same thing we just showed you in the other module okay also what we want to do is we want it to activate or deactivate the broken version you remember hotkeys shortcut keys do that too if I control C we want to make sure you tell them it's not allowed So how are we going to do it we do it well we can do it here again we will use select state and it will depend on whether allow is true or false so this is our own variable here it will be either true or false so if it is false we will not allow them the application we will clear the clipboard If there's anything I want in the clipboard to clear it and if the user presses Ctrl C that's the up arrow control that's C so that means it's on the key when they press Ctrl C what do we want happens we want to run this macro called cut copy disabled and all that th A macro is just a simple message box cut copy disabled message box sorry cut copy and paste is disabled in this workbook so that's all we're going to do is just run this macro to let them know that it means they click Ctrl C Ctrl V Ctrl X or again delete shift also We'll definitely do a disable conditional cut object also trying to enter anything using the extension again that's so all that functionality will be disabled but if it's true all we'll do is bring it back to that original location again that means nothing will happen we're not running Any macro that runs this which will basically reset so that's all we have to do is reset these hotkeys to original files by not putting any macro associated if we want to put the macro we'll put the macro action here but we're not going to set it back to default so that's how We take care of our Cut Copy at least for though right click on a part note we still have the ability here here and here so we also need to disable the toolbar allows here at the top we have to cut copies we don't want it happens so how are we going to do that well our toolbar is controlled by a file something a little different called a UI editor to do that what we need to do is download a free program called UI editor because we want that part of the toolbar disabled so how do we do that this is what happened ok And again the first thing what we need is a separate program called the UI Editor and we can get that right here so we can go into this here let's take a quick look at this called Better VBA Solutions there is some difference in the custom bar places it has it but this The person has it and it's basically called Office Custom UI Editor here I'll include this link in the description as long as I remember if not please mention me I will make sure you get it I'll put it here also I put it inside the workbook here it's called Clients with that link it's here inside the workbook okay you can have On that from here okay so once you have this what we want to do is we want to go ahead and download it so we click download and what this will do is download it for us and create a zip file what you want to do next is what you want to run it and It's going to create this app called Custom UI here Editors look like this when it's running I'm going to run it now and you'll see it's working basic called Microsoft Office Custom UI Editor okay so what do we want do we want to open our menu open the app what am I in this workbook here this is One I'm using Excel application protection so this is the one we're going to be focusing on this here so I'm going to click on open here and I'm going to open it from inside here now to do that I want to make sure that also if I'm going to save this notice there's nothing here there's nothing here what what i want to do is i want to add some code for this now i put this code inside the workbook it's not a macro but i put it here just for convenience here if we think i put it under protection macros here all the way here ok scroll down here i put it here i'll comment on this i'll Copy this and I'm going to comment this I want to make sure it doesn't work so use this code in your custom XML editor is appropriate for that what we want is the XML code so all we have to do is just copy this make sure you don't include comments so what you want to do yam its just an uncomment that you copy and then recompile we don't want this because you know create any problems for excel because this isn't VBA code so we have the code there then what I'm going to do is I'm going to go again in modifying the UI I'm going to paste this in Basically and what it's going to do is run this now we also want to make sure when you try to run it what we're going to do is just check validate and you'll see something that probably says unknown namespace 20061 should be 20907 so what we want you to do is just make a little change to this change this to Nine and seven of course that's the person I am I 'll give you a 97.

Even if you see that because when I downloaded the first file we'll make this change over here because it depends on your version so I'll change that to a seven but if it's anything else just follow what it says and I'll update This change make sure save this ok so this will work now if you try to validate again this is called validation it will just be ok ok it's formed ok now what I'm on now I wouldn't do that I wouldn't save this now what I want to do is I just want to save this workbook and a Na you want to close it before saving any changes so that will be saved and now what I want to do is save it here and that's what it's going to do it's going to look for this particular shared RX macro basically what we're doing inside this bar dump what we want to do is when the user selects the cut that we're going to it runs this macro when they choose copy we 're going to run this macro bess it's the same macro so this is the last macro RX get enabled so that's what we're going to do I'm going to put the focus on inside the VBA code so I mean that when the user clicks this thing will happen so what I'm going to do now is I'm going to save now that the workbook is closed fine I'm going to close it now just to make sure it opens I'm going to open the UL editor again just to make sure it saves so we open the program here it will launch again I'm going to open the workbook here looking for the workbook here this is clicking open and we want to make sure the code is there and that's exactly what I want okay so we copy and paste our coast then we we can close that we can reopen the workbook so now we can look inside the code and we'll make some modifications to the code because I temporarily disabled the code only you can see that h T the Excel application the protection is pretty good so you see nothing happened with the we're still there so we're going to go inside the developers inside Visual Basic here we're going to look inside the modules here I'm going to focus on this cut a copy now that's the one I want to focus on remember we covered this already but this is what I wanted to focus on the public sub grx UI cut version paste as the ribbon UI so this is the one we're going to focus on this is the macro that will run the GRX group I'm the ribbon company ui equal ribbon fine and now what I'm going to do this is the Public Sub RX macro shared by ref As I buy that this is the control and this is the return value so I'm going to change both of them one of those two right to false because I just wanted to truce show you the difference false and false ok now when will this be run if we take a look inside this handbook of our want to run this on workbook open that opens the workbook where i want to see the workbook toggle cut and copy worksheet functions and we have that but also if we take a look inside and what now this will launch automatically when we open it because it turns on this toolbar but what limit W when I don't switch to another worksheet I need to make sure it doesn't work and that means when I change to a brand new worksheet so let's go ahead and save what we have now I'll close it and reopen it we'll look at it some changes made ok so we'll open the app again another and we'll take a look inside here and we see we now have the "cut copy" function everything is disabled look I'm not doing that and that's exactly what I want so now everything is disabled just the way I want it we want to make sure it gets enabled when I select another workbook note that we still have availability here notice copy cut and paste that if you run tests here and I want to copy that here and paste it here it works fine when I go back to the existing workbook none work that's exactly what I want we have the ability in it so how does it work well what It's happening within developers right on that worksheet is deactivated so if we go back here and we go let's go it's not that basic visual here it goes back to this workbook is what I want to focus on here workbook here and you'll see the sheet deactivate that means when we do the sheet it's deactivating what we want doing it Do we want to make sure we override so that users can copy and paste inside another workbook but not the current one that's great so I'm glad we covered that so now we see we can disable Copy Cut as best we can actually we disable it here inside and we It can be disabled within the shortcut okay that was a long time crippling copy paste very important through that okay what if you don't want to allow the user to save it as maybe you just want them to save the work but not save it as another workbook so that's another feature we can disable the save as feature Another way to protect it we can do it relatively easy so let's go back inside our protection here and we can actually say let's go to the workbook and I'm going to uncomment that here we have our code here coming and it's going to be on before saving the workbook we can find that under before saving we want to run some code if save ui then right if they save ads if that's the only option they collected let them know save as function is disabled save info as disabled cancel equals true means we delete don't let them soon so i can save workbook file no problem if you try to go into it file and click save as right next try change the name just any other name and click save you will see that the save as function is disabled we can now not save this as another name we can only save it as the current name and it may be This is useful if you just want your users to save it as a name file in a specific place it gives them a bit more security so you can do it again save as is a very very useful feature if you want that okay so we also covered saving ads now what if You have multiple users correct users and you want to hide password files also be able to give some users notice certain features when we do this create this edit mode this allows users to customize this but what if you only want certain users to customize it and not others then what we do is create Some additional users beforehand let's say I can turn this off or on but I only want users with some admin passwords to be able to edit this I can do this with multiple users so how are we going to do that ok we can Create a form when they're logged in that will allow them to put in the username and password and I just did that we see here inside our forms we've got the login form I've also created a screen called splash screen now this splash screen is basically the kind of screen we want to see at the time appropriate the user logs in so when you open the workbook file I want this screen to appear so how are we going to do that will give us the ability to log in now if you log in to Fred frederers and I'm going to show you what and then I think one two three four five click ok and that's Is the admin password it will log all the sheets files and we can get it so how do we do that do that how do we give certain users the ability and sure use is not good of course we need some sort of list of usernames so I have this here I have got a list of application users so notice david davis is a user And not the admin so when I'm logged in how would that help how do I make that change let's take a look inside go back to the start screen and I'll go to the user logged in l of course i will show you that i log in under the command of david davis this time david davis and the same force so i run two three four five i click ok but notice now that editing mode is off if i try to turn it on it says you don't have admin rights to make changes to this please check your admin bill So this means we can assign different rights to different users based on the type of user so here inside we have the userid we have the username the usertype and the userpassword we can now also hide these passwords if we go into them go back to protection and then after that our menu is called hide userpasswords Your mask and now there are several ways to hide it let's go through a few different ways first of all you already know how we can hide this sheet we know maybe we want this particular sheet that has all the username and password information that we're going to have to log in we want to make sure Than we hide the sheet though another way to do it is to embed a focused file and we can just basically hide this entire column so there are a few ways to Um so obviously there are multiple ciphers that hide you the sheet can hide the column just hide a file then there is another column we can do hide it so we can hide the column file then protect the sheet we can hide the hidden sheet very well we can also maybe we don't want to have any ones the background is like we can do this no fill and then we can't do any borders so we can do cell formatting here and then I'm not going to do any painting just kind of make it look another way to hide this basically as an additional way of hiding 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 and then what I'm going to do is I'm going to go into the custom format more number formats here you go into custom and I'm going to give it three semicolons one two and three clicks okay so we now click now he sees you can't even see the word pass you don't even know there's a password here so it's an extra layer of security very very very good we see we can see how we hide passwords now we can also hide passwords inside our user form until the fireworks go Go back to the beginning and click on the login and we see fred fredder we can also do it here note that the password is not hidden right we can see it but what if we want to hide it or hide the password we can do that also inside the form details so we will go inside this login The form here I'll choose on the password and we can give it a very very specific password here in the properties I can give it whatever character I want so let's say I use a star let's say a star here okay so far I've given this an asterisk or star when I go to login again Here we put in the name and then the passwords you want to see one two are automatically masked three four five you can't see them as well this is another great way to hide the password file using masking to do it okay very good so we're over that now let's take a look inside let's take a mask Covered User Passwords Good Coverage Save Disable Save As Feature We Got Covered User Rights Selective Moderator of Protection Right When I Remember When I Said When I Was When Frieder Bit log in and was able to edit the bill when davis log in wasn't able to how do you do it well we do it through the macro so let's take a look inside the sandbox here and we also have the login form here so I'm going to actually let 's go to the display code when the user clicks ok button that's a button right here ok i want something to happen if you double click on that here let's click let's get out of it and double click on it again we'll go straight to the macro associated with this what I want to do with this I want to validate the password to make sure it's the right word the secret and the correct user and how do you do it we do it well and we can do it with some named ranges and some formulas what i want to do is through vba i want to put whatever the input is the username i want to put it in here whatever password they are entering i want to put Here inside B2 one two three four five and then I want this formula to let me know if it's correct or not so we can do that with some named ranges and so I 've created some correctly named ranges here so let's take a look inside formulas name manager and we go all the way to the bottom here I have three different grain name ones one for the username it's using the offset formula so it goes on to include all the usernames I have another password for the user is right there remember we hid the user password so that's right I'll probably change That's because that's going to confuse people who don't know they'd be looking for the password so I'd rather reverse the end-user type so we've got all those in the name domains again listen all we have to do is go in the house and change that back to public just so it doesn't people get it confused okay because otherwise it can be a real problem for people some people don't watch the training videos and get there it's like where's the password I can't copy and paste so we paste the format there so here's the password here I have now I got three domains named one for the username one for the usertype and one for the password so we can use a formula to determine if it's true so we're going to wrap it if we're going wrong let's do it if we're going to be the index index this user's password A what I'm looking for I'm going to match that I want to find the associated row 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 inside the usernames and we want an exact match so this is our calculations you're going to have a row this is the column we're on it we are looking for that what we are going to do is this will give us the password now I 'm going to check if the password searched is equal to whatever is in B2 if those are equal then it is true we know if it is wrong username we won't get match here so we know that either the password or the username is incorrect it will fail us if both are true it will make us true if you make one change here it will turn false if you do one change the password field again it will also go false So it means either the username or the password is wrong this will go to false so all I have to do inside VBA is check if B3 is equal to true if it is you have a correct internet username and password so let's take a look inside this ok we'll go to Dimension the worksheet as a worksheet If the users I think we don't need this but we do if we are going to loop through the worksheets as a way so if user B3 equals true we know it's true password let's do the correct password ok very good how do we get that we'll make sure It's correct but what I haven't made it clear is how do we do this get B1 and B2 with user forms and that's what I want to go into just a little bit inside our code so how are we going to get that let's look right here if the user made we got some names for that so let's take the file look inside and I'm going to view the application just so I can identify it with the name of that we're going to view the object so this field is called username let's shrink this a little bit so we can look at it username bring them a little bit closer together so this field it's called username that field is called password so basically what happens is anytime a user makes a change to this field I want to put whatever they have I want to put that right inside B1 same thing for the password if it's a file the user makes any change When out of this I want to put whatever they have entered in I want to put it right inside of B2 so that's exactly what we're going to do inside this code here is a special sub exit so that means when I exit this field when the user exits everything in that field I want I take it and put it in B1 for me is the form username so the username value I want to put directly inside B1 users exactly the same for the password on the exit any change they make it will take the value of the password field and we will put it directly inside B2 so that means any changes It has to make that now that we understand we know that automatically if B3 is correct because now check B1 and B2 as we know it's the correct password so what I want to do is I want to set the current user I want to put this current user directly inside B4 Fred Frederick this What I want to do inside the code forever I want to put that and the reason is I want to check inside to see if they're admin or not we're getting ahead of ourselves let's continue ok so B4 will work just in case it's a valid user I'll take over it means B1 I want I put this username copy it then we'll hide a shapefile and now this is where the worksheet comes in what i want to do is what i want to hide the works if the admin looks go ahead and this we have the admin sheet here the admin we want to make sure it check to see if it's an admin I don't want let's take a look On this year we have a admin sheet here oh it's hidden now let's log in here we're fredericks unhidden going to log in as admin one two three four five then click ok so what in that he's going to do is log in and he 's going to show this admin sheet Just want this sheet shown to those who are the admins so how are we going to do so we can do that through what can we do is we are going to iterate every sheet in the worksheet in the workbook format used for each worksheet in the worksheets format of this workbook if the name of the worksheet we are using Codename here equals admin then what are we going to do I want to double check to make sure it's admin how do you do we know if admin is back in users I'll use if that's right we know it's admin how do we know that here again snst service if air and we're going to use if and indexing but this time we're indexing the type of user I want to find the type of user I want to match based on whatever username the B4 depends on so if it's admin we'll look up we'll index I'm looking at the usertype This I'll find based on the associated row using the matching username B4 we want to find the row this is the row this is in the index this is the first column if it's admin if we find them we know I'll mark them as correct otherwise it's wrong and if there's any wrong it'll be Also false that means this is true and we know it's admin B5 login will tell us it's true or false all right if you change this to david or something else it's ok to be false so only if it's fred it's going to be a real fred feders or whatever Another team another admin Lisa Lopez will work as a file ok ok what we understand in the code is we know the B5 that I want to make make sure the admin screen is visible otherwise I'm going to make a mistake on this non admin non admin users so we see how we can hide specific sheets between add-ons In it and users so this is kind of a nice feature to show you how we can hide it and of course we're going to make it very hidden as we went before because we don't want them to be able to show it so we hide sheets or unhide sheets based on a specific user okay Very good what if it's not admin I want to make sure the worksheet is visible to non admins what do I mean by that that means l every other sheet that isn't admin you want to make sure it's visible non admin sheet so let's put this code there non admin sheets okay very good we can run tests Different of course put extra sheets if we want okay so we understand how to hide the sheets based on the type of user the next thing I want to do is when I go out I'm going to clear the contents of B1 and the password username I don't want to keep them in the cells I want to clear them there's no need to keep with the passwords in there so we can clear it from them and we already know it's already checked out and then what I want to do is on the invoice B10 I want to know I remember again I said I just want you to be able to turn on detective mode t just for admin type users but what i want to do do i want to check if this is in action look at b10 so we see here edit mode this edit mode is true if i change this to two it goes to false so i know if this right I'm going to run the macro that turns this off there's the macro that automatically turns this on the switch I just want to turn on that switch if that's true and no matter what the user I want to turn off edit mode like that based on B10 so I know if B10 is true I don't need To turn off edit mode so we'll do it right here if the B10 invoice value is equal to true then edit mode that's the macro the name of the macro and that's if we're edit we can just go to definition there we see edit mode basically we'll turn this off and check that out in original training text in management console so this is going to turn it off turn off editing invoicing mode if it's ok then what i want to do is i want to activate invoice sheet and please enter a correct username password one more thing when we get back in mode p edit here go to the definition you made an additional change here if user B5 equals false let's look back at the users remember we only want the user the admin user of to be able to edit that invoice so we know if it's wrong if it's not true we know It's not an admin user so we can make that change again let's take a look inside here if user B5 equals false that we know it's a non admin user non admin user we don't want them to be able to edit that bill we'll give them a little bit of a message box and then We're going to go sub out without doing anything that changes well so that can be really useful when we want to allocate only specific rights to specific features in the workbook and then the rest we go on to modify that ok keep going inside our code what else I want to do let's do pull this code back Here and we can pick up where we left off so we want to make sure that we've got the B10 running in mode and then what I want to do is I just want to activate invoice so I don't want to have this start screen active or any other screen so we're going to install Yay that bill oh well if for some reason that's not the real meaning they didn't enter the correct password we're going to skip it all the way here at the bottom and give them a message box that says please enter a correct username and password we're going to output the sub which means the form isn't masking properly the form stays running and we're going to show them this is very useful like we're doing it ok and so that's how we're running this start basically now when we do that I want this to show up fine I really want it to show this screen when the user logs in how to do it Well what I need to do is run a macro that ensures that when the user logs out or closes the workbook we want this screen to show up and we want them to log in so it's going to be on the closed event workbook like this means when we close the workbook we want that to happen Something we're going to do is run a macro called user logout and I'm commenting on this so far I'm going to uncomment this macro so let's take a look inside the protection macros and our user logout is called user logon and user logout c user now when they logged in which means they pressed this button what do we want to happen ok first thing i want to make sure this is within the users i want to create make sure any username is cleared or any of the current user is also cleared b1 b2 and B4 should also be cleared so we 're going to clear this contents and I also want to make sure inside the login form itself we also cleared the values in the login form and then the last we're just going to show that login form so when I run this macro it just shows the form sign in ok what else about checkout time now let's say we want this macro to run every time they close the workbook right the workbook is closed and they are automatically signed out that's why we have added this macro in a sub workbook before closing it means automatically when Close the workbook those two are going to run out of macros we 're going to let the user copy and paste because they've closed the workbook in case something else is open we're also going to run backwards to sign them out okay so knowing that those two macros we're going to do after worksheet as a worksheet what i want to do is i want to hide all other worksheets except for the start screen and i also want to clear the active user i want to make sure to clear it so you get B4 scan inside users again i want to activate i have a string called start this is start that I want this screen to be shown and I want to hide every other sheet to do this we'll run a loop and check all the sheets if it doesn't start we will hide it so for each worksheet in this workbook worksheet we are going to iterate through all the worksheets like as long as the name of the worksheet doesn't That's an even start and then we'll hide it and we'll make sure it's good that hides this additional builds that means when I log off when I run this macro just like this again now take a look there's no other hot sheets there are no other sheets visible here if you try to button click right mouse there is no sheet i can show it so all i can do is all i have is this private sheet here all i can do is login this there is nothing i can do when i log in as admin then it will back all the sheets one two three four five we went this macro where we were scrolling through every single sheet now they're all visible oh yeah yeah that's a great way to protect so we want to make sure that this is triggered automatically when you run the workbook file it's closed I'm going to disable this because I don't I want you to have these issues when you get this workbook so most of these events most of them events will be commented out when you get the workbook now just to make sure it's working save our work here we're going close it here we'll be requesting save again because it's on when you close notice It works if I open it up again here we see it again we this splash screen is not visible and just that splash screen that's exactly what I want let's scroll up a little bit and bring it up so we can see it's ok that's perfect that's exactly what I am I just want that splash screen Visible when the user is logged out until logged back in so you can see how a file really good protection can help us and then put the password very fine okay let's continue with our code this is very good so that's all Something for the user login that's everything for the user we went over all the code that's inside that file simply cancel button all we're going to do what you're doing is hide the clearing form whatever text is inside those fields and filter the content so what happens when the user clicks on the cancel button As you saw it a little bit ago the Cancel command would have done exactly that okay that's pretty good that's exactly what I'm like all right to go on let's take a look where we are now so we went on user rights type protection well chosen admins I'd like to override named ranges we can Hide them also name ranges very easily because sometimes you don't want to be able to let's say you don't want specific ranges let's take a look inside our formulas name manager and say we have user passwords and I want to hide that so note that this is visible I don't I really do want the end user to be able to see this user password we can actually hide that so how can we do that we can do that through the macro let's go back inside inside our protection modules covered logout and Now what I want what I want is to hide the name branches we can do that with a single macro this macro line this workbook is called name from our name visible banks equals true if you change this to false then it will disappear so we are going to take a look at it now I'm going to run this the macro we go back to the name domains we scroll all the way we see the user password doesn't exist it's hidden now ok so cool what if i want to show it of course i'll change that right now what if you name the branches but you don't know the right name what if we hit something but we don't know the name so let's run this again and show that good name manager so far use your password it shows fine what I'll do is I'll hide it but let's say we forgot the name from the sheet obviously we can go in VBA we can see it here but what if you'll excuse me what if we forget the name from the named ranges right so what happens then let's run this and let's assume we don't know the password of the user what we can do next is we can iterate through all the named ranges and cancel the hide up name manager take a look at this hidden now which is exactly what i want now what i want to do is i want to fire up all the hidden named ranges so we can do it by looping through the named ranges we can hide them all or show them all or unhide specific items or show specific if we want to do this dimension named range as name for each named range in this workbook name so we are going to search everything that if name range visible equals false the visible property is false which means it is hidden then i want to show it and this will loop so if you go through it will go through all the named ranges and will unhide all of them so we can now see the user the password is now visible by looping through so this is great the way we can hide named ranges and show named ranges but if we want to lock VBA one of the most important Things are locking VBA You don't want to quit so users can unlock the VBA version Changes can create a real problem So how are we going to do that What are we going to do Tools here is opportunity We are going to Properties of a VBA project What we are We are going to z sign the password so we go into the security tab here lock the project to watch select that and we assign a password one two three four five then one two three one two three four five ok click ok and once we do that we save now nothing will happen yet Close the workbook and save the changes to the workbook and then back to where we are going to open it up again then we'll open the VBA editor and see we can no longer access without actually entering a password we enter the password we see that if we try to click here it will ask us for that password without that password We can't see anything in the VBA editor okay once we enter our password then full exposure to now I'm going to remove that because when you get this I definitely don't want you to worry about it all I have to do is go into VBA unclick this then of course remove the password and click ok ok ok very ok ok we're going to log in at work but again with the admin password and username and then we can access this ok let's take a look elsewhere on our list of 18 ways Amazing for protecting your application VBA lock with passwords okay very good what if we want to hide these tabs these all tabs we can actually hide them very easily with another macro again back to the macro here and we can do this right if we take a look in let's move on down here this is called worksheet tab switch so you can create a file called macro here switch worksheet tabs active window we're going to show it it's showing as boolean in other words it's showing it's going to be true or false if it's true we'll show them if it's false or not very active very easily It shows worksheet display tabs so that means if I change this to false and I'm running the macro let's keep your eyes here at the bottom where the sheets are and I'm running this macro that will hide the whole tab if I change that back to true this bar of TAPS should I'd say it works to show up so this whole tab bar here can be hidden which is really really useful if you don't want users to have access to that so we can do this switch worksheet tabs pretty cool as n able to show I'm happy with that we can do that let's take advantage of this it's a little clear that reads very well very good so that will go away what if we want to lock Excel on one machine this is a great way you say you know what your application protects you create it says I share it with all my buddy and you know I really only want on one machine well we can do that what we can do is we can run a file check to see when it's opened we can create a macro workbook open check machine but to do that what we want do we want to We take something very unique just to this machine and put it in the cell so every machine comes with an id whether it's a PC or a Mac what I'm going to put that inside of B6 all we have to do is run a macro that checks to see if the current machine identifier is B6 or not so how would we do that nicely to be called checking user machine id so what you would do first is going to check if there is an identifier if there is no id let's say you have distributed your application you are going to keep a blank allowing the user to put it on a machine One and then users of course will not be able to th Access to this sheet it's going to be completely hidden so they're never going to see this so what I'm going to do is I'm going to take that and put this here so I'm going to run that macro and what this is going to do is it's going to take my unique device identifier we're going to put it there inside of B6 because I just ran Macro how is this going well the first thing we're going to say the device id is a string and the run script as a string we need to derive it whether we're on a PC or a Mac the first thing we're going to do is run if Mac lb if Mac means that if a Mac machine I will create a script to run the run script it will be a file you will call it set uu ID that do system shell script double quotes meta SP hardware if please don't ask to explain this because I have no idea I found it okay because I'm not an expert on max actually probably worse Someone asking so basically what this would do is take some unique mac scripts if there is an issue with that let me know because I don't have a Mac to test one then what I would do is I would go take that machine that would run this script the Mac would put the hardware ID inside this A variable and if there is an error it will skip that but on a more simple computer create the object we will create the system object and then inside that file system objects we will extract the C now most computers have the C drive so what I want to do is I want to extract the serial number attached to the drive C this is almost every computer has a unique drive C serial number so I'm going to take it comes straight from the computer I'm going to put that inside this string is called Device ID and now I want to do it I want to check to see if it means first use if user B6 Also empty maybe the first time they used it so all we'll do is we'll take this device id we 'll put it right inside B6 now what if it's not empty if it's not empty I want to check if it's a developer now if you're one of the developer you put it in word developer here and allows you at least from a while testing and programming to run was that if user equals developer it's probably an admin or developer we can go out another sub does not equal we'll let the user know please Registering this app on a particular device is not equal to the device id and it's not null then let the user know they install or try to run this app I'm not the original which means there's a number here that's not the developer and that number isn't equal to their number even when we run this you can see my number here It's here but if I try to run this another computer will let me know that whatever is in B6 isn't the same thing and I have to then register a specific machine so that's a great way to do it fine if it's a valid identifier we're just on the way out of the sub ok , that's how enabling adds protection so that you can run Excel on one specific machine so maybe you want to offer multiple license and so on each time you send a request this will be empty and so on if the user needs to use 10 different things you give them 10 different apps or You allow them to start on 10 different machines and that every time they install you for the first time just run this macro on the workbook open that or when you register or log in or whatever and it will only run one time which is once it becomes B6 populate this very application Then tethering this device is very important a great way to do it ok covered lock cell another one of it is a 3rd party app I've used in the past and it's something I really like if you want to go beyond the protection methods that are inherent in Excel and there's a lot you can Its using a third party tool and one of my favorites and it's called xls lock and I'm going to include the link below I got a special discount this is a great great app I've used it so many times it's a free trial and free to download so you don't get anything to lose and you have a lot of security we can Build Let's take a look at some of the security we have there inside XLS lock and also give a list here so once you have it installed you can download the free trial from you can check the website you are installing your system to see if it is 32 bit or 64 bit download free trial I ask you what If it's 32-bit or 64-bit 32-bit or 64-bit if you're not sure which one you just go to file go to file calculate here then go to update options here oops no no there about X Excel and what you can see here here top right will be 64 bit so it will help if the older system is probably 32 bit okay once you download it you install it you 'll get this extra list here and we can compile it to an exe file so that means you can create an exe file based on the workbook is not modified do you want to save it up be it will give us save and what then it will give us that several different options actually it will run a separate program and it will allow you to create your own exe file and the exe has a lot of different options so it is an evaluation I just downloaded it now take a look so you hear the distinction Paddock will protect your current work By converting it into a safe exe file the application you have to distribute the exe file to your end users nothing more than the original Excel the workbook file is of course it got original right only you and give them open it so you can create it output path you give it a title you can Excel Excel versions limit versions minimum required to work is 9 you can set minimum 32 bits what you can set to 32 bits 64 bits or Universal 32 bits will be two different files for you can have universal to work with the boat both or one other at will create two different files Two wraps one for 32 and one for 64 bit you have some configure a lot of different options I'm not going to go through with all of them allow loading or providing other workloads so a lot of protection we just go here also is really useful nice doesn't show VBA compiler doesn't disable protection sheet list non protected so tons and tons of other individual workbooks and worksheet methods that are here also inside this a lot of different things you can do here and you can add more if you want to add additional files sure maybe you have some fonts or something you want to work with your excel you can add them here so that It will add those fonts or whatever you want to bundle with your workbook can be distributed in one exe package so it's very very useful here there's also an extra security you can add so you can do a workbook save here you can save selected cells add don't allow loading or saving other workbooks it will be safe save lets users save put changes back save some options we go and say always show load previous changes save changes automatically save files like normally so you can save some extras Please give it some extra restrictions for that Allow PDF export Disable right click This kind of disables all the nice ribbons and toolbars you could want to sell and cut to scissors Remember we went on that this does all that Disable the formula bar So a lot of greats we do VBA remember security we just got over it a simple VBA lock there's also blocking uh access to the VBA editor there are formulas you can protect formulas and use passwords excel automation formula excel add-ins you can decide if you want to allow certain add-ins or not allow add-ins and We have a custom UI for Excel remember we got a UI if you have a UI like we created you want it completed for you can then add this XML UI format right here you'll be done you'll make sure do you want to use a custom ribbon and quick access so remember we just create one you can Add it in here as well there's a lot of different things you want to hook up Excel to a specific USB just it's going to allow it here you can activate so you can do activation settings a digital setup you can do on a network a for internet activate online interaction validate custom exe distribution in its entirety it's pretty big a really good app I'll go for it include the link below if you want to check it out ok we did it great cover a lot to come out without bailing us covered 18 different great protections different features you can Adding them 17 of these are inherent in the last Excel one We use a third party tool We have got a lot of different protections that are great so you can protect your work and make sure that bugs and problems don't happen when end users get file protection is a great way to do that I'm glad Because I got you covered this is an instructable thank you very much if you want to download this template go ahead of course if you like these templates you want to support in my channel I have 250 of my best work available for download and I will include the file links below that would really help us too great way Others we have a patreon where you can get a lot of different features and fixes and we can focus on different areas to download PDFs you can get early bird previews extra big discounts on To our client the platform a few bucks a month goes a long way in supporting the channel also I have a masters academy freelance class available now showing you nine on how you can become a very successful translator so I'll include this at the bottom thanks a lot and we'll see you next week foreign

As found on YouTube

Free Coupon on Your Medicine

About Post Author

Happy

0 0 %

Sad

0 0 %

Excited

0 0 %

Sleepy

0 0 %

Angry

0 0 %

Surprise

0 0 %