Alrighty thank you so much for joining me today and if this is your first episode welcome make sure you subscribe in it the icon for notifications so you can get automatically notifications what I do release new videos each and every week this is part 21 and our final part of the employee manager it's been a marathon series we've covered so much and we'll go through a brief summary if time allows and today we're gonna be creating an employee manager custom ribbon all with custom icons and you can use this for any type of Excel project.
So we're gonna show you just how we did that in today's training so it's a lot to show you might get to a summary as well so we'll try to do that so basically in this custom ribbon here I've just created the ability for users to quickly and easily get to different parts of the application simply by clicking the icon above and it's pretty simple relatively as far as getting there and we're going to show you how to customize that what software to use and of course I will include all the software all of the icons of course the.
Application as a free download for you so make sure you check the description below this video for either what it's gonna be Facebook or email links so that you can get your free application free software to create these icons as well I'll include the icons in case you want them separately so you may want to customize it just as yourself I've created these icons using a free online design tool called pixel art and I've downloaded some through various channels and through Google and then I've customized them so this I use Pixlr it's.
Just a great way to customize our icons our the same size they are all 200 by 200 pixels and that's important I think the maximum is 256 by 256 but you won't want to make sure they're all exactly the same size that's helpful and you want to make sure they're all big enough so that they're clear enough so that's important so you can get your icons looking and acting the same so I've created them in pixel art and I'll include a link of course in the application in case you want to check that out it's free it's a really great tool I use it a lot for a.
Lot of my design aspects so I wanted to show that to you as well alright so what we've done here is I've basically created different groups and in each group I have icons so I have a total of five groups we have users where there's three icons employees where there's four icons whether it's manage employees or employee ID card or whether we have attachments employee archive time clock where we've got the actual time clock we've got a time clock history that's in our third group called time clock and we.
Specific and only go on sheets you know it's another way to hide and show sheets and guide your users to help them so they don't make any mistakes or remove or delete any data that you want this employee to have we've located it after the view but you could put yours anywhere I'm going to show you how to put it anywhere whether it's before the home or whether after the developers tab or anywhere you want and so to start off with that we just created a bunch of very simple macros and if you want to create your own toolbar and you don't care so unnecessarily about the icons you want to use some basic icons there's actually an easy way to do that one.
We're going to go over that first just so you can show you how to create a basic toolbar a basic custom toolbar without the custom icons so we would do that into the file menu and go into options and then of course once you here and you see the customize the ribbon here now we have the ability to add a brand new tab we can click a new tab and we have a brand new tab and then we can right click and rename this we can call this custom tab 1 whatever we like so we do have the ability once we have this we can also create groups again we can then rename this we can call this group Group.
1 and then we can does we can choose from some of these kind of basic icons of course I didn't really want this but you could if you're just looking for something very basic and very quick you can so if we were to choose this into Group 1 we could choose an icon for that and we can also add a macron now if we're getting out of macro we have we can see we have got a lot of macros here these are all of our macros so for example if you want someone to log out you could just add this there and then we'd have a macro for log out again you can right-click this you can rename it.
And add a specific so if we want to rename it we want to add a specific icon from these icons we could we could just add a icon like this and click OK and then we click OK and now you see we have a brand new custom tab and we have the ability to log out that is an easy way to do that but I want to customize the icons in this case if you just want a basic way to do it we can do it that way but I'm going to show you a way to get a little bit more streamlined UI look and.
A nice custom look so that you can have all of your customized icons in something like this so where everything looks really nice and professional alright let's go ahead and remove this if you want to remove it just back into the options into the customize ribbon and here's our custom tab that we created we can just right-click this and then remove it and then it's gone ok so that's how we create a very basic toolbar but we're going to go beyond that where we have custom icons and I have those custom icons I've created.
Them all already as you saw and I've located here in a folder called icons and I'm going to include these for you
Separately so you can play with it as well so I'll make sure to include this today this week's downloads gonna be in a zip file and I'm gonna include a few other things I'm also gonna include something called the custom UI setup this is a custom software that I'm include when you run this you may need an additional software so Microsoft may ask you to download dotnet 3.5 I believe so you may have to download that as well but this is called office custom UI.Editor set up and run with anything from 2007 on up to 2016 or 18 or 19 it should work with all those although I have not independently tested on every single version I'm currently using 2010 and worst grade for this version although it should work all the way at least all the way up to 2016 and when you run this program basically it's a very basic set up it'll once it's run it'll and then it's gonna open an application that looks something like this so once it's installed it's very basic and let's go ahead and close this.
One this is our 21 now I have another one without it let's go ahead and we don't need to save that I'm gonna open up another one which is the same but a dozen it's called ribbon and it doesn't have the employee manager custom ribbon on it so there's no custom ribbon on this that's the one we're gonna build our custom ribbon on so there's nothing here okay so this is called ribbon that's the one I'm gonna be working on with you so that we can create that so let's go ahead and close this now you want to make sure that when you're working on the custom ribbon when you're working creating it we're gonna use the separate software to.
Create that then when we do that we need to make sure that our application is close to let's let's close that and we're gonna open up this custom UI editor that's the software I'm gonna include just run that and it's gonna open up and can be blank like this and now what I want to do is I want to open up the excel file that we working on and for our purposes it's called employee manager ribbon ok yours is gonna be here I'll include yours but this one is without the ribbon this one is with it so we're gonna focus on the one without it let's take a look at the one with it.
Just so we know what we're going to be building just so you know what is the goal so you can see it once it's completely added in its gonna look something like this let's go ahead and open that up I'm gonna trick this a little bit and so basically it's just a little bit of code and we have groups we have buttons and so on and so forth so that's what it's gonna look like when we're complete we also have all of our icons here ok so let's close that we don't we're not gonna work on it and let's go ahead open up a new one.
And we're gonna work up we're gonna focus on the one without the ribbon so we're gonna open up the one when we make sure that our Excel application is closed we want that closed so open up that and we're just going to see a blank workbook when we open that up all right now that we've loaded this in you see there's no code below that so we want to make sure to add in our code and the best thing to do is insert a 2007 custom UI part that's the one we want insert 2007 from here insert and then.
What I want to do is I want to insert some sample UI sample let's go ahead and enter a custom tabs sample your custom tab and now I want to change this let's change this to 2006 and then 0 1 and we can check to make sure that that's going to work and it's correct by clicking this validate and it says custom UI as well form so change this to 2 0 0 6 0 1 that'll cover all the versions and keep it from errors and then next up we don't necessarily need the start from fall so.
We can remove that and we're just going to have ribbon ribbon basically what I want to do is I want to create different groups and in each of those groups I want different IDs and here in our other version we can see that we have different groups we have a user's group with 3 icons we have an employee's group with 4 icons a timeclock group with two icons and we have a scheduling events with three icons and a payroll reports now each of these we are going to assign.
A Mac or a to each of these and those backers are located here I've created brand new module called ribbon control macros ribbon control macros here and inside this is a selection of macros that I've created although these macros are specific to control as an i ribbon control those are specific for the ribbon controls within each of those macros are some very basic additional lines of code like for example employees would be sheet 1 activate and then of.
Course hide ID card in case the ID cards are showing or again if we're going to show the ID cards we'd also want to
Activate G 1 which is our employee manager sheet and then show the ID card so run the macro most of them are just very simple the employer archive would be just activate sheet 6 while the employee attachment sheet 3 and so on and so forth and of course log in we're gonna run a macro called start workbook log out we're gonna just run a macro called.Log out activity and the only other one is week view now in scheduling there's really two different macros are gonna run whether it's week view or month you for example in our scheduler in our scheduler here we have week view and we have month view so I want to be able to user to be able to select either month you or week view with the icon so to do that all we need to do is add an extra line to that code so she date activate which is our scheduling sheet and then run the week view macros and for the month view sheet 8 activate and the run.
The month view so they're very very simple and it is these macros that we're going to be running these macros the first one we're gonna focus on log in so the first group that I want to create in the first macro is log in so we're gonna focus on the users create a group called users and then within that group create three different buttons login logout and admin all right so let's go back into our custom UI editor now and here we have insert our sample code here but we.
Don't have any icons so let's insert some icons by clicking on the picture now I've got all of our icons saved in this icons folder here which you're going to get and I'll make sure it include so ctrl a we're gonna select all these icons and insert all of them into our custom XML now they've been inserted if we click on this + and expand this we can see that all of our icons have been entered they have already included now all we have to do is make sure that they're in here alright so let's continue on and we have the ability to.
Place our specific tab at a specific location so we have our tab our tab ID here we're gonna we can keep this custom tab then I want to give it a label what do I want to call this label employee manager that is the label for the entire that is the label that is right here on top right here employee manager that's the label I want to give it and of course we want to make sure that we are not open this is a different version this is 21 we are focused on 21 - ribbon.
So we have make sure your excel is not open when you're working on it we are focused on 21 ribbon those are two different version one contains the ribbon one is the one we're working on so the rib is the one that we're working on all right so we've got our label now now where do we want to place this we can also place it so we can tab over called insert after M s oh where do we want to place it equals let's put it after the view so I want to put it after tab tab and then view so I want to place.
This right after the view menu that's what it's gonna do I want to place it right there you can place it anywhere you want just make sure so for example if you wanted to place it after the review you put it in tab review or tab data tab formulas tab paints layout and so forth so we're gonna I want to place it right after the view like in our sampler here so tab view is where we're going to place an insert after tab view now we have where we're going to enter it we have our custom grid let's give our custom group a name we want to call.
It users our first group is gonna be called users does that right our first group I want to name that entire group here this name here I want to give it a name called users then I want to focus on three buttons within that group login logout and admin and we can do that right here we have art now we have our group name now let's focus on our button ID let's move this over a little bit so we can bring out the screen a little bit we still want to see our names button ID let's give it a button name we can call.
It login button okay and we want to give it a name what is the name that we want to give it we're gonna call it login then what I want to do is I want to enter an image I want to want to take this image and I want to put it there but image Ms oh those are for the Microsoft images for example happy face we don't want we have our own custom images so in this case we want to erase MS oh we just want it to be image next we're going to use the name what is name here well the name here is login.
Make sure we we use it is case-sensitive so when we see a capital L or capital letter we include that so it is case sensitive so login is going to be the image name and of course the size is large what do we want to do what is the macro that we want to run when a user clicks it on action let's take a look in our code in our sample and see what we want go to login this is the macro I want to run so we're going to copy that now we're just going to paste that right into our code right here now we have our.
Login so now we have our size let's take a look at that save it see how we are so far we'll okay after we save it we're going to run validate to make sure our schema is OK and it says it's well-formed so we're good on that let's close this and let's open up our one that we're working on not this one remember this is so let's go back into that and we'll look at the ribbon and see how it looks all right now we have an employee manager here and we have one group users with one login and perfect.
It works perfect and all right now we're logged in ok so that's working great let's continue on now that we see how that works remember close this we didn't make any changes so we don't need to save any changes make sure this is close so we don't want to work on add and go back into the UI editor open that back up open up that folder again look for the custom ribbon file here this is the one we're working on and open that up now we're right back for where we left.
Off so we can continue on we know everything's working correctly so let's expand this and continue on so we've created one button I want to do this I want to do two more buttons in the group so I'm gonna copy this I'm going to enter a new line and then I paste it right down here and we'll tab that over a little bit so it's lined up we're gonna do the same thing once again so we want three buttons in that group three buttons so now we have three buttons we want to make sure that each one has the different next up I want to add the log out so change the name to log out and.
The next one we want admin I want to create an admin button so here now we've got three different button IDs now let's create three different labels so we're gonna change this to log out we're gonna change this to add pin alright so now we've got three different names now we've got to change the image what is the image for our log out let's look in here log out but that's lower case log out I guess I should have changed it but as long as it's the same it's okay so lower case log out alright.
Next up the image for admin is capital a BMI and that is the name here you can also copy and paste this if you're not sure just to make sure if they're long names copy and paste alright now we always do sign our macros to these buttons what is the macro we want to assign to our logout let's look in our sample and we see it's go to logout so let's copy that and put that right in here go back into our UI editor and paste it right in here all right we've.
Got go to logout now our admin probably go to admin so I can just retype that and double we'll double check that all right and look in our code to double check go to admin perfect now we've created our first group with all of the cells we'll double check it just as we move forward and we'll check our XML to make sure everything is correct we validated it it looks good everything is the well formatted now we're ready to create our second group let's do that.
All we want to do is copy our first group hit enter and then paste it and then tap it over so it's nice and organized now we're ready to into our second group what is our second group well our second group is going to be employees where we have four different buttons in our employees so we can then add employees so this group is going to be employees so we want to change the username to employees and we have a button ID what is our first button ID.
Going to be it was call it employee button and our second one what do we want our second one is going to be we're actually have four in this our next one we are going to call it attach let's take a look here see what we have our next one here is employee ID attachments and file archive ok so we have four of them so it is going to be employee ID just as long as they have different names and our fourth one want.
A third one excuse me is going to be attached so we'll just call it a catch button and then we're gonna copy what we have for buttons in this so let's do that copy and paste and create one more go to the beginning a little tab that over here so it's all lined up just with the rest of them and the last one is going to be our archive so we'll give that call that employee archive employee archive ok so now we have four different buttons we now we just have to give them names so let's give them labels give them a label employees and then that we.
Have our employee ID employee ID okay so we have that one next up attachments so we can just call that attachments and the last one is employee archive so we'll give that a name as well and will abbreviate that a little bit okay so we've got that now let's set our pictures now we set our pictures our employee picture is name is employee let's copy and paste it just so we sure.
To get the exactly the right name because we want it we want the spelling and we want everything exactly in there okay and the next up employee ID do the same thing double click and then ctrl C and then the same thing for ID we'll copy and paste that name here next up is the we have the image for attachments so we'll copy and paste just to make sure that we have everything file attachments copy and paste that and you can also rename them here too you can rename them.
If you want of course right from here if you like so the attachments would go right here and put the image their file attachments and then of course the last one we have the archive so employee archive is here double click that or two clicks but slow actually not a quick double click and we'll put that name right here now all we have to do is assign the macro is associated with deeds the first of which is going to be go to employees let's take a look at those here we have go to employees we.
Have go and go to employee ID and employee archive so let's put those in here employee the next one employee ID go to employ ID and the next one would be go to attach let's double check that take a look at the attachments we have employee attached EMP attached so let's.
Update that EMP and the last one we have is archive so let's change that to see what that is EMP archive I believe and double check that in our code to make sure that we're setting up all of the you may want to copy and paste this to separate just to make sure you have all the macros just right input archive alright we've got that we're good there EMP archive just double check that ok that let's get.
Alright so we have that group we've got our first group here and we're looking good now we're ready to create another group and let's take a look at the next group because we have four different groups so we're going to copy this group and then we're gonna enter right after that one right scuse me right after the end of the group we're gonna create a new line and paste that in and bring that over here so it's nice and lined up so the groups are all lined up we have some larger names but that should be fine as long if we move this over.
We'll see everything lines up pretty well but we have to add in the group we we're missing this group you see the end of the group we're missing that at the second ones and we need to put that in tab it over here so make sure everything is lined up group and then ungroup group group ok we have now our third group what is the name of our third group our third group is called timeclock where we have two macros inside time clock and time collect history so let's add that this group name is going to be called time clock and we have two just.
Two buttons in there so we can basically delete these and then add our two buttons in we can call the first one time clock button and we'll give this second one the ID of let's take a look at time clock list so that's going to go right to the list we have the labels time clock and then the second label we will put down as time clock in history or time clock list time clock either way whatever you like time clock history a.
Little bit more explanatory we have the image what is our image it's over here time clock here just towards time clock control see put that in there we'll do the same thing for a time clock list controls see and then back here to the image okay so we've got our now we need to do is add the macros go to I believe it's time clock and we can do the next one here.
Time clock list let's take a look I'm not on the percent sure on that I haven't memorized all of them time clock histories are macro name okay and time clock so let's capitalize the C there and make sure this is right I'll just copy just to make sure we have it right here back into our UI editor I want to make sure that our capitalized this and then our macro we're going to just copy and paste we've already copied it for the history control V okay so we've got that set up let's save it save our work.