Because i want to show you how you can share and sync these id cards among any users and that means any change by an administrator user is going to change for those users and we're going to save all these details and so that all you have to do if anything gets changed or cleared out or automatically gets deleted for any reason it will come back on a click of a button just by clicking the refresh fields i'm going to show you how to do that how to do that we're going to click on the finish customizing we're going to be able to group and ungroup and have everything come together and we're going to show you some conditional formatting on those i'm going to make the update to the classes here which we need to do on this on the header there we're going to make an.
Update to seamlessly run these tabs across there with the code i'm also going to add to the admin how we can add this certificate and we've got adding that so we have a lot to cover this is the sample we're going to put that away because i'm going to go back into the one that we started last week and we're going to take right up from there so we've got a lot to cover on this i'm going to close this out no need to save that one this is the one we're going to be focused on we've got a lot to go on this one because this is where we left off last week but before we do i want to make sure that you know that we do have these trainings free for you each and every tuesday i'll ask is that you just subscribe and then go click that notification.
Icon bell that'll get you alerted each and every tuesday i bring these to you and happy to do so and if you want to comment below i'll be sure and respond if you like these applications you like developing excel applications i have just the program for you i've got the excel for freelancers mentorship program now available my excel mentor.com what will you learn in there you will learn a ton let me show you what we're developing in there we're developing a complete accounting system and we're going to show you how to do that it's going to become of course complete with an ad dashboard complete admin you'll be able to run any report from the toolbar just like that of course it'll have a complete chart of accounts be able to enter any type of transaction writing checks credit card charges we'll have a general journal be able to make transfers reoccurring.
Transfers reconcile account you'll even be able to print checks with multiple different check templates here on that we'll show you how to do that we'll have customers in invoicing along with a really dynamic invoicing and that goes along with same as a credit memo even issuing credit memos to customers of course we'll have customer information complete with invoice history and payment history along with vendor information complete with purchase order and general info items in purchasing email automation and a ton more if you want to learn all that and how to make and sell your own applications the excel for freelancers mentorship program is the best program to do that all right thanks so much we're going to get started right now.
Back into this okay a few updates so we have some updates we've got a lot of things and this one remember we changed this to a 10 we change this to classes but i want to make sure to change this name actually to classes right so this is this tab is going to be called classes i want to get that done instead of attendance attendance we're going to be covering certainly in our dashboard we'll cover a lot of reports but classes we want to we have all this i want to make sure to change that tab okay we're going to go straight into the developers there's more things that i want to do inside the id card i want to add some buttons here i'm going to increase this column a little bit so we can add some buttons what kind of buttons do i want to add well i want to separate the ability.
It's still within the v i'm just going to put something called id edit mode id and then called custom mode and that's going to be basically true or false we'll just have a boolean we'll start it out as true and so what i want there is basically it's when we put it into edit mode it's going to automatically have things available that aren't available when there so they'll be able to edit those specific things and also what i want to do is when i turn change this to none i don't want any fields to show up in this case the picture when i have labels and values when i make changes here i want those changes to reflect here on the card so if i say value only the first name will go away and it'll only show the the actual name so i want this to be dynamic and based on that.
Last week we created these fields but what i want to do now is i want to make sure that these fields and their position and all the information get saved somewhere and the reason we want to save that all that information somewhere is that when multiple users gets that they can basically create these id cards dynamically pretty much just running a macro so that means if the administrator decides to move this over here automatically when the user gets that they're going to be able to just click refresh and it will update automatically so i'm going to show you how to do that we're going to do that with a few buttons we might as well insert the buttons right now in fact i'll just use this format here so i'm just going to copy this update button and just place.
It here and so what i want with that is i want to i want to have two buttons really one called finish customizing and one called customize so customized eddy card so we'll call this finish customizing when they're done customizing we'll give it a probably a width of let's say 1.4 something like that and then i'll write justify and make sure it's right justified alright it is okay good so that's what i want to do and a little bit more on that one maybe 1.55 i've got a bit of an icon there and i'll increase this a little bit okay so now back to 1.55 and we'll.
Go ahead and set those to size and i also want another one i'm going to duplicate that control d and i'm going to call edit id card edit id card so that's going to help us there so we have both of those fields and i'm going to want some icons on that but i'll take care of that i want another one i'm going to delete that one called print id card we want to be able to print it and i also want to have another one called refresh field so i'm going to duplicate that and call this refresh fields and that's going to be able to take all that information from our database and basically recreate it just how i showed you on the sample so these particular three we can size them accordingly maybe just one inch is enough for those 1.2 let's try to make them the same okay so once we have this i want to put some icons on there i'm going to assign the mac or actually i'm going to clear the macro from that we're not ready to put a macro on that yet okay so i'm going.
To bring in some different icons so i'm going to insert i've got some saved up it's going to make it helpful for us and so let's put them up here so i've got let's see i'll pull all these up i think we're going to need all of those and click insert and then i'm going to size them to about 0.22 that's going to size them accordingly and now we're ready to place those icons so finish customizing it's kind of hard to see let's that's going to be the edit id card this is going to be the finish card and this is going to be the print one and then we have the refresh i'll make the refresh a little bit smaller too so point two on that one's a little bit bigger okay so we're good to go on that that's actually make it even smaller 0.19 okay so i'm going to make this button.
A little bit bigger because it doesn't have it and then we'll go point let's see on this button here
Format point 1 3 1.3 should cover it okay i like that better so now what we want to do is i'm just going to group these and just give them some names and i want to use some specific names that's very very important and then i'm going to group those and then i'm going to do the same thing here i'm just going to align them in the middle here and then group them i'm going to do the same thing for each icon here as we do before if you're following me and then finish icon i'm going to do that too now i'm going to probably take off all the macros on this i know some still have macros assigned because i duplicated them i really don't want that i don't want any macros assigned so i'm just going.To clear in case we accidentally click a button just clearing those out we'll assign it okay so the edit id card and the finished customizing those are going to be over on top of each other in other words we're only going to show one and i want to make sure that each one of these contain a specific name a clear name that is unlike anything else and also contains the words id card so let's call this one called finish id card finish custom id card each one of those are going to have the word id card in it because i'm going to need to group these i want to group these and i want distinct qualities on each one of these this within their name so that i don't group all the shapes in the sheet i only group those that concern id card so that i'm going to use that id card so this we're going to call it edit id card and then also we're going to have print id.
Card because you can imagine so each one's going to attain that that's going to become helpful a little bit later print id card and then the last thing refresh field id card we'll just call that refresh field id card okay so that's important i also want to do the same thing here i want to add some top also let's call this we're going to call this front top id card and we're going to call this back top id card just also back top id card and then i want to do the same thing with borders because when we group them they all must have the same name so that we'll call this front id card.
And then we'll call this back id card that should be sufficient enough so that we can then group back id card good so i like that the way it is there so they're all named accordingly so they each have and again the i edit and the customize i want to put those and on the top these two here i want these to be the same i want them to be aligned horizontally on the top so i'm going to move them up because they're going to be placed directly over so we have two buttons here if we open up the shapes command we can see that the two buttons here are refresh print id finish custom id and edit id card so they all have the word id card in them and that's very very important.
Okay so then print id and refresh fields they can just move up here and then we'll center everything accordingly here all right good so we've got our id cards that's going to really help us let's write some macros that can help us do some of the functionality that we get these completed i can zoom out a little bit we don't need to zoom in all the way now so into the vba we're going to go inside the visual basic and we're going to be heading over to of course the student id card macros now last training we went this to this went to from 10 to 101 so that's why we had a lot of different fields i've just corrected that if you'll notice that that was just a little issue we had but basically we went instead of 101 it said 10. so keep that in mind all right i saw that during the video i was like oh no okay so let's what do we want to do well the first thing what i.
Want to do is i want to go into customizing mode i want to do a few things let's write a macro called student id customize where we're going to actually go into the customization mode so sub student id card and then customize all right and within this what we want to do i want to focus of course we're on the student sheet so that's what we're going to focus so with students that's the sheet we're going to focus on notice that end width that's auto hotkey and then what we're going to do is i'm going to have a specific group a group of all those shapes grouped together.
And if that group exists i want to ungroup it right so so the best way to do that when we're customizing is to ungroup it so dot shapes and it won't exist until we create it student we'll call the student id group and then dot ungroup so we're going to ungroup it and if it doesn't exist it would create an error so we do need to wrap it in onair resume next and on air go to zero okay so also i want to set remember we had b99 that's going to be are we going to set this b99 to true we know we're going to and we're gonna add some conditional formatting in here as you saw in the sample so let's do that so dot range b 99 equals true edit i'm gonna put that edit mode to true okay once it is true what else do i want to do i want to basically show the finish.
Button this button here i want to show the one that says finish customizing finish custom id card i want to show that and i want to hide the edit custom id card so i want to hide that so let's just take a look at these buttons so we got your name so i want to show the finished customer id card and i want to hide the edit id card shapes so let's write that in right now so dot shapes finish let's bring this down so we can see both at the same time that's always helpful and then okay good there we go so now we can see our button so we can see everything we can bring this up and we can continue with our code here so finish see now we can see the shape so we know finish customer id card dot visible equals mso true right we want to make it visible show the finish button.
Show finish button and we want to hide the edit button dot shapes and then the edit button there is of course called edit id card edit id card dot visible equals mso false we're going to be hiding that hide edit okay all right so now that we've hidden that what else do we want to do i also want to make sure that we're going to be locking some fields right if we're not in edit mode i want to make sure to lock these fields this one these are formulas this is always going to be locked right but the users are going to have editability on this but i want to unlock those fields so all the way from e101 all the way to f120 we're going to lock those fields so that way once the sheet.
Is protected they're not going to be able to edit those so let's do that dot range e 101 all the way through f119 let's make sure we got the rows right i have one one let's go 1
120 actually on that 120 we want to lock those so 120 and how do we lock this is not going to be value it's going to be locked equals of course false in this case because we're in the customs locked dot locked equals false right and then of course once we go out of the customization we'll go ahead and lock those and it'll be true that's all we're going to be doing as far as the customization as far as inside the customer id card customize so when we run that there all we're.Going to see is that button disappear let's close that definite now we have the finished customizing this button should be a little bit bigger inside and so that's it so that's all we need once we have that there we can then easily then get into the edit mode okay we'll just add some conditional formatting in a bit too so we've got the so what are we going to do once we're in the enema now that everything's unlocked we're going to be able to save those but let's focus on the finish once we're finished customizing what do i want to do when i'm finished with this i want to make sure that everything gets saved exactly where it's at right i want to be able to make sure that all those fields exactly where i've placed them are going to be are going to stay there in case there's any changes i want to make sure that everything doesn't doesn't move or doesn't change.
So how can we ensure that that's not going to happen well what we can do is we can line those up and we can save the information all the way and how do we going to do that we can save it with it some kind of a table so we want to this is in excel so we might as well save that information inside a table well where's the table why don't we put it somewhere why don't we put that information somewhere let's go ahead and put it all the way over here i want to save all of that information let's say starting on let's let's reduce these down to nothing here i'm going to do the same thing for this because we don't need this all this extra space here and so what i'm going to do is we're going to start i want the rows to be the same in other words if i make a change to 101 i want that information to save 101 here on ba so i'm going to put the information here and what do i want to save well i want to save two different things i want to save one the field value and.
I want the the labels and i want to save the values right we have really two different we have sometimes we're going to show like in this case the labels and sometimes we're going to show the values so i need to save each one of those so we'll have two different sections one for the labels and one for the values so we'll start out with the field labels so i'm going to put the field labels up here the main title called it's called field and this will be hidden you know nobody will see this field labels and then what i want to do is i want to put what do i want to save i want to save the left position i also want to save the top position and i want to know the width of the shape once we save it we can recreate it i also want to know the.
Height of it and i want to know the font i want to know the font size i want to know the font color and i want to know the back color in case we want to change the back color we can save that too that should be sufficient now keep in mind that the picture itself will only have these the picture we will have to make some checks in the code to make sure that the picture we don't have this information for the picture but this is enough so this is our field label so we can merge and center this one here and that's going to cover i'll just give it some little bit of a color but it's really not too important in this case let's drop this down here but i just want to notate that you know these are admin and nobody will see that once we have that we can then so that's good.
For the field labels but what about what about the values we need the values too i want to save those so we can save those somewhere else too why don't we to make it easier let's use ca so i'm going to just go all the way over here and then i'm going to shrink those columns down all the way or just almost hide them but shrink them down way a lot and then so what i'm going to do is i want to put in ca i'm going to put this but this one's going to be the field the values field values and so we're going to do the same thing everything we're going to simply save all the information here so now we know where we're going to save it so for example if i want to know the left position here is a let's say student id this is a.
Student label in row 105. so in 105 of that label right here i'm going to put all the left position of that i'm going to put the top position the width and i'm going to have vba do it all for us once it's saved in this table like like you saw before once i delete everything all i need to do is push a button and it'll recreate everything automatically because we have a sample shape we can take this sample shape and we can recreate every field based on this based on all of our data so that's exactly what we do so let's write some code to do just that saving our work and we're going to go in and we're going to write a brand new macro and this macro is going to be called student id card finish customizing it because when i'm when they're finished customizing.
It it is at that point that i want to save all of those changes into the table so sub student in this case id card finish customizing okay kind of a long macro name but that's okay so what i want to do is i want to dimension the shape let me make sure that i've got some shapes i think that's sufficient i'm going to dimension here the shape group as a variant because we're going to need to group these shapes so it's going to be half as a variant and also i want to dimension the shape array since we're going to need to group all those shapes into an array they're names so we're going to need that here shape array as a string now we've already dimensioned other things up here.
Hopefully at the top i've got a bunch of things field row okay field shape we're going to be using these two but these are fine up here but these are specific to this macro so i want to make sure that we have those shape group as a variant shape array as a string and this is going to be array so we're going to put the parentheses around there like that okay so now that we have that now what i want to do is i want to first of all i'm going to turn off application screen updating this is going to run a little bit slower so we need to make it a little bit faster so application dot screen updating equals false and before the macro ends we're going to make it true so application dot screen updating equals true now we're going to write everything in between we can turn off calculations but there's not a lot of formulas in here so that's fine not false true okay true.
Inside there we're going to write all the code so what do we want to do the first thing what i want to do is focus on with that student sheet with students sheet that's the one we're going to focus on dot make sure just adding the dot make sure i got the name right but i never did so the first thing is i want to ungroup remember we had a group with student groups i want to ungroup this but for some reason if it doesn't exist it's going to create an error so again i'm going to copy this and paste it down here i'm going to ungroup that by the end of this macro we're going to write another macro we're going to add it in to group them so the first thing what i want to do is i want to go through all of the shapes inside the sheet but those shapes are very specific notice first of all keep this in mind this one have i.
The words id card but everything in here has id card in the underscore so i need to differentiate between every single field id card and the underscore id card in the underscore so that's contained with both the values and the labels both have that so that's what we're going to work with so so that's those shapes only those shapes that i want to work with so for each field shape in dot shapes that's all the shapes within the sheet and then we're going to close our loop next.
Field shape we're going to work with all those shapes first of all i need to make sure that we set a field row so what is that field row going to be the good thing is because we've named because we started out at 100 conveniently kind of luckily i know that the last three digits the last three digit of every single shape is going to be the row number automatically because we're in three digit area so we can just extract that row number from the shape using the left command so let's do that we know the row that it's going to be placed on right we need to know the row where we're going to save this information what rows are going to be 101 102. so based on the shape type we know the.
Row here it's going to be in this case 105. so why don't we write that out let's extract that field row make sure that we have that as a i believe that we have that as a field row good we have that as a variable a long variable so let's do that so what's the field row field row is going to be equal to and we're going to use the right command right i want the last three digits field shape dot name the name of that shape and i want only the last three digits that's going to get us our row.
Field row so now we know the row so now we're just ready to move on to the next so what are we going to be doing now i want to check to make sure to see what is that what if if it contains a label does it contain things and i want to continue on but first i want to make sure it contains that id card in the underscore so those are the only ones we're going to focus on right now so if in string i'm going to use the in string command what is the we're going to again we're going to use the field shape name we're focused on that name and if it contains id card and the.
Underscore that's distinct about every single field if that's greater than zero then we're going to continue and if so everything's going to be in here everything right we're only focused on those shapes okay only on those id card shapes but now we want to drill down a little bit further now we know that the container id cards but we've got labels we've got a picture in there and we've got values in there so we need differentiate between those types right why do we need that because we know that our field labels are going to go in this section our field values are going to go in this section so we need to differentiate between those so why don't we do that now with just a little bit of code that we're going to write okay continuing on so if the in string again we're.
Going to use instrument field shape dot name this case if it contains label that's the one i want to start label then what do we want to do greater than zero that means it's contained then we're gonna set the field column right i wanna know the field column right we know the row but we gotta know the row how do we know what column i wanna know what starting column what is this the labels what column is this well that's column 53. what's column with this 26th grade of course that is of course column 79. so i know that if it's a label where starting column is on 53.
And if it's a value it's going to start on 79. so having that information we can set that row then so the if the label in string label column is less greater than zero it means it's a label then what we're going to do is we're going to set the then the field column is equal to 53. else of course the field column is equal to 79. okay that's gonna set the column and we always anytime we place data we need three things the sheet the field the row and the column.
Now we have all we know what sheet we know what row and we know what column now we can work with it now we have all the information that we need to place that information in okay so we know that so now we're going to ready to set up so what is the left position so if it's labeled this so the left position is going to be basically the row the cell and that so we but i don't want to just put the left position this i want to put the left position based on a specific cell that specific cell is going to be i 102. everything is going to be based on i 102. so how far from the right is it and we.
Use the left command to do that so that's what we'll do so dot cells we're going to use dot cells in this case because where both our row and our column are dynamic so the field row comma field column dot value equals field shape dot left right the left position but it's not the full f position it's minus the left position of the left position of minus dot range again i 102 102 dot left so it's minus the left position of that cell so we're subtracting so i want to know the difference.
For example i want to know the difference between this position and the left position i also want to know the difference between the top position so that when we place them back in there we're going to figure out whatever i 102 the left is and place it to the right of that or place it below the top so we're going to use the top left cell to do just that that's going to be our guide so next up we're pretty much going to just copy this let's just put that in the left position and now i want to just paste that in here let's go ahead and paste we'll just add that copy that then we'll just put it in the top but this time we're not focused on the field column right our field column here is 53 but in this case it's 54. so i need to add one to the field column for.
That so let's add one field column plus one is not going to be now we're not focused on the top position we're focused on the top position based on the shape and the based on the cell so now we're going to change this to top position topo and then top here so that's going to give us our both top position from range 1 i102 that's what i want now all we need is the width and the height so we can just copy this and of course we know now that it's not going to be in this cell it's going to be plus 2. that the next one of course let's take a look in here is our width and after our width comes our height so let's add that in here dollar.
Equals and in this case all we need to do is field shape dot width we want to know the width of it so once we have the width the next thing is we always want to height so that's the width let's just put that in although self-explanatory the value equals i'll fix the column in just a second field shape dot height okay that's the height and we know that in this case it is going to be field column plus three and so there's our first four columns we've got the left position we've got the top position based on i102 we have the width and we have the height now what i want to do is now i want to make sure that we're going to add in the rest i want to put in the font size.
The font color and the back color i want to put all that in but i do need to run a check because that information is not going to be put in on a picture right we're not adding that information and pictures don't handle that they handle the top with the rest of them but not the continuing not the following four conditions so we need to run an if statement if in string we know and we know from you know from renaming that our picture contains this word pic so anything that contains pic i know not to include so let's use that as our check so let's take a look if in string again field shape dot name of course we could name this as a variable two it would be a little quicker but.
Pick if it contains pick or we can put does not you know in this case in this case in string picture what we're looking for you want to make sure it equals zero then we continue on the following so then we do the we continue on with these only if it does not contain pink because the picture would not use these of course that's the font the shape and there's so four right four what's four in our column four we're looking for now we've got the font the font size the font.
Color and the back color so now we have all four of those to add in of course not for the picture so we're going to do that just right now so for dot value what's going to be in that in that case we're going to put the font name so equals in this case field shape dot text text frame i want to focus on the text frame 2 dot text range dot font dot name that's going to set us to set the font name i also want the font size but let's copy that and then we'll just paste it down there and we'll get the font size we'll make a few small changes of course it's going to be column 5 that we want that in and we also want font name in this case we're going to focus on the font size.
So dot size is what we're focused on that's the font size i also want the color too color so i'm going to paste that in here again in column 6 and i'm going to do one more thing let's see 6 and then i also want to do seven is going to be our last one here seven okay now we just need to update so i want the font color let's write color and i also want the back color the back color so what would that be what would the font color be well the font color would be something like text range but not font name so it would be text range characters dot font dot fill dot for color.
Dot rgb i want the rgb of that that's going to be our font color actually got it i got that wrong let's do that right up here that's the one i want font here and we'll update that so characters with font fill okay but what about the backfill this is going to be the backfill of that shape so what do i want that it's going to be the field shape in this case fill then what do we want then i want to know the four color of that fill and then i want to know if it's rgb or not so rgb that's it for the back color and that's going to be the seventh column okay so now we saved all that information.
In okay perfect okay that's it so now we can close up our loop making sure we've got all of our two and if that's correct so we've got the end if here based on the picture we've got an end if based on it is containing an id card and we've then we've got our next loop and then we have our next loop so everything's lining up just right okay so what do we want to do now again i want to set the being that now we're in edit mode so now we're finishing up edit mode i should say so we're going to change dot range b99 i want to make that false no longer in edit mode equals false and then also what i want to do is i want to update these two things these two buttons here i want to reverse these so we're going to show them hi there so i'm going to paste those in here.
In this case our finished id card this is going to be false so i'm going to hide that we're now finished with it so we don't need to show that but i want to show the edit button in case we want to edit again so see true showing that edit button what else do we want to do once we have that i also want to again lock these so now we're going to be locking these equals true so i'm going to copy this one and i want to lock these fields and paste that down here equals true so now we're going to lock the field okay great so all i really need to do now now what i want to do is i want to.