Collaborate on Free Rent Receipt Template Excel for Security with Ease Using airSlate SignNow

Free rent receipt template excel for Security

working with Excel can be fun and exciting but without proper security things can go downhill quite fast hi this is Randy with Excel for Freelancers and your security problems are over today I introduce you to the user security and rights Excel added that's going to allow you to assign multiple security types to any worksheet on any workbook for any user in just a few clicks it's going to be an incredible training I'm going to share every step with you I cannot wait so let's get started all right thank you so much for joining me I've got a really fantastic training the user security and rights addin this addin is going to work with any workbook you want we're going to be able to assign user rights to any workbook any worksheet and of course we are going to have three different levels of security that you can assign to any worksheet just like that so it's a whole lot not only that this particular added we're going to be able to manage different users so we can select add and update and remove users as we wish and also we're going to have a log in and out feature that works with every workbook now this addin we can apply to anything it's going to be a great training if you do like these trainings I just ask a few small things go ahead and subscribe share the video with all of your friends and family I'm sure they will appreciate it also click on the like and the notification icon because that'll ensure that you get these trainings I respond to each and every comment every single morning with my morning coffee it is you and my coffee that is the best morning because I respond to every comment if you look down in the comments you'll see every single one unless it's within a few hours old has a reply by me personally that is what I do every day so I do want to hear from you I want to know your ideas your feedback your questions your comments or the issues that you faced or the videos that you might want to see whether it is comprehensive application development like I do every single Tuesday or perhaps it's VBA basic for beginners that I do every single weekend because this channel is for you my goal is not just to make you good with Excel but to make you successful with it so I don't want to just teach you the fundamentals I want to show you how to create success from using Excel and that's what we're here to do every single week if you do also like these trainings you want to support the channel some fantastic ways to do that the first of which is the extremely popular 350 workbook pack so I took every single massive template that I put together the last seven years and I put it into one incredible zip file along with a library so you can quickly locate any template you want and open up the video or open up the workbook or even the PDF codebook each particular workbook has a PDF codebook that's the 350 workbook pack I put the link down below that's a great way to support the channel along with our patreon now patreon members is well over 350 members now and each and every week I create an additional updated training along with an updated workbook and that's going to be based on your suggestions your ideas your feedback we call that the feature fix or Focus or I add additional features or I create fixes in case there's any issues or perhaps I focus on something that we didn't cover in the original training or something more in depth that's all happening on patreon so make sure you join us all right so let's get started on this training this training I'm going to show you everything you need to know about this addin it's called user security and rights it is an addin which means it works with every single workbook you open and we have three features of this we have the workbook security what we can do is we can select from the open workbooks we see we have two different workbooks open but if I open an additional one it's also going to be active so let's go ahead and pull up an additional one I've got this older Tree View here and I've got a user security addin here this is an old I've got a user rights now if you remember this correctly let's take a quick look at this for those of you who've been with me from the beginning seven years ago I created this user login and security however there have been some fundamental issues with this type of application there's been three ones that I wanted to focus on and that's a reason that I'm redoing this training and I'm putting it in an added so the first issue is one this applies to a single workbook so this workbook basically the user would select on an individual item and double click on it and then they could change it right so it was kind of cool but it only applied to this exact workbook and nothing else so that was a problem another issue was inside the code let's take a look at inside the code of this workbook there was some issues inside that so which was this form right here if we take a look at some of these fonts here so you see this strange D well it turns out that this character doesn't work with every single language so in essence what I would should have done is done something like character and then whatever the character is 254 or something like that or whatever it is I would put the character inside there so that's the proper way to do that so that was a fundamental ER and another issue was this particular sheets were based on a formula and it didn't always work especially when you were adding and removing sheets so I would never have done this again I would use VBA to populate this or update this or something like that so I wouldn't do that again so I used that but that seven years ago and I've learned a lot so now I've taken this training and I've put it into an addin so now we have all the features that we do we're going to use this tree viiew control so we have all of the users so each individual user here and then each individual user can have different rights on a per sheet basis so for example if we take a look at Fred here we select on the dashboard he has the dashboard which is locked but it's visible the chart data here is is completely unlocked while the admin here is locked and hidden so Fred feds unfortunately he does not have the intelligence to be able to look at the admin screen so we're going to hide that completely from him we don't want him looking at that so there's three different security levels that you could assign to any user so if I want to select John James and maybe there's a worksheet that I want so let's say I want to give him full capacity for orders so I would just select it and click unlock and then save and update that and now we see that John James if we see here he's got that green icon there and we see that he has the ability now to look at that and of course we can change that if we want to make it locked invisible we can update that and we see that John James now has orders with the yellow and the red icon locked means that is hidden and locked while the yellow is locked and visible and the green means unlocked so we have three different icons that represent those similar to the original trading this is all in an added and it's going to work with every open work book so let's show that to you now I was going to show it to you before but I wanted to give you the fundamentals of where this original training come from so if I take a look inside this treeview this older treeview training that we did which is one of the things and so now what we can do is we can apply that to that so I'm going to have that open up and we see that it'll open up the add-ins let's pull that up here's the security and rights so maybe I wanted to apply it so actually I've already added this to the workbook so we see that we've got the tree View and we've already got some data on this which is very very helpful but what I want to do is I want to pull up a brand new workbook and I want to show you how we can use this even in new workbook so I'm going to open up a new workbook now and then it's called book two so I'm going to save this and then we'll go ahead and do a save as and then we'll do an xlsm file and then we'll just do it on the desktop here so we'll do test so once it's saved we're now going to look in the workbook security and rights here and we see that we have three workbooks here and the test is one of them and we have all the users that we have we can also select here so either you can select a user here in the user list or we can select a user here we can select the worksheet now sheet one we only have one worksheet available and then we can assign the security so it's kind of simple we can do that and once we do that we assign it so Debbie now has sheet one that's visible we can change that relatively easy so basically whatever workbooks that we have open are going to be available inside this drop- down list now when it comes to users what users are available well if we take a look at it we have a user management and the user management is here and we can select on any user we can sign a password we can add a brand new user so we can do this test we can give it a password 2 three and we can give it an email if we want and we can save and update the user and so what that's going to do it's going to automatically save an update and so we see now we have test here that we just created so creating new users is very simple we can also delete the new users once the user gets created we can distribute that workbook and then the user can go ahead and log in or log out so we can test here and we do a 1 two 3 here and then we can log in with that too and it gives the user the ability to log in once they log in it will automatically be available for them to actually show and hide the worksheets ingly or assign the user rights great so we have log in and out we have user management and we have worksheet security and management all of that into a single added of course I'm going to walk you through every step if you just want to Simply use this addin of course I'm going to go over steps on how you can use it it and download it if you do want to download this addin the addin is absolutely free all you need to do is if you're on my website you look for a download button if you're inside YouTube you'll just look for the word download in the description and click the link and put your name and email and I'll make sure to get this sent over to you absolutely free once you do download it make sure that you rightclick on the file if you're new using the newer version of Windows you want to be able to right click and then click the unblock button so you'll see an unblock once you have enabled the macros and you want to it you'll just go to the developer now if you don't have the developer tab open you can rightclick any of the tabs and click customize the ribbon make sure the developer is selected here once it is visible you're going to go inside here to excel add-ins and then what you want to do is you want to browse for the addin that you just downloaded and you unblocked it so the screen will come up like this you'll browse for it and it'll look for the addin so it'll be inside your down downloads folder and it will be an addin so it looks something like this or whatever it is speaking of the Excel add tool pack you might want to check that out so you'll simply just click on it and click okay and what that's going to do is going to add it in and built it and it'll probably be selected if you have it before already you would just find it in this list and select it if you want to uninstall it you simply unselect it so you see here M user security and add and if I want to uninstall it I'll just unselect it and click okay and it's going to be uninstalled if I want to it again after I've had it I just go down here and I select it once it is installed of course you're going to want to then manage your users it'll come with some data but you can of course delete any of the users here that you want and of course you can add your own I do suggest leaving at least one admin if you can and also you'll be able to disperse this workbook to anybody else and once they log in they'll have the assumed rights so it's very very helpful so let's get into a little bit about this addin so once it's installed you'll have this custom toolbar with these three options the login log out where they simply enter their username if they try to enter something without any data we're going to say please make sure to enter a correct username so they will need to enter the correct username Fred and the password in this case it's 1 two three and log in once they log in it'll say Fred Fred has been logged in and it will disappear and then all of the Hidden sheets will be hidden or all of the visible sheets and all the Locking will happen inside the macro and then your workbook will be ready to go so let's take a quick look at this and how that might work let's say we don't want Fred to see vendors so we want to make sure that the vendors are going to be hidden from him so what we would do is we go into the worksheet security here and we take a look at Fred here and we see that there's nothing for vendors here so we're going to add it to that so we'll add here and then what we do is we select on the vendor worksheet and we want to make sure that it's locked and hidden we're going to update that very good so now when Fred gets his workbook assuming that he has it and we're going to log in as Fred so we're going to log in we're going to log out first and then what I'm going to do is I'm going to log back in as Fred and so we'll just type in his name Fred freder Fred's got to make his appearance as always one two three now you know my password and we're going to log in once we're logged in and everything's going to go okay we're going to see that the vendors is no longer visible you see it used to be right here vendors is not available anymore to Fred he cannot see it he cannot view it and that can be very helpful however let's say we're going to make a change we want to give Fred the visible property but we don't want him to be able to make any changes so we can make it locked and visible and we can save and update that now the next time Fred logs in so let's go ahead and log out of Fred and then what we're going to be doing is we're going to log back in and Fred frers and then we'll log him in once we do you're going to see that that vendor screen is now going to appear so here if we take a look at it we now see the vendors however if Fred tries to make any change at all we're going to get this the cell or chart that you're using is protected now assuming that you protect or unprotect individual cells I've got a training on that so whichever cells are unprotected Fred will be able to change whichever cells are protected Fred will not be able to change so that is the viewable but protected and then once again the other option is to view and unprotected which means that user could do anything they want to that screen so you've got three different options here of that so we see how simple it is simply just select the user and we can also update an email and I've got a lot of ideas for our patreon update on this so I'll be adding more functionality even that such as email functionality and a lot more so it's relatively simple in its purpose we just need to select it even if we don't have any information here we can simply select from the workbook and when we select from the workbook this list is going to update so this list of users and their user rights is based on the selected workbook so we see how in the test we just have a few users so there's no users with any so we can do that to Frank I think I cleared that out so sheet one locked invisible save and update but as soon as we make that save we see that Frank has the loock so that is going to be remembered on a per workbook basis so if we go back into an older workbook and then we have that but we go back into the test workbook we see that those changes have automatically been remembered and it's going to be expanded so that we understand what visibility Frank has on sheet one so it's relatively simple to use I would like to dive down a little bit deeper and see how I made this addin and if you want to customize it you can customize it as well and that's really important so I do want you to stick around because I'm going to show you how to change passwords I'm going to show you how to update security I'm going to show you how to customize this for yourself even if you are new to VBA I will make it easy to follow you will not need to reprogram this on your own unless you want to but I will show you how you can customize it and make it your own and you can fix any issues that might come up along the way and also how you can disperse this in real world applications and have other people use it also keep in mind that we do have the ability for other users to share and sync their macro enabled workbooks at the same time now I have an addin on that I did training a few months ago with an addin that allows users to share and sync all their macro enabled workbooks and all the data in a workbook so if we combine this addin with that one Adam we have a very powerful tool one to allow the security the other to share and everything would work really great together all right so what we're going to be doing is we're going to be getting into this added and taking a quick look and seeing exactly how I made it and what are the most important applications and how we are able to bring this to life in order to use it so first thing is we have this custom toolbar now I've got trainings specifically on a custom toolbar so if you want to learn how to create a custom toolbar I've got that for you but basically we're using the office ribbon X editor this is a software and in this software I've created a custom tab that custom tab is called user security and rights you see there's a custom tab here inside this custom tab I have a group called security and right so you see these three buttons are inside a group that group is called security and right so that's the group right here inside that group I have three different buttons as you can see I have login user management and worksheet security and I've got some other unnecessary things workbooks open so inside that I've also uploaded three different icons sheet security user login and user security now this is called office ribbon X editor remember I have a training just on that this is a free software it was done by Fernando what was this guy's name Fernando Andrew and it can be found on GitHub so it's free download and so the idea is that we have these three individual buttons each button has a unique ID each button has a label an image now I created these three images and I was able to insert if I want to insert more icons I can just insert that and I just go to the folder that we're working on here and I would just insert some icons so that's exactly what I've done I'm using three different icons the sheet security the user login and the user security those names are called image so user login it matches that the image user security and the sheet security so we have three different names that are tied to the icons here and then of course we have the size which is large for each of them and then we have a macro that's going to run we want something to happen every time user clicks so when a user clicks login log out I'm going to look for a macro called user login log out when we add or edit users there's a macro called add edit users and there's another macro called worksheet security I'll be sharing those three macros with you so that is essentially it three buttons three unique icons three macros that run it and it's in its own unique group and its own new tab so that's all that that is and I've also got uh two beginners training that covers this and a more advanced one on my channel so just look for toolbar or custom toolbar and I've got that all right so here's what we're going to do we're going to jump inside the developer we can take a quick look at this now this is the ultimate Erp application that we just happened to be viewing that of course is in another training where I created this incredible ultimate Erp application so if you're interested in that and how I created this that is also on my YouTube all right so once inside the developer we're going to drop in here and we've got a few different workbooks open you can also use alt f11 to get in there and I've got my Excel AI tool pack that is a very very cool I'm considering adding another feature on here too so make sure you grab that just a few dollars a month and a great great tool for you so here's what we're going to do inside that you see I've got several op let's close them so we can have a look at what I've got open and we have few different projects users and security this is the addin that we're going to be going over this is the ultimate Erp application is the treeview the test and then my tool pack so we're really going to be focused most on this one right here user security added I've got three different sheets as part of this added and added is just like a regular workbook however the sheets are hidden we don't necessarily see the sheets until we make a few changes inside this workbook I have three different sheets users workbooks and workbook security I've also got three different user forms which you saw briefly the user login log out and these buttons these are two buttons on top of each other that's why they look like this but that's the way I want it only one button is shown at the same time I've got a button for login and a button for log out but I only going to show one at a time so it's okay that they're on top of each other we've got the security form which is going to be our main security form which is where the user will select the workbooks where we will have the tree view of user security and rights and we've got some hidden Fields here which I'll be going over a little bit we've got a selected user and the work sheet and the security level user can save or update cancel and they can add a new user and and the last is the user form where we have a list of users we select user the username and the password and the email we have the save and update we can add a new user we can delete a user and we can cancel out of the user form inside this we have three different modules we have one module that's going to focus on user login log out so that's its entire focus is user login we have another one that's going to be focus on updating existing users or adding new users and then the last module we're going to focus on the worksheet security mags how do we assign security levels to different worksheets on different workbooks so it's all going to be handled inside this module so what I'm going to do now is as I mentioned here you can't really see the sheets they're not here so where are they well they're inside that hidden workbook so we're going to unhide that workbook when we go into this add- in here we're going to go into this workbook now keep in mind while it's an addin form I can save it no problem I can make any changes that I want and I can then save those changes but when we change it back to a regular workbook at least temporarily we won't be able to save it as an addin when I create these addin I work as normal workbooks only at the very end do I then convert it to an addin so I work as an xlsm file just a regular macro enabled file except as soon as I'm ready to launch it I convert it to an xcl AM file so what we're going to be doing is we're going to click on this workbook here then what we want to do is we want to go into the properties of that actually just just this workbook to get to the properties we're going to go here or F4 is a great shortcut we're going to look into the properties they might be in different locations on your screen and we're going to go to something called is added so that's what we're going to focus and I'm going to change that to false now as soon as I do that three different worksheets are going to appear now keep that in mind we can't save it in its current form notice it's an xlam file so I just told it this is not an addin so if we take a look inside here one more time we go into the workbook is add in now now it's showing false oops I double clicked it now it's showing false right so let's go back in there because I double clicked it so what I want to show you is this workbook here we see the is added I want to make sure it stays false so when it's false and we try to save the application it's going to give us saying hey this extension cannot be used with the selected file type basically we're telling it in two different instances this file type is an xlam file however this is showing so that's conflicting this is saying it's not an ADD file the extension xlam is saying it is so because of that conflict it's giving us a warning but that's no problem for viewing purposes I'm simply going to keep this as not an addin if I want to save the changes I will change is addin back to True before saving those changes very good so we saw inside that but now let's take a look because I have changed this to as addin equals false I can now view the three different sheets that are part of this added so the first is the workbooks every workbook that we decided we want to add some user security has to get saved whoops let's get out of that screen we want to make sure that we assigned a unique workbook ID I want to know the name of it and I want to know the path of it the path might not be too important but it's there so those are the three information that I want to know and it's most important is that workbook ID that way we can refer to that meaning if I decide this workbook I want to have some security to it so automatically we get that so let me me show you what that would look like here so we add that new workbook next up we want to have a list of users obviously I know that I need to have a list of users so we've got a list of users now keep in mind that we could easily mask these passwords but when you protect your VBA code for example if you were to go inside this addin right here rightclick here and you want to go to the project properties when you assign protection and you add a password to it nobody will be able to look inside nobody will be able to convert it and unless they hack it but most people would not do that and so it's better to be able to have that security there so that nobody can actually look in and see those passwords so that's a much more secure environment so we have the passwords which we can do more to hide but they're here the email and the row so this is essentially our user list I've got icons that are going to need to be used inside our user form so those icons are here and I've got the three different this should not say username this is our security types so we'll just put sec security types and I'll save this right after I convert it back so we have three different security types notice these security levels here are in a named range called security levels when I have a drop- down list of security levels we need to know which of those security levels so I've got them inside a drop-down list right here but keep that in mind we're going to need that next up and lastly is the worksheet security so each individual worksheet has some security level so I've got a workbook ID I need to know what workbook it's been assigned to the name of that workbook which can be helpful I also need to know the sheet name and the sheet code name could be helpful in case it's different the reason we're keeping the sheet code name is because theoretically users could change the sheet name so we do want to have a code name I also need to know the user ID and the username and I need to know the security level so that means I know inside the tree view updated for the sheet named vendors the admin has a locked in visible so Fred for example has locked invisible the sheet vendors remember we locked it and made it visible so we're keeping track of that here inside that and then we have a database row that's assigned to it once we have all that information it's very helpful if I want to display only the controls for a specific workbook what I would do is I'd run it through an advanced filter meaning I only want to see workbook one so I would just put in a criteria here let's say workbook 3 then we run an advance the results of that so we know that inside workbook 3 we only have one row so for example if I choose that test here here we see that we have just that one item sheet one so we see here sheet one sheet one sheet one user ID 3 that's Fred's ID we see the username is Fred the locked in the visible and we see that it's row 29 now if I decide to change this to unlocked and I save and update that we see now that it's unlocked so we see how the database changes and the results now if I load a different workbook with more data and we also see the results here so this here is workbook ID 1 this is workbook ID 2 I believe so we can see that all these different security rights are based on the results here so that's how we can filter based on a workbook ID so that we only know those sheets and security levels on a per workbook basis so it's relatively simple as far as the data is concern we just want to store the workbooks that have the user security we want a list of users and we want to understand exactly what security is being applied to what worksheets by which users but now how do we get that data into the workbooks and how do we get it inside the added well that's all going to happen with those user forms so it's the user forms that regardless of what workbook is open is going to handle all the loading of the information and all of the added information now the first thing that's going to happen is when we launch the user form nothing's really going to happen until we determine what workbook now what I've done is I've decided to display the default workbook unless it's the addin for example if I have in this treeview or let's say we have this ultimate Erp and I decide I want to launch it let's go ahead and make this back and addin again so that we can get this to work just temporarily so notice that we do not have the option here anymore however it is only available on our user security and that is because we have turned off the addin feature so if I were to load it in it would automatically default to the workbook so in other words if I've got the ultimate Erp it would automatically load it in we're going to keep it like that for now now what we're going to be doing is we want to be able to update and change so when I select let's say I select admin admin's a user or David Daniel we want that user to default to the selected user but when we select a specific worksheet such as orders we want the worksheet and we want the security level now keep this in mind because I have these worksheets open because I have the addin open let's take a look at this inside the workbook here is ADD and equals false if I try to save this either manually or with the code again we're going to get a bug so if I try to save this we certainly could have a bug that's going to come up if we want to be able to add that into an additional workbook so I'll go over step by step with you what we want to do is when we make a change to whatever workbook we want all of that information to load inside this what's what we call a tree View and I want that tree view expanded now I've got a training specifically on a treeview in fact it was this training right here in which we actually created the tree view so it was I believe in the order items or orders and it was very very cool so we were able to create so if you're looking for more of a detailed list on that triv I believe it was here so we created this triv which was quite cool so here's an individual training where we have purchases we're able to display that so it's a tree view component and once again look for Tree View inside my YouTube channel or on my website and you can find the detailed instructions about how we do that but tree views are really great because we can show icons we can show a breakdown of information for example in this type of treeview we have our customers we have our individual work orders purchase orders and invoices if I select on purchase orders I can select on individual purchases and have those display so all we have inside our add-on is a simpler version of the treeview where we have the user we've already got the workbook here and then we have the individual worksheets and the security assigned to those users if we add more worksheets they're going to get added to that so for example if I decide I want to click here and I want to add another worksheet I've already got vendors customers and chart data but maybe I want orders and I want locked invisible and I can do just that so we can simply very easily add a certain type of security level to any sheet or of course we can change it as well and it'll be safe so how do we get this tree viiew to display the details and once again it's going to come through this Advanced filter I need to know the workbook ID I need to have all the results and then what we're going to is we're going to Loop through these results and we are going to get those results come into our tree view component if we take a look inside our VBA let's shrink this we got a lot open here and we want to shrink this one if we want to look inside we're going to focus on this security we're looking at this tree view component there's a name for this and it's called Tree View so we want to populate that tree view with all the data from here so keep in mind that we do have some pictures now if we want to get pictures in inside that it's a few different steps the first step is to put those pictures somewhere in other words I don't want those pictures on a file path on my computer because if I give you this file I want you to have those pictures too so to do that I want to make sure that those pictures are somewhere inside the workbook so I've done here now I've inserted these pictures here now how did I do that well if I go into the developer and I go into insert here and I choose an ActiveX such as an image ActiveX that's the one I'm looking for that's the easiest one to work with so I've selected Here and Now what I would do is I would go into the properties here once we've selected that now make sure the design mode is on if the design mode is off I can't do anything with this once the design mode is on I can select it then I can go to properties once inside the properties and I'll bring that over here I can then browse for an image I'm going to select on the picture here and I'm going to look this little browse icon then what I'm going to be able to do is I'm going to browse for that now I've got some different formats here I've decided to use the Ico so I I tried PNG jpg but I like the I type I'm going to give you all these in N of course you're a patreon member you'll get all these icons as well but I use the I I like that one much better it was more clear so I use that and so once we do that let's go back out of here so we see that we have it but it's not really set right what we can do is we can change the size mode we can use either stretched or we can use zoom so either one's going to work so we can stretch or Zoom it now if we stretch it how like this we might get some gaps in there but if we stretch it the aspect ratio may not look good so what I do suggest is I suggest you use the zoom feature and then what you want to do is you want to shrink it just about so that everything is just right just the way you like it like right about that so that's pretty good the way that we have it and the Size Doesn't Matter so you didn't hear that from me so what we're going to be doing is we want to make sure that we give it a name that we can remember so if we take a look here we see locked and hidden we see this one's called locked visible and we see this one's called unlocked so I've given them names to give it a name all you would do is just name here we just type it in right there and now it's been assigned so we select it we see that name again these names are important because we'll need to recognize them in VBA once again this is called locked hidden this is called locked visible and this is called unlocked we will be setting the size inside VBA and we can change that a little bit but keep them standard and about like that once we have them what we want to do is we want to load them inside our user forms but it's kind of like a multistep I can't actually just load them directly in here I need a middleman like a holder like let's say a carrying book or maybe you might want to think of it as a picture book and that's a component right here if we take a look at this component here and we go into the properties we see that that's called an image list now an image list is something that may or may not be available if I select on my tool box here here's our toolbox what we're looking for is something called an image list so if we take a look we've got list view it's this one right here if you don't have that available you can rightclick here go to additional controls and what you want to do is you want to look for something called Microsoft image list Microsoft image list control and then select that once you've selected it it will become available here inside this and the image list is simply a holder it's not visible so when you bring it in here again we can size it any way we want to it's not going to be visible when we run the form you're not going to see it see it's not here but it is a container that holds those pictures so the reason we want to load them inside there and hold that container let's bring that back up here is I want to whoops here security form I want to be able to put pi pictures in here once they're inside here I can then load them in here so to do that I've given this one here this one's called image list so here's called image list this one I can delete so to do that when we load the user form up I'm going to take those three pictures and I'm going to load them directly into this like such as a folder or this carrier that's going to hold those pictures once they're available in here they can become available in here so as I mentioned that's going to happen when we load the user form so when we load a user form that's called an initialize event so what I want to do is I want to look at the code behind this user form so we can right click and go to view code we're going to look for a very specific event and to do that we're going to click in here and we're looking for the user form so the first thing we're going to do is select on the user form and then it's already here but we want to look for initialize so the first thing is when I initialize I want to make sure that I get those images inside that image list so we're going to do mention the my image list as a new image list so that's very important to get those images and the workbook as a workbook might as well go in order I want to add each of the open workbooks to that list if you remember correctly when we load this look it's got three different workbooks that are already loaded inside this combo box that combo box has a very specific name so if we go into the security we see that the name is called the selected workbook so what I want to is I want to determine all the open workbooks and I want to build this list based on all the open workbooks the only thing I want to exclude is the addin so I want to make sure that the addin or perhaps any addin is not in this list and so to do that we're going to Loop through all of the open workbooks and that's going to happen on that initialized event so we're going to go back and view that code and here's that initialized event so we're going to say add open workbooks to the list except the added so remember we have a workbook as a workbook dimensioned as a workbook so for every workbook in all of the workbooks that are open we're going to look if the name contains user security if it doesn't contain meaning equals zero meaning we're going to look inside the workbook name and I'm going to look for user security if it's not found then this will be zero that's the instring command so we're using the instring command we're looking inside the name of that workbook and we're going to look for user security as long as that's zero meaning not found then what we're going to be doing is we're going to take that workbook name and we're going to add it to the item of the selected workbook now the selected workbook is that workbook item so we're going to add that into that so once again I want to show you this one it's called the selected workbook we see it right here so we're simply adding those individual workbooks to that list so that they're available to us that we can select on them and load the workbook so this simply loads the workbooks into that combo box now we're going to focus on this is all for initializing the images into that so we need to work with them first thing is we want to set a width and a height so we're setting individual sizes for our image so my image list that's the one right here we're setting some default sizes now what we're going to do is we're going to take those pictures on this sheet this worksheet right here these three pictures we're looking at the user sheet that's where they're located we're going to look at them by name we see inside the developer once we click the design mode we can see those names locked and hidden locked invisible and unlocked so we can see those names and they're coming from the user sheet we need to put those into our image list so focusing on the image list all the image lists we're going to Dimension the image number as long we need to increment that to make sure the image number equals one we're going to add this image to our image list the key is unlocked I want to give it a unique name and the picture is going to be on from the user sheets unlocked picture so if I click on users dot we see unlocked unlocked is the name of the picture remember we added this one too so remember we created this one let's see what was the name we used we called name here so that means that's going to be available to us so when I click users. name here you see it's available once we add it it becomes available and then pictures that means that's the picture that's focused on that so I'm going to take that picture and I'm going to put it directly inside our image list so that is available so we're doing that with three different images unlocked locked visible we want to make sure that the names are exactly like we have added and we're good to go so we've added all those next up we want to initialize so basically we're taking the images and we're putting them inside this little holder here we're putting them into this one right here this image list think of them they're inside here they're holding it that's where images are contained but next what I want to associate this tree node with these images so that means when I need to display them here they're already available so what we want to do is we want to kind of Link these two together and we want to say hey anytime that we need this type of picture look at unlocked or anytime we need this type of picture so it knows what we don't want to do is every time a picture is needed we don't want to go and look for it we want it to be available so we kind of have to link these two things together so that they pictures are quickly recognized and to do that it is relatively simple we're going to focus on that tree View and we're going to set the image list equal to my image list once again here so the image list inside the tree view is going to be connected to them so here how we're connecting the two together right here treeview has its own image list this one has its own image list so now we're linking the two I'm just going to put it there link two image lists together so that's exactly what we're doing we're giving it an indentation so this is how far it's going to be to the right that we want we're giving it a label we're just going to call it tup manual so it's going to be manually editing the labels and hide selection is false CU we want to do that we don't want it hidden we want it visible so that's all we need to do so this is how we set the image widths we bring them into our holder and then we connect the two together the next up is simply the user list so we want to connect the user list I've got a user list that needs to be linked up what do I mean by user list that's linked up if we take a look in our formulas and we have a name manager and I've got something called user list so if we tab over we see that it's all the alphabetized user names every time we add or we update a user this list gets rebuilt and real alphabetized notice it's different because this could be in any order here on the left side but this is our sorted and organized list because I want kind of an alphabetized list of users which makes it easier to locate so to do that we created a macro and then I've got a named range an offset that means as we add add users this is going to grow so we see the dancing ants around this so that means what I want to do is I want to connect this it's called user list I want to make sure that that user list is connected to this drop- down right here this is called selected user so that when we run it automatically I want to make sure let's select on the form so that when we run it I want to make sure that our alphabetized list of users is populated in that now to do that we want to make sure that when we're working with an addin it's very important to make sure that the named range of that list called user list not only do we not want to add the user list I want to also associate the addin name with it so the full name of that is workbook name and the user list so together that ensures the row Source now what do I mean by the row Source well if we take a look inside that object and we want to connect the list to this that is called the row source so here we look down here and we see the rose Source now it's empty here and that is okay it can be empty and the reason we want it empty is because we want it real time we want to update that so there's no data however if I were to just put this to user list here then it would update as well now why didn't I do that if we see here we got the user list here I want to make sure that it's always up to date and so by using VBA we can ensure the most current user list and it's just one line of code so Emy is the form name the selected user is the combo box the row source is the specific property and we're going to tie this named range to that now notice this is kind of hard to see what this is is a quotation marks and then it is an apostrophe and then the quotation marks so we really need that then it's the workbook name then you can't see it here I'm going to add some spaces here then it's the quotation marks then it is the apostrophe the exclamation mark and then the list name so all that needs to be put together just like that so we can assign that we're going to do the exact same thing with the security levels if you remember correctly I did create a named range called security levels right here we're going to do exactly the same thing that we did and add that named range once again because the user is going to have many workbooks open I want to make sure that when we associate a named range I want to be very specific with the book this workbook means the addin this workbook is always the addin it means the workbook of the code the active workbook could be whatever workbook the user is using or working in so if I want to focus on that specific workbook the one that the user is on I'll use active workbook if I want to notate the workbook where the code resides I'm going to use this workbook so keep that in mind the differentiation is very important on that great so the last thing is we want to make sure if the active work workbook name does not equal this workbook name then what does that mean this workbook is the added name the active workbooks whatever workbook is using in and that means if the user is not currently in the active workbook then what I want to do is I want to locate that selected workbook name I'll show you what this means better than so here what we're going to say is if I run this we see that there's no worksheet because I'm currently inside the added if I were to run that directly inside that it would let let me show you what I mean by that so what I'm going to do is I'm just going to go back in here I'm going to go to the properties and what I'm going to do is I'm going to set the is addin back to true just for a moment now we have that availability so now when I select it notice how this is automatically populated because the current workbook active workbook is not the addin file so I can automatically populate this when we launch the user form so if it's inside let me show you that one more time going to shift to this workbook right here I'm going to go back inside our addin I'm going to go to this workbook here I'm going to go to is addin we're going to go back to false here now because they're the same workbook we don't want to populate this unless it's a different workbook so that's basically the idea so great but however when I do select it we will populate that now notice that when I made a change to there this automatically pop populated so that's going to happen on a macro that means when there's some kind of a change to this combo box we want something to happen so let's go ahead and take a look at that action and so we're going to dive back into that user form here inside this one and we're going to focus on the change event or when something happens to this particular selected workbook we can doubleclick it and what that's going to do it's going to get us right here to the selected workbook and of course the change event and that's exactly what we want to focus on now inside that we're going to have that change of event so here's everything that's going to happen as soon as the workbook changes so when it does we're going to Dimension the workbook as a workbook and the worksheet variable as a worksheet if for some reason this value the selected workbook if the value inside that combo box is empty there's nothing we can do so we want to make sure that it has a value first thing what we want to do is there a hidden workbook ID I do want to clear that as well and that means for every single workbook as we saw earlier inside our workbooks we have an Associated ID so I want to make sure that we populate a field with that ID so let's take a look back in here and we see that we have this hidden field so that means on any change of here I want to populate all of the treeview and this hidden value called workbook ID it is hidden if we take a look at the visible property here we see that it's false so we see that if we make it true it's going to show up and I don't really want the users to see this I just wanted to hold some data and I want it to hold the ID of the workbook so when we make any change to this I want to clear this field right here called workbook ID and so we do that directly inside the code let's pull that up here and the code is right inside here view code so we want to clear the workbook ID if the selected workbook value goes empty we're going to exit the sub meaning if there's no workbook at all there's nothing we can do I also want to clear the associated worksheet we have the worksheet field that's the selected worksheet we want to clear that as well and so that workbook that the user has chosen is going to be our workbook we're going to set that workbook so we can work with this object variable we're setting it to workbooks the selected workbook so this is our selected workbook once we have it inside a variable we can then work with it we want to make sure that it is something if it is nothing we can exit the sub obviously now what I would like to do is I would like to get every sheet in that workbook and remember we want to populate that drop-down list we've already cleared the worksheets from the dropdown list now we need to populate these worksheets with every single worksheet name inside that workbook so we're going to run a for each Loop for every single worksheet inside that workbook we are going to add an item to the selected worksheet just to review let's pull this up we don't need to see all that just to review here this is the drop- down list that I'm going to be populating right here that's the drop- down list called selected worksheet the combo box that I want to populate with all the worksheets in this selected workbook so we're looking at the workbook then we're going to build out the drop- down list based on all of the worksheets and we can do this through this little Loop right here here so we're going to add worksheets to a combo box so once we do that we also want to grab the unique ID so the unique ID is going to be where it's going to be inside a custom document property when we create these workbooks I'm creating a custom document property I want to have something unique about every single workbook and in Excel there's something called a custom document property if we take a look look back inside this workbook here and we go into here and we go to the information we can go into the properties right here we're going to look in that and then Advanced properties once we do that it's going to open up this tab here we want to go then to something called custom now we can create as many custom as we want we can do something like test custom and then we can create a test value so when we add it we can do that so we can create a unique IDE or pretty much anything we want on a Pur per workbook basis and the beauty of it is it ties this workbook to this unique ID so that when the user has this we know exactly what workbook we're focusing on and that's going to be very important so we can tie it to the individual workbooks and then there's another way we can conver it through a hidden sheet so both are going to be very helpful this tells us that this workbook has already been added to our list so it ties the individual workbook itself along with the database that we have directly inside so it ties this workbook ID inside our addin with the actual workbook very good so once we do that we want to tie that you're getting that and we're going to put that inside the workbook ID and we're going to run a macro called treeview refresh now this is the macro that I want to go over with you some of the others of macros are very similar to what we've done but what I want to share with you is how do we build out that tree view so that's really cool and really interesting so let's take a look inside the worksheet security Mac and we're going to build out this tree view so we're going to look here for Tree View refresh what this is basically going to do it's going to take all of our security so we're going to put that workbook ID I want to know all the sheets that have been assigned to users and given security for the given and then what I would like to do is I'd like to Loop through all of these sheets and then would like to add them but what we want to do is we want to add them to individual users right so what I would first first like to do is inside a treeview I'd like to have a list of users then for every individual user if there's any worksheets that have been assigned to that user with certain security rights I would like to have that ability here's what I mean by that the first thing what I want to do is I want to create a list of users so we see this we've got a list of users now if we were to shrink everything all you would see is that list of users some of them here have no worksheets assigned to them some of them do so I want to get a unique list of users and I want to populate this list once I have that then I want to look to see if there's any sheets that have been assigned if there are sheets I then want to assign them to the users so it's going to be in two steps the first thing is to populate this treeview with a list of unique users the next is to associate those individual users with worksheets so what we're going to do is give each user a unique ID so we have a list of unique users we've got a user ID so let's do that let's create that unique list of users and then the second step will be to tie those any individual users using the ID and then adding the individual sheets to those users and then at the individual security levels so let's go through that macro now it's called treeview refresh so inside this macro what we want to do is we want to Dimension some variables as we're going to need to run through the users so we need to user row we need the last user row and we need the last worksheet we're going to be going through the worksheet and so we need a bunch of variables we also need the user ID as a string I need to know the security level and the sheet name as string so we're going to focus on the security form here Tree View Notes so what we want to do is inside the security form inside our tree view remember that's that form the tree view is once again just to relate here view the object it is this one right here we can see the properties and we can see that it is called Tree View so we're focused on that component right there so in inside that component we are going to clear the tree View using nodes nodes are the individual items of a tree view they're called nodes remember I've got a unique training just on the treeview first of all I want to refresh users I want to make sure that we have a brand new list of users all that maer does is create this unique list of users and then it alphabetized them so that's all we're doing with this once again we want an alphabetized list so I'm going to be working with this list here next up inside the users I want to determine the last user row we're to use column G CU I want to know the last row in this case it's 13 so if for some reason it's less than four we're going to exit the sub actually I should use less than three let me change that to less than three we're going to exit the sub if it does doesn't have any users next up we're going to create a loop the user row is going to be three to the last user row I want to add each individual user and I want to give them a unique name now what we're going to do is we're going to create something called a node to that so Tree View nodes we want to create a top level node and we need to add a key now a key for everything is a unique identifier no two nodes or no two items within your treeview can have the same key so first of all what is unique we know the user ID is unique so if I combine the user ID with text I know it's going to be a unique ID so I can do that so our key for the first one is going to be the user plus the user ID now the user ID is coming directly from column G as we can see right here once we have that we need to give it some text so we have this unique ID but that's kind of hidden nobody sees that but the text part we do see and that's basically the name and that's going to come from column H so that's all we have to do there this four lines of code or five rows will build that first level that top level Tree View so we're good with that relatively easy so if we were to stop it here just so we can see what that mic looked like although I think you can imagine we do the worksheet security and we select a workbook here and all it's going to do it's going to stop right there which is what I want and all we're going to do is build that top level so that's all we have done up until this point once we have that and we can continue that code here and then it's the next part of the code that adds in the remaining sheets so let's take a quick look at that and see what that is first of all I want to create basically an advanced filter based on the workbook ID and I'm going to put that workbook ID here then what I'm going to do is I'm going to run an advanced filter based on all the original data of the worksheet so that's going to come all all here and only the workbook criteria here not the user ID just the workbook criteria just this criteria L2 through L3 so I need to place that workbook ID right here where is it coming from that workbook ID is going to come directly from that hidden field called workbook ID that's that hidden field we saw going to pu