Learn How To Load Employees & Pictures with this Excel Employee Manager [Part 2]

hi this is Randy with Excel for freelancers and  welcome to part two of the employee manager where   we've added file attachments additional fields  we've added a full scheduling tab as well as a   leave tab we've also added the ability to add  employees which I'm really looking forward to   showing you and they're all based on additions  that you have requested and I've written them all   down and we're gonna go over them so I'm really  excited to show this to you let's get to it all right welcome back to part two of the employee  manager we've got a lot to cover today and as   mentioned in part one and if you haven't seen  part one please do so I'll include the links in   the description for part one because we're gonna  take right off from where we ended up on part one   and as mentioned before in part one we're gonna  be taking your suggestions very seriously on what   to include in this employee manager so let's get  started with just that I have written down all of   your suggestions everything you have commented  in both Facebook and YouTube as well as private   messages and I've got them right here so let's  go over them real quickly so we know what we're   gonna be focusing on Eric pest or I'm gonna get  your names all messed up so just bear with me   it's impossible to work scheduled to be linked  to this sheet yes work schedule something that   is common even Louis cadet a I would also like  to see a schedule featured linked to the time   clock Piero story sure yeah we're gonna put that  it in this week now it's not gonna be functioning   yep we're gonna put all the capabilities in there  so that we can have that for the future so we're   gonna be focused on the schedule because that is  an important part a lot of you've mentioned it   richard hansworth also mentioned I'm wondering  how easy it would be for employees to request   annual leave and capture the request decisions and  track annual leave sickness special leave invents   yes leave paid or unpaid is also something that  we're going to be focused on in this as far as   capturing that data and so we're gonna put in room  for that so that we can use that later on for the   payroll so that is employer it would be great to  tie this up with a payslip generator okay we're   gonna work on that that's gonna be focused in the  reports once we generate the payroll we'll have a   report station where we can generate payslips  so yeah that's something that's going to be in   the future so we'll work on that disciplinary  records we may put an Events Manager in here   so we can track events per employee we will cross  that bridge a little bit later on depending upon   how many people want to see some sort of an Events  Manager tied so that might be kind of helpful GDP   our requirements we may add some users security  on to this a few of you have mentioned that to me   so we do have some user security I think that's  going to be kind of important especially we're   gonna have some private information like social  security numbers Jules mentioned would you like to   see tabs for employee absence and training yeah  we're going to include those tabs we're going   to be adding tabs into this part too annually  sickness suspension disciplinary again so a lot   of you're requesting the same thing so that really  guides me to what areas are important jury service   and training now there's a lot of different types  of absence that we can have so we're gonna create   an open table where you can create your own type  of absence there's just so many different types   so I think this is we can't really have a feel for  everything but can create an open table so we're   gonna do that Jan crude is impossible never work  schedule yeah we're gonna include that mercy wing   I'd love to see more key employee data capture  such as position title company will include some   of that today we're gonna add a little bit more as  far as population specific fields of payroll that   are only associated with a manager I will try to  get that in we will we do have employee types of   week include filters later on Michael maja Rica  messed that name up I'm sure the tax revision we   nice to use code from your attachments project  attachments that's a good idea I like the idea   of having attachments for each employee we're  gonna add some space for that in this week so   because employees have additional sheets you know  you may have worksheets may have PDFs may have   filled out forms so having attachments attached  to a specific employer is kind of a nice idea so   let's go we're gonna go ahead and add that in as  well at least that the space is for it and then   we'll add in the functionality later because we  do have a lot to cover I don't know how long the   it's training this series the employee is going  to take it really depends on your suggestions or   as long as you have interest in it so make sure  you view the entire video that helps me know that   you're interested in this and you want to see this  application to completion we can do a lot as long   as you remain interested in in absorbing it and of  course sharing and liking this video really helps   me understand and know that this is something  that you want to see Chuck Hamby I'd mention you   can also do a performance review tab and I think  that goes into the events and I think having an   Events Manager would be good as mentioned before  Kenneth mentioned I have a proposal regarding   working hours and Sweden okay I understand that  I want to know the working hours how many hours   in a week how many hours in a day so we're gonna  include some of that scheduling as far as totaling   do I understand the requirements for that a lot of  you regardless of the country you're from you have   certain specifications as far as how many hours  you can work in a day in a week or multiple days   so we're gonna put some of that in Frank these  always appreciate Frank's comments the following   groups are years of service male/female care we're  gonna include gender in this in today's yearly   sit-down that's again a we're gonna go with that  in events I think in having an Events Manager so   that you can put any type of event like a yearly  sit-down or discussion or something like that and   to what occurred in that event would be really  nice because there's so many different types of   events that can be associated with employees so  I think that's good a dashboard for department   overview we will probably include it the dashboard  a little bit later I'm drilled down as a nice idea   when it comes to payroll and work hours of  employees so we'll try to include some drill   down features in this especially in the reporting  once we have all the data then we want to be able   to drill down into it so I think the reporting  issue will give us our drill down availability   perhaps a period to drill down hours work details  yeah that's good Webster webs print out the report   payslip in PDF format that can be included also in  the reporting and that's going to be at the very   end once we have all of our data incorporated then  we can focus on the reporting of that and putting   it in different format so that will come in in the  weeks to come near and in this tutorial would you   like to be catered to those HR or current data  yeah for sure you know this is gonna be for HR   and then we're gonna data mapping data mapping on  imports nice so if you have hours or or employees   or your data information is in a different type  of format could we import that data maybe we can   take that into consideration to bring in that  data into the hour so although I would like to   have an application that captures all the data  itself you know as far as hours are concerned   we can build that out but importing it would be  really helpful because a lot of you track those   hours in different applications so that might  be something that's a really good idea so thank   you for that Satan was just puro Heat can we add a  keyboard shortcuts that's a nice idea I like that   keyboard shortcuts on tab maybe next week we're  gonna tackle that and that's moving from tab to   tab using a keyboard shortcut so I do like that  idea Charles Charles is always a really a great   big fan of ours would it be possible for you to  demonstrate what changes would be necessary to   scale this application so it can handle from 100  to 1,000 yeah definitely we're this application   should be capable of handling thousands of  employees so I think that's a great idea and I'll   try to we'll try to build it and make sure it's  scalable so they can handle a lot of employees   and we'll go over some of that and how we're gonna  do that today when we add in the employee tables   I was thinking that you could use the list box  for employee lookup yeah we're gonna have a we're   gonna work on an autocomplete so that when you  start typing in the employee name the employee   name comes automatically we got a lot to cover  today kay 102 stone could you add time off request   vacation time sick leave yes we're gonna add that  in today and all your going to add security levels   I think so I think that makes sense if you found  we do have a year user security in details video   way back almost a year ago some of you're familiar  with that and I think having that type of security   in an application like this is pretty important  so I think towards the end we're gonna add in   user login and per sheet security I think that's  a really good idea to bring that type of security   into a payroll especially something as sensitive a  payroll you can't just have any employees looking   up payroll records so it's a very sensitive data  so we want to protect it Hashim Khan I suppose I   have a manpower 5,000 place they'll need vacation  so we're gonna focus on vacation exit resignation   or terminate that would be four events so I think  we're gonna include Events Manager in this and   then Randy Austin I don't know who this guy is  some crazy guy make this application shareable   these are my ideas yes share a bowl I'm gonna  try to build this application so that the front   and in the back end are always separate because  having multi-user ability with this application   would be really great we want multiple people  to be able to make changes nearly at the same   time with this application to do that we'll use a  shared workbook but we don't like excels version   or shared workbook we want to create our own  so that we can share with anybody in the world   using simply any kind of a shared photo like  Dropbox Google Microsoft onedrive or P cloud   or so many other great types of shared so those  are the suggestions keep those ideas coming I   really love them and they really gonna help us  form this application so let's get started into   the application and I've got some ideas and  some changes that we want to make and first   of all we're gonna add some more tabs to this  because based on your ideas we want to do that   so let's add one more called scheduling do we know  we need to focus on scheduling and perhaps another   one called leave a lot of you mentioned leave  absences so we're gonna keep it very general   called leave we can copy this formatting here  that we have in the other tabs over to the new   tabs and then we'll paste that formatting let me  can go ahead and go into conditional formatting   and just update that to make sure it's all set  right into the manage rules we don't need two   of them we just need one of them so let's update  this one all the way until jj4 which is what we   want everything else is fine and we can delete  this one now we have one conditional formatting   rule from efore to j4 and that constitutes all of  our tabs so we can check that out and then close   it move over all the to the selected column here v  if we change this to seven but still it goes over   to payroll history and then our new tab be nine  and ten so let's look at that that looks right   and then J leave so that looks right also okay  so we have the conditional formatting setup now   we'll update the VBA code so we can get those  now we'll notice we've ended it 1:45 so we need   to update these to include everything from 145 to  164 on the scheduling and then for the leave we're   gonna go from 165 all the way to 184 on that so we  need to include those in that let's go ahead and   go into the developers and the VBA and update our  code accordingly if we just update the code into   the sheet one we have each be four to h4 on the  selection change we'll need to update that to j4   and that's not gonna be quite enough but we need  to go into the tab macros and make adjustments on   here as well and I'll show you why if we were  just to click on the scheduling it's going to   go to the earnings information because we add 20  so we need to make some considerations for that   in other words we've added 20 here we add 20 here  we had 20 here payroll detail includes a lot so we   so anything after that we have to compensate for  that in other words scheduling it's gonna really   start on 1:45 so we need to update the code for  that we can do that in our Excel macro here under   the tabs macro here so we can make a consideration  for that here and in this code we want to say add   some additional first of all we need to change  this to 184 because we're gonna increase the   number of rows so we want them all hidden also  we need to make a consideration for the selected   column if the selected column is greater than 8  right because our new ones are 9 and 10 greater   than 8 then the selected column equals selected  column plus 3 what that's going to do is in our   formula here it's gonna add additional rows on to  that it's gonna add additional rows let's take a   look at that and see how that works so because we  have to add additional three because we've used a   total of sixty rows or eighty rows in our payroll  so additional 60 additional 60 a total of 80 for   our payroll so we need to add for that so when we  go now when we go click on scheduling let's fix   that that should be selected SEL and when we add  that on to that let's take a look at it now so now   we've got one 45 that looks right one forty-five  and then when we got leave one sixty five perfect   so that's what we want so that we can now update  the formats now we can work with the scheduling so   from 145 to 164 that is our range right here we  can format those cells we can let's raise it up   format those cells and add the borders to that  we're not going to add the under border we're   gonna add that on 185 because that's the last  row so let's do that now 185 is here a lot of   top border to that I'll raise it up so you can  see it format those cells and we're gonna add a   top border that's going to be our default blue  which is here and a medium thickness which is   here so we're adding that top border because this  185 is always going to be seen so that's important   now we can do the same thing and leave let me just  check that scheduling we need to add out we need   to remove the bottom border from 145 145 was our  previous bottom so we need to remove that format   those cells that was our previous bottom so we  need to remove that because it's no longer our   last row now 185 is there we go so now 185 is also  on leaf we need to add so now we've got 185 it's   looking good we just need to add the borders  on the leave and that's gonna give us our full   tab effect then we can start filling them out and  format those cells and then we'll have the right   and left border okay so now we've got now we've  very simply we have our full tab area general info   time clock history payroll history of scheduling  scheduling and leave now we've got everything set   now we just have to fill in the details but let's  fill in let's start with scheduling on that I want   to add a weekly schedule so let's call a weekly  schedule and perhaps weekday is going to be our   first column work Dan you know if it's a workday  or not if they're offers workday what the start   time is what the end time of that day is and  perhaps the total break hour so let's put break   hours and then the total hours this will be a  formula okay and first day of Monday start off the   Monday and then we'll continue down all the way  till Sunday on that and then I'll write justify   them alright that we go and we can do a workday  what I want to do is I want to I want to know if   it's an off or if it's a work day I want to create  a data on that so let's go ahead and I put in a   data validation just gonna have two options  on the list is it gonna be offer is gonna be   work day work day or off separated by a comma and  now we have the options perfect so let's go mark   these as work days and then we'll do Saturday and  Sundays off just just to put some data in there so   we can see where we're going on that I'm going  to conditional format those and for start time   and end time I want to create a drop-down list  of times so that you just have some control over   that why don't we go down start at 185 I've get  to create a list here of times just want to start   it here so that's always a visible and something  out of the way starting out at 12 o'clock a.m. I   want to include all times because you never know  when shift start and I want to increment it every   15 minutes now how do I get 15 minutes I want  to make sure I'm going to use a formula so we   know a day is one one so equals one divided by 24  it's gonna be one hour but what is it if it's 15   minutes well that's divided by 4 again right so  divided by 4 again and that's going to equal one   zero zero four zero it's point zero so that's our  15-minute increment copy that I'm just going to   paste the value because I'm only concerned about  the value and now what I want to do is gonna copy   that control C equals 12 a.m. plus that number  good that's what I want now I can continue down   that and you see how we got increments so now  we're going to go all the way down for a full   day's schedule and let's continue down all the way  we're gonna go all the way to 12 and midnight so   that'll be our list 5:30 a.m. too far okay here  we go 12 so the last time will be 11:45 p.m. so   that's our list that's our x list let's capture  that list and we will call that just times so   now we've got our time set so now we can use that  in our drop-down list and it's going to cover all   the all of them so its start and end times here  we can use that as our data validation and bring   that in and I'll be at a list and it's getting  equal times all right there we go now we've got   our now we just have to format them accordingly so  let's go into home and I'm we can format them as   time but I don't really want the seconds on here  so let's modify that and just go to say this you   can you can use whatever times you know based on  your current I'll just use this at 12:45 a.m. and   then we'll probably sin or left justified other  ways good so now we've got our x in there we'll   format this table it's merge and center this and  we'll use our darker fade for this theme which is   the blues filling we're going to go from medium  to a little bit lighter so the fill effects will   create a fill effects we have two different  fill effects ones for our header this one is   going to be used from dark and then we'll the one  below that will create just one lighter on this   one will Center it format those cells and we'll  go ahead and give this a fill effect of just a   little bit lighter here so it'll go to our medium  and then to our light blue that'll give us a nice   fade out effect for both of them we can give it  a border inside border of the lines so use those   thinner lines for the inside and also we can use  a top border so that gives us a nice fade effect   and I want to create this schedule and we're gonna  use this for each and play will focus on the data   aspect of it a little bit later on right now we're  just creating the information we don't want to use   connected to a data just quite yet because we want  to make sure that we have everything set just the   way we want it all right let's create some borders  and I'll keep this blue and this is not gonna be   user entry so we'll keep this darker because the  wider we want we want user entry white or so these   fields are going to be for user entry these fields  are going to be for user entry let's color those   white and then give it a given a alternating  color as well so that's a little bit easier to   see using conditional formatting or there's a few  ways there's only a few cells using the mod which   we've done before and then we'll use our and then  that'll fill out right remember this comma could   be semicolon depending upon your different  conditions fill effects and let's go into   fill effects because I'm going to use a blue that  I've used before which is here and that's common   and that we have recent colors when we use fill  effects even though we're not using two colors   we're using the same both the top and bottom but  our color we can easily locate our recent colors   when we use fade effects okay that's good and  give it some borders and then we'll be done with   this and we can move on from that I'll scroll up  so you can see it format cells and then we will   give it a lower border and then dotted line for  the center then maybe a line him it's continued   dotted line okay so we're good with that we've got  that formatted alrighty we're gonna keep this dark   cuz that's gonna be a formula and we're gonna keep  this and let's go ahead and add that formula now   just so you can see we're gonna finish just do a  6 o'clock p.m. 9:00 a.m. and then we'll copy that   down just a standard schedule for those days 9:00  a.m. to 6:00 p.m. with a one-hour break that's   going to be 8 hours total so in this case it's  simple math equals and then we'll have to multiply   times 24 equals and time minus the start time and  then we also want to subtract the break too but   the break is in one hour you see the break is one  hour and we have this equals so if we were just to   do this right we'll get to 9:00 a.m. that's not  really what we want we want a number so let's   go to number right but 0.38 is not what we want  either we need to multiply this times 24 because   we need to know by the hour so it's gonna be nine  hours and of course we have to subtract the break   now so minus the break hours and that will give  us our total hours which is 8 hours and we can   copy down that formula we'll just [ __ ] that and  bring just the form as I don't want to mess with   the conditional formatting and there we go so now  when we add in the times we can copy down this on   our work days and paste those values in there and  then also the break so any any differentiation in   time is going to result in our different time so  that'll be really really helpful let's Center this   as well we can Center we can Center both of these  ok so now we've got our total hours so if we just   make a slight change to 6:15 change it to 8 and  1/4 this brings our hours once again we need to   multiply this times 24 because in Excel one equals  one day so we have to remember that so if break   hours so we have to differentiate between that  I just want to add some conditional formatting   here for the off days so let's highlight these  and then we'll add some conditional formatting   numeral we'll use a formula and we're going to  start with the first possibility it's also the   first row of our conditional format so the first  row of the possible change it's also the first   row of it's going to apply to you which is f4 now  we also have to remove this dollar sign we do not   want that fixed we need to use that for all the  fected rows equals off in parentheses if this is   often I want to format this and what I want to  format it let's just make it a gray fill it in   with a little bit gray a light gray and then we'll  use the font of a little bit darker gray so that   way our off dates are clearly differentiated  between our on days and now we see if we were   to change the dates off it'll work okay great so  that's working just fine we've got our schedule   set up let's add some totals in there total work  schedule just so we know the total hours total   breaks maybe and networking time which means is  our actual working time and let's go ahead and   add some colons in that so it's uniform and then  we'll write justify it color that a little bit   differently and give it a darker color because  it's a different type of total give it art and   format this I'll scroll up format that for you  overall alright there we go and put some lines and   our dotted line as our vertical now we just have  to add those toes let me raise this up so you have   a little more viewable area alright now we can add  in our totals our total work schedule would simply   be the sum of all the total hours say plus the  total breaks here sum of our total breaks and we   give arts our total hours which is 45 and then  of course we're gonna subtract the total break   equals and first we're gonna sum our breaks let me  get all of our break hours here so we have one for   day five total break hours and then it's a simple  subtraction equals total work schedule minus the   total breaks and that'll give us 40 which is our  total hours so now we've got our networking time   till two breaks so we're set up on the schedule  and it's very flexible let's save those changes   now we can move on to leave now a lot of you  mentioned many different types of leaves so we   want something that's really open and allows you  to put in all types of leaf so let's call this   annual leave because we're gonna annualize it it's  much easier when we Antal annualize our totals   it's easier to break it down for whatever pay  frequency you may have as there's different types   of pay frequencies so we're gonna have leave type  and then we need to know if it's paid or non paid   that's important for the payroll we also want to  know if it's clear annually if it's cleared that   means does it accumulate we can call it accrued  accrued annually meaning does it does it accrue   does it carry over if you don't use all of your  leave in a single year does it get carried over   or not so that's very important so we want to know  if it's carried over next up we want to know the   annual hours allowed and then we also want to know  how many were used that's important and of course   hours remaining so we can keep track of how many  hours we're remaining that's important all right   let's format this we're gonna use the same type  of formatting see if we can copy this over if it   allows us to do it if it's exactly and then we can  use that same formatting there we go that'll save   us some good time next up we want to include this  table probably till about let's say here we want   to keep that in fact let's just color these I want  to color only the ones that the users are going to   be entering data in here color that give that a  white for now and then format those those going   to be our user entry fields format those cells and  we'll put the border same type of formatting use   the dotted line for the center and use a solid  line for the bottom and then let's again use   conditional formatting so that we can alternate  the rows you're gonna get very good at this we've   done it so many times you know on our mod formula  I'm gonna keep a same consistency throughout   the table that's always important the fill fill  effects and then use our common light blue color   here in our fill effects okay not that one that  one there we go all right so we're good with that   now we've got that covered and let's put a little  bit of line here we can do this is kind of a nice   effect let's go a little darker on this one and  we'll do a conditional formatting because these   are going to be protected with formulas so we're  gonna also do the same thing equals mod but were   you can use a different color a little bit darker  of a color on this one perhaps something like this   yeah that'll be fine and then so that gives us the  alternating effect but it's a little bit darker   color so now we can see it's got a nice effect  now what kind of a leaf type this I'm gonna leave   open you can put in any leaf type you want paid or  non paid we'll use a drop-down list something just   very simple using data validation so that we know  if it's gonna be paid or not paid just a list and   then have both of those options available paid and  then non paid okay so we have those options and   then we can we'll be able to pull it in how many  hours you want how many hours allowed and true or   false something like yes or maybe yes or no if  it's a annually okay let's update the spelling   on that correctly with two L's so we can put in  just yes or no on that on the data validation so   that'll give us the ability to to automatically  know whether we should pay or whether it should   carry over or not so we can use that also I want  to put in a paid time off now many companies they   give you a certain amount of paid time off based  on the work that you're currently doing so for   example for every month you work you might get  two hours of paid time off so let's put that in   their earned paid time off it's also known as PTO  paid time off in many instances so let's create a   PTO status that means is it currently active or  not so that's gonna be and then we want earnings   rate per hour earning and this is going to be a  very small decimal earnings rate per hour worked   so for example it could be point zero zero five  so it means every hour you work you earn another   hour something like that it's usually a very small  decimal so that maybe you get one hour worked for   one hour of paid time off for maybe you know 50  hours of hours of work so it comes into a decimal   and then the annual max often there's a maximum  you can't keep earning annual max in hours so   there might be a maximum that you once once you  hit that max you can't continue earning so that's   important and then we're gonna have some we will  also want to know how much you've accrued accrued   PTO would be how much you currently aren't or  taking off how many you're used and what's your   balance so we need to know so let's say you've  earned ten hours into year but you've only used   five your balance is going to be you know five so  we want to put that in there let's format these   accordingly based on our format that we set up  previously in the last one cell styles and we'll   use a field label and then in these three we're  gonna because these are user entry so we'll put   our cell style as our field here and then we can  do the same thing here except we're gonna have to   recolor that we're gonna have to recolor that back  to blue because I don't want these are automated   and calculated so we want these actually to be in  blue so we're gonna put those back to back to our   background blue which is here okay though it's  good and we'll update the borders accordingly   make sure it's all formatted accordingly and then  we're gonna be done with the time off for now we   will come back to it later on when we're adding  data but I wanted to make sure that we have so   now we're gonna we've got to capture annual leave  let's copy this and paste the formatting here so   we can use the same formatting paste those formats  for that so it's a header alright that looks good   let's not lighten that up a little bit it's a  little bit dark we use this when we have two   tables I'm gonna use something like this so not  quite that dark just want to remain consistent   throughout the applications so that you get  a really good look and feel of a professional   application that's what we're creating we're  creating a professional application that can   hopefully be used around the world so we're gonna  try to keep it simple but keep it powerful so we   want that so a consistency consistent look and  feel is important for that alright now we've   got it and we've got our scheduling we've got  our leave and let's update there's some fields   I want to update with the general info as well  so let's go ahead and do that I've made some   updates some ideas based on your suggestions I  also want to know if it's active and I want to   know additional information so I also want to  add attachments here too so that's important I   want to know I want to add file attachments so  that's really important because I want to give   the user the ability to attach specific files to  a specific employee so we're gonna add that in and   it's kind of important because there's always some  hard document or some scanning document that you   want to do so let's go ahead and add that in now  file name we want to know the file type we can add   the file path that's very important entire paths  and perhaps who it was added by this is going to   be multi-user so we're gonna know who was added  by and when it was added added on believe this   blank I think will eventually use this field I'm  not sure for what yes we'll just keep that there   let's uh copy and paste this formatting I want  dis formatting our header table and bring it right   in here pay special and then format so we can use  that same formatting for our table yeah that's the   look I'm looking for right now I want to update  some fields here we based on your suggestions   we won't put gender in there let's put gender  here and I'm gonna put higher date that's kind   of important higher date make just some updates  of these fields make this position and I'm gonna   make this active I want to make this active or  not active active and so you can put inactive or   we could yeah this we can keep we can change that  up alright so let's do act that way you know if   it's active or inactive and position we'll just  put on or something like a manager so we've got   that and let's update those formats because we're  gonna add a few of them I want to base it on your   suggestion so alright good we've updated that  and now we'll go ahead and update this as well   so we have everything consistency now we've got  our fields we're gonna put our picture right in   here that's why I'm leaving this blank let's do  that now let me see if I have any shape saved on   here pictured no no other shapes and we'll create  our shape for our picture here you can bring in   some shapes that we have insert we want to put a  box for the picture and I want to put a default   picture there as well that's kind of important  when we put the size down there perhaps make it   vertically long and then maybe around one that's  a box I want to put that picture right here and of   course I also want to not put a fill in there so  we'll clear that fill out just the border and the   borders a little bit let's bring this down while  we're working with it and give it the same colors   of shape outline that's a little bit too big of  an outline I also want to create a that looks   good I also want to create a box for the to add  the picture so we're gonna copy this and paste it   or control-d works and bring this down I want to  make it a button down here and I want to be able   to add a picture with this button here okay so  let's bring in some shapes so what I want this I   want this button to be clicked when we're adding a  picture and I want this box to contain the picture   or contain our default picture so let's insert  that now I'm going to the pictures I've got some   saved up to save some time we're gonna use this  and then I'm gonna use these buttons I think at   some point so let's import those now okay so let's  bring everything over here we'll focus on just   this one this is our default picture I like this  so we'll size it accordingly all right and but I   don't like the black color I want to give it a  color I'm gonna color it's very close to what   we're working with they're just about there okay  now we've got our color set up and I like that is   a default picture and but it's I need to crop it  look it's just too big right there's nothing going   on so let's crop the picture and bring it just  to what just only what we need it cuz we don't   want it overlapping we just want the big because  it was a larger picture so we don't we can crop   it using this if you haven't used a crop feature  it's really really handy in Excel it's not a not   a bad cropping tool for for Excel okay and it'll  just click crop now we've cropped it good so now   when we click here it's not going to give us an  issue when we click the button so we've cropped   it we have our default picture I had some text  into this box add picture and we'll color that   a picture it's white now but won't change the font  color no worries on that to our default blue color   and I'm gonna right justify it so that we have  room for a butts and put that to center and we can   increase the font a little bit and make it bold  that's good oh there's one other shape I wanted   to in and but that's an icon so we need to go into  our all pictures and do actually all files that's   this icon here that I wanted to bring in so let's  insert that that's going to be used for our button   icon so we can bring that right in here alright  that looks nice we can increase this font a   little bit make it a little bit bigger that's good  alright we've got enough space so no problem we   can group these two I want to make a button out of  it so I'm going to group them now they're grouped   that's perfect and let's put give this a name  called this default picture this will show up when   we have no specific person or no specific employee  picture we can use this kind of signifies what to   do it's so it's a good place holder and as well  shows what the user makes it very simple for the   user all right let's uh now we can go ahead and  focus on that we can add a macro to this so that   we can actually add a macro and you'll you might  be familiar if you see my videos too where we're   adding and placing a picture because I've used it  with inventory in a few other videos so you may   be familiar with it well well we'll write it up  real quick here alright so into the VBA we'll go   visual basic and we'll create a new module we will  call this employee picture Max or whatever just   something specific so you can easily recognize  that we're gonna have a lot of modules in this so   you want to be consistent employee pick your max  this will cover all so basically this is gonna be   two macros one to add and one to display so let's  start on the add one sub add employee let's go   add employee pic okay that's gonna be our sub and  first we're gonna dim dimension the picture file   is a file dialog we need to know that alright  and next up we're gonna focus on sheet one so   let's with sheet one cuz that's what we're gonna  set that picture file ya equal to application   file dialog and then MSO of course we're gonna  do file picker we need that file picker that's   the one we're focused on so of course with that  picture file we're gonna do some things with that   we're gonna set a title for that and title is  going to be equals select this is going to be   the title that you shows up and employee picture  of course you can put any title you want here it's   none and we're gonna add the filters remember we  have filters we're only gonna allow pictures on   that so we need to add filters we need to add  those filters and what filters are we going to   be adding we're gonna add jpg JPEG I can add a  few different ones in fact let me just save some   time I've got it listed somewhere else I'm gonna  copy those from where I've got it listed and we're   gonna copy those here we're gonna say I'll give it  a name for that filter pictures I'll picture files   and then comma and then we'll just go ahead and  put those in okay so we've got that covered that's   going to help us so we need to know what those  filters are and next up if we need to know if they   have not selected what should we do if they don't  select or if they don't select it they cancel   without if dot show does not equal negative one  that means they have not selected anyone anything   then go to no selection just skips everything  else selection so right down under before the   end of the sub we'll just put no selection that's  gonna skip everything else in case they have not   selected in case they selected nothing so we've  got that so in case that happens all right next   up we can do sheet one we're gonna set we want to  set a specific range to put this file path we can   use J ten J ten is right in the middle of the  picture and we're going to eventually hide that   but for now we're gonna put it into J ten value  equals what does the equals we'll links without   that path is dot selection selected items which is  going to be that one first item and that's gonna   put the file name puts the file name in j-10 so  we know where that's going alright so we're done   with that and then it just says we have to also  let me just put a note here because we're gonna   add to this on existing not only do I want to put  it there I want to put it in the employees in the   employees database if we have booked right now  we don't have an employee are gonna be adding it   in very short and so on existing employees add or  update write add or update this is a little bit of   reminder for us the employee picture so not only  do I want to put it on the screen I want to put   it right into that whole file about any employees  file that's very important add our update employed   picture in database we'll just put database there  that's a little reminder we're gonna add that in   right now right a little bit later on but for  right now we'll add that code there so that's   good that's gonna that's gonna add the picture but  it's not gonna place it it's not gonna display it   it's just gonna put the file cough so now we need  to write the marker that's actually gonna display   it so let's write that sub show employee picture  now we can write that so we're going to focus   on that and of course dim the picture path as a  string because we need to know that path of that   patient we're going to define it as a string with  sheet 1 because we're focus only on Chi 1 for the   work and then what I want to do is I want to  if there is a current employee picture I want   to delete it so I want to write I want to remove  that but it could if there is nothing could cause   an error so we're gonna do on a our resume next  and then dot shapes because working with Chi 1   we're gonna call it this employ picture is gonna  the same name no matter what employee pic that's   gonna be the name of our shape and what I'll show  you that but right now we're saying if it exists   delete it because we're about to add a new one we  don't want to have multiple pictures with the same   name so we want to make sure to delete anything  with that name before we add it so let's delete   that and then basically says delete put a note  here picture if it exists and the reason we wrap   that in air is because if it doesn't exist it's  going to cause an air so on air go to zero there   and so that that way it'll start and stop this  when we wrap this in on air and resume next and on   air go to zero this prevents airs if the picture  does not exist if it doesn't exist it's going to   delete it okay now we can focus on now let's get  the picture path now what if that picture path is   empty first we're going to define the picture file  picture path equals remember it's J 10 equals dot   range J 10 so we know that that's the path path  of the picture okay but what if it's empty if the   picture path equals empty equals empty then what  should we do empty then well what I want to do   then is I want to show the default picture I want  to show that because remember we set that default   picture dot shapes default picture dot visible  equals Emma so true so we want to show that else   that means it's that means it's not okay actually  we can just exit the sub because there's not going   to be nothing and we're going to set the default  picture and then we can exit so that's fine we can   do just that we don't need to move on with placing  it because there is no picture all we do is show   the default picture and exit this up so we're good  with that now when we continue we can show the   default picture so now we can continue dot shapes  and then if we want to hide that in this case if   there's no picture we're gonna show let me just  go over this again if there's no picture we're   going to show the default picture and exit out but  if we go beyond this point that means there is a   picture so now we need to hide the default picture  equals false right so we do we have a picture so   we don't need to show the default picture so we  want to hide the default picture not delete it   just hide it just hide it and then what we can do  is with dot pictures insert what are we inserting   the picture path picture path okay good and now  with the shape we need to name that with dot shape   range range what are we going to do with that  shape range first we're gonna lock the aspect   ratio okay and next up we are going to set the  height and the height is going to be let's say   maybe about 95 or so and then we're gonna give it  a name dot name what is the name we're gonna give   it that employee picture it's gonna have that  same name no matter what employee kick that's   the name we're gonna assign to it so we want to  make sure that that's set okay so we've got this   and we've got this now let's close our end with  I think we're missing an end with let's take a   look there we go it's down there okay we're good  that's the end with okay let's just show that   this is the width just NART with the shape range  this is this with with the with the picture with   the pictures and this is the width with the sheet  just so we know sheet one just so we know Oliver   because we got three different widths so I wanna  make sure that we're focused on on the right one   okay now we now we've got it but now we need to  display it where we want to display it so now we   can focus on with dot shapes deploy pick that's  them that's the name that we've just said what   are we gonna do with that well the first thing  we're going to put it on the Left we're gonna   put it right around let's see let's take a look  back I think it's gonna be I'm gonna put it at   I I six or I'm gonna put it at I five and then  I'm gonna adjust it so we're gonna start out by   right around I five that's where I want to put it  so I'm gonna say laughs equals sheet one I needed   to use sheet one here I need to use sheet one here  because we're with the width is with the shape so   we need to specify she range i 5 dot left the left  of i-5 and then we're gonna put top and then also   we're gonna use sheet 1 i-5 as well so we're gonna  the top near that same equals u 1 in the top and   then we're gonna increment a left dot increment  left maybe around 50 because I want to move it to   the right and I want to move it down a little bit  maybe about 15 increment top so I don't want it   right at the top I want to move it down so I want  to move it a little bit to the right and a little   bit down so that's good with that and then I think  that's it let's take a look at that let's go ahead   and copy this I'm gonna cuz we're gonna use that  for our button so I want to copy that and now we   can assign the macro what we want to do is this  is the one this is the group not this one this   one we want to sign the macker right click assign  the macro and then paste it cuz you just copy it   of course we only have 4 macro so it's pretty easy  to find at this point okay well let's take a look   alright now we click on add picture and we'll  click on the add picture and we have got some   pictures here and click OK alright now we have  our range that's perfect just the way we want it   but now I need to know we need to run that other  macro and place that so let's do that ok we need   to mark that that's that last aspect we should  be true because we need to lock it so MSO true   that should be set to true okay and that's resume  this ok let's take a look alright that looks just   about good now we've got our picture let's add  another one just to look just so we can change   both of them mm-hmm add this girl here alright ok  good to change it over and now we have our link   and that's important but we don't want it I really  doesn't look ugly so first of all we don't want it   to bleed over so let's add a double spaced here  puts there and now let's change the font we've   got it so let's change the font here and make  it the same color as our background color so we   can hide that link there now it's hidden it's  positioned very nice and we can change it soon   so it's good now we are set with the employee  picture so we're done with that we've got its   place there and we're going to add of course the  employee database in sheet 2 and that we're going   to be able to continue with that by adding the  all the details from there so let's do that but   let's create some buttons first using our icons  here because I want to add new when a cancel I   want to be able to delete employees and do all  of that right in here so let's size these icons   accordingly we're going to group them and then  format not group them but select them within   a group 0.23 I want to make them smaller because  they're a little bit too big it's create a button   creating multiple buttons we'll start out with  just one get the formatting and the placement   just right and then we can duplicate that I want  it about the same height as this here just so it   everything's consistent that looks good and then  we'll format it consistent with our theme use this   blue and first let's add new okay and then now  we're gonna create this button and right justify   it and put it in the center and that's I don't  need I don't have so much space to work with   here so let's remove some of the padding on this  so go into the format shape and on the text I want   to remove some of this padding on the right  maybe 2.03 so that means our text is going to   be moved over a lot we don't have a lot of space  to work with on this so we want to and now let's   put the icon appropriately it's gonna be in back  so let's undo that and take all of them and move   them to the top we want all of them in the front  so we can do it with all of them otherwise we're   gonna have to do them individually bring to the  front okay that'll cover it now they're all in   front so now when we drag it over its going to be  on top let's zoom in take a look at that check the   sizing that looks right holding down the control  selecting both of them I'm going to group them   all right that looks good now we can duplicate  that lets duplicate ctrl d control D control D   so we're going to create multiple ones and then  of course we're going to remove the icon and all   right ungroup those and let's make this the cancel  new cancel new we need to need to make this one a   little bit wider this Ike this button here so  into the format we can also zoom in and drag   it one point one that looks good okay and we  use the correct icon for cancel which would be   this one here so bringing that up you have to move  that to the back let's move these buttons to the   back that's that's not that's go ahead and ungroup  these because we don't need them group right now   and then move these to the back all of them to the  back moving to the back here okay now the icons   are displayed and we can zoom in we don't need  to squid our eyes cancel new bring this around   we can remove move the picture down we don't  need that there okay so cancel new looks good   the sizing looks good move the icon over holding  to control selecting both we're gonna group that   and let's put in give us a name cancel new button  it's always good to name these and let's give one   of them we have our add new this is our original  add new so let's give this add new button okay and   let's also create a delete we're not going to be  putting a lot of text so we don't have a lot of   space here delete you it's just delete is enough  because we're gonna have tons of space change the   icon out using this icon and then we're going to  shrink that button down just big enough because   we don't have a lot of space are working with in  this instance and then we have one more let's see   what else do we need we need save new now for  existing we will going to be we're going to be   saving automatically but for new save new so we'll  use this icon for this we don't need this icon   can delete that okay so we have our buttons again  shrink it down just a little bit alright now we've   got our buttons all set up we're just gonna use  those four buttons kind of that covers the basic   properties we've got that we've got that now we're  gonna group them accordingly now we want save new   and cancel new those are both together right when  we can I want these two buttons displayed when we   have a new record when we're in the new record I  also want to align them let's go ahead and align   them in the middle and we'll group those because I  want those to be used so we're gonna this group is   going to be called new employee group I think  that's what I want to call it and we'll call   this existing employee group bring those two add  new and delete those are going to be I only want   those visible for existing let's shrink this  one down a little bit we don't have a lot of   weakening we're going to increase the column just  a little bit as well we can do that and then we   also want to make sure to set the properties on  these holding down the control let's align them   vertically align the middle and then group them  and we'll call this existing employee group okay   did I use an L for here nope new employer okay  those are good now we also want to place those   right on top of each other and that's too big  we're going to that's okay for now but we'll   want to place them on top of each other because  only one is gonna be displayed at the same time   so we can do that that's no problem for now let's  align them align the middle so everything set up   and let's move it over here just wanna okay that  looks good let's change this to him let's shrink   this down just text here just call it employee ID  that'll give us some more space employee ID okay   that gives us some more spacing so we have that  alright we're looking good on the buttons there   and this picture can move back but it's gonna  place by itself we don't need to worry about   that now we've got our and set and we're gonna  change them based on the based on that and well   one more thing we need to do we want to make sure  that we saw we position but if we don't sighs so   right clicks when we change those column I don't  want the buttons to size size and properties and   then under the properties we want move but don't  sighs we don't want to size if we increase these   columns we don't want the buttons to change so  that's important all right next up we're good   let's go ahead and add our employee list right now  we can go ahead and create a new sheet starting   with sheet 2 let's call this employee list and we  can also name the current employee she just call   this employee manager for now we can change it  we're not going to use sheet names within the code   so we can change it at any time employee sheet  here we go we'll start out I want to create this   first row as probably our data mapping so we can  easily hide it so it's just give that a color of   gray this is something that's going to be hidden  we're gonna use this for our data mapping and I'll   show you what that is then of course Row 2 we'll  start that out of his employee list and then our   headers are going to be covered there so we have  that I've done this in a sample so I want to save   some time because I've got a lot we're already  now here so let's I'm gonna pull up a sample I   did recently and I'm gonna copy over some data  and then I'll just go over it with you on that   here's the sample I'm working on I'm gonna take  a look at this we've got it all figured out we've   got our mapping I'm gonna copy this also save us  a lot of time I'm gonna paste that right in here   and then let's go over there and we can know it's  close out I want to close out the sample too so   we don't get confused which one we're working  on we're gonna we're gonna use it again in a   moment when we call it data mapping but so this  we have this is one we've been working on and now   we've got our Employee List here now what this  is I've got a bunch of employee IDs here which   we're gonna need we've got a last name first name  gender all the fields that we have and now they're   mapped individually let's show you that if you  want when you do your mapping try it this way   pull it up so that you have everything in the  upper left corner and use your favorite screen   capturing software how'd you snag it and so all  I'm gonna capture a screenshot just like this and   then what I'm gonna do is I'm going to copy that  and I'm gonna with my software I'm gonna copy that   I'm gonna bring it right into my Employee List and  I'm just gonna paste it here temporarily and this   really helps for mapping so now we've got last  name we know that's an f6 so we put that right   here excuse me f6 right here so we see that now  we've got address we know that's an f8 so we can   put f8 so it really helps you don't need to be  going from sheet to sheet so you can look at the   screenshot and it really helps mapping so address  is in f8 city is an f10 so every column is gonna   be mapped out and this is gonna really help us  reduce the amount of code when we're bringing   data back and forth from the employee manager  sheet so we see and we just continue on and so   for everything we're gonna map out the data now  I've already gone through otherwise you're gonna   watch me type all this out and it's gonna be a  lot I've gotten a lot of test data in there so   basically I've mapped out f-16 here of course is  our active or status let's call this status okay   that's what I want to call it shouldn't be called  active should be called status so let's go ahead   and mark that status indeed the employee manager  status so that's our status okay cuz and so that's   what I've done and then when you're done with  the picture just delete it so we've mapped it   out mapped it out alright now here's one issue  now I've got an employee name so I want this to   be in a drop-down list but I want the entire name  and I want it to be last name and first name last   name first name that's what I but here we have in  our last name and first name is separated in the   fields so we need to combine them so let's do  that in fact I've already done it if you look   over in under BB calm BB you'll see that this has  a formula right here it's if B 4 which is the last   name B remember B is our last our last name column  is our last name column if it's blank then return   blank but if it's not blank what I want to see is  the last name before and then a comma and a space   and C for which the French so that's going to  return our entire name here and then what I want   to do is I want to do that for everyone so we'll  copy down I want to do that for every single one   all the way down so we're gonna create that and  so cuz this is the list that I want to use now I   need to create a named range let's go ahead and  look at that into the formulas name manager now   when I copied over the sheet week I got so we only  have time so let's do that employee name so let's   create a new one we're gonna call this employ e  name and I want to make it an offset formula means   I only want to show the values I don't want to be  showing a bunch of blank at the end so we're gonna   use offset so we're gonna call it equals offset  and this particular offset because it contains   formulas we need to count the blanks I can't use  count a count accounts text but all of my fields   even those with nothing have a formula inside so  I can't count text like account cut out blank so   I'll show you how that's done we're gonna start  out at BB four that's gonna be our first that's   gonna be our first row of D let's copy that cuz  I'm gonna use that in just a moment partially so   we're gonna copy that and now what I'm gonna do  is I'm gonna place three commas for our offset   one two three and I'm gonna what I'm gonna do  is I want to count the blanks now so in fact   the blanks is going to be the total we're gonna  start it out let's say nine nine six and I'll   show you why that's important 996 - 996 is going  to be all the rows - and then what we're gonna   do is we're going to count the blanks I need to  know the blanks because it's not the blanks that   we want it's the opposite it's those that's why  we're using subtraction count blank and what is   the range that we're gonna be kind of like let's  use nine nine nine okay so we'll go all the way   down to row nine nine but our data starts in row  four our data starts in row four that's why we're   using nine nine six comma one okay let's take  a look at that let's expand that out so you can   look at that and bring it all the way over into  offset Employee List nine nine six - so we're   gonna use all of the range the entire range what  we're gonna do is we're gonna count the place and   let's see if that's accurate and we'll tab over  there well highlight we'll scroll all the way down   that's perfect right our starting we're using 996  because we're starting in row four that's why we   have to subtract from 999 so that's gonna work out  perfectly because if we use count a it's not going   to work we have formulas so that's perfect when  when our contains formulas when our range contains   far those we can count blank okay good we're now  let's do another one let's copy this I want to do   it for employee IDs - so when employee IDs there's  no formula so we can use count a new employee ID   and what's the employee ID we're gonna I'll just  paste it it in but we're going to use offset in   a column a in this case we can use count a there's  no count a list a four through eight nine nine and   let's take a look at that now for this employee ID  when we take a look at that there that starts out   at four and goes all the way down to currently two  twenty three so as we add data this range changes   to have two so this one uses count day because  there's no formulas and this it's just straight   data so we have an employee ID an employee name  okay good let's close that out and of course I   added lots of sample data and don't worry about  the conditional formatting it does need to be   updated here it's kind of a mess the formatting  but we'll worry about that a little bit okay so   let's go ahead and add a data validation here  home add data data validation and we want to   include this employee so it's going to be a list  an employee name equals employee name okay good   so now we've got that and now we see this contains  our employee name and it goes all the way down to   the last one great so now what do I want to happen  is now when I select a name here I want that   information to fill out and that takes now we need  to create some macros that do that so let's go   ahead and create those macros now so we can load  that employee data but before we do that there's   a few additional fields that we want to create  here we need to know some certain information I   just know if that employees loading or not that's  gonna help us so let's create that it's gonna be   a true or false so we'll set it to false now we  have a slide to call me other selected row we need   to know the employee ID row and the employee name  and I'll explain why that's important a little bit   later on but we're gonna need that and employee ID  list that's employee ID row although they should   be actually the same but we'll need them both  employee name row so we want both of those so   there's gonna use match formulas and we need to  know if it's the employees new or not so if we're   sending a new employee we just know if that's  true or false now employee ID row is going to be   based on this so we're gonna use a match formula  for that but first we're gonna wrap it in and if   they're equals if air and the reviews match what  are we matching we're gonna look up this employee   ID and what do we what's the array well we just  set that that's the employee ID here and of course   we want an exact match so let's put 0 there and  then comma and then if there's an error it's gonna   be double quotes okay so that's 8 let's take a  look at that that's not perfectly accurate just   yet one zero zero five one zero zero fives in  this row 11 it's not eight we need to add three   because our starting four so we want to make sure  to add three then it'll be accurate so plus three   great okay let's copy that because we're gonna  create a very similar formula for the employee   name except in this it's not gonna be j2 it's  going to be this and it's not gonna be employee   ID it's going to be employee name everything else  would be the same ok so now we've got the right   agent forces in Row 4 and Giorgione Zeon is in  Row 5 ok that's good and they're different now   but it's fine because we have a different place  one zero zero one of course would be four one zero   zero two everything's working just right that's  the way we want it alright so moving on we have   all of our information that we need let's color  those so we can these are of course are going to   be hidden and we'll put some borders around it  just so we can show and display those distinct   so that those clear give it a different distinct  of course these columns are going to be hidden so   that'll be important that's why we colored them  differently and they're very different because   we're gonna hide those and protect them okay  now we are ready just go within the code let's   go into the developers into the Visual Basic and  we'll create another module we have to let's see   let's title this module we need to always give  our modules name so we'll call this alright these   employee pictures and the other ones tab but name  those wrong pics max okay and then these are the   tab back so let's set those right and double click  on those and here these are the tabs so let's set   those employee tabs okay now we've got it set up  right and now we can create a new module insert   module and we will call this employee okay  and then miscellaneous because we're gonna   be putting lots of different ones in this lawsuit  macros the first of which we'll probably put save   new so let's I will add employee new employee load  sub employee load we want to load those employees   bring them all the data from database in to the  actual information so where we need to dimension   both employee row and the employee column both of  those are important employee row as long and of   course employee call them as well employee column  as long to separate rows I'm gonna do it right   first of all we want to make sure that we have or  with she one will be working primarily with she   one so we want to that and we need to check we're  gonna be checking b5 we need to make sure that's   not empty b5 is based on the name so let's look  at that again b5 right employee named ro know that   when they select the name right when they change  name this ro changes if they put in something   wrong right cancel so it's gonna be blank right  we haven't allowed anything but their name so   but it's gonna be blank if it's not accurate so  we need to say if it's blank then do something   that shouldn't be blank but if it is we need to  compensate for that within the code because we   definitely need an employee ro that's critical so  we can say if dot range b5 equals empty and we've   put a little message box then message box please  enter a valid employee from the drop-down list okay that way and then exit the sub we can't move  further unless we have a good employee accurate   employee all right now we can continue first of  all we need to set b1 to true and the reason for   this is we don't want anything else to happen  while we're loading it we don't want to save   the database so we need so while we're loading  it we need to set b1 to be true and I'll show   you why that's important in the future but we want  to sit be well at least while this macros running   and what after it ends we're gonna set it to false  so we can set it dot value equals true and then we   can we're gonna set it false before it in so let's  do that now just so we set it and then we'll give   it a name just so we know what it is set employee  load to false and of course this one's going to be   true just so we know properly notating what we're  doing in the code because sometimes we forget I   know I do too true okay so we've put those in we  know we're setting that up and that's important   because we don't want thing else to happen while  we're loading it and because we're gonna make   changes that we don't want so we're good to that  now what I want to do is I want to go through I   want to get the employee room I want to get the  employee calm and I want to run through all the   columns they said the what I want to do is I want  to go through column 1 all the way to 28 and let's   go down to the bottom just so you know where we're  headed equals column so it's column 1 all the way   through 28 I believe it's a a a 28 right that's  our last column right so we're going to whoops   zoom that in not necessarily let's zoom out a lot  okay so 28 is our last column and a B is our last   column with data the last column of data where  it's gonna have the thumbnails that's 28 right   column 28 so I'm gonna loop through every single  one of those columns 1 through 28 we don't need   this anymore and what I want to do is I want  to figure out what bro and I want to take this   and put it in the right place I want to take this  and we know what right place to put it is because   we know that the first name goes in h6 right we  know the first name goes in in h6 right so we've   mapped it out properly so all we need to do is go  through this row once AJ to f6 put this name in   h6 put this or whatever it is so we just get the  employee row we know the employer room we need to   look through all those so that's what we're gonna  do if we're gonna do right in the code we're gonna   do it right now so we need to set that loop so  for employee column equals 1 to 28 you now you   know it's gonna run all the way to call them a B  which is 28 so that's going to be our loop okay   equals 1 to 28 that's gonna be a loop and make  sure we do we close our loop next employee call   them and write everything in between so that keeps  it clean so for that here's what we're gonna do   dot range of course that's sheet 1 so we've always  had that now what is the range what is the range   well the range of that is is going to be this or  this or this that's the rain so we know the range   is in sheet 2 Row 1 Row 1 and then we just have  to change the columns so that's all we're gonna   do here we know we need to get that range so let's  do that so we know it's sheet 2 and because both   the column and the row are variable we need to use  cells cells so what is the row we know the rows   1 right what is the column the column is going to  rotate employee column so there we go now that is   what is the value of this this is the range right  so that's the range so let's close that dot value   right so for example right here this is going to  be H 6 F 6 B 6 or whatever it is and then we're   gonna run through that and this equals what is it  equal well first of all we need to set let's go we   need to set here before we do that when you set  the employer-employee row equals remember it's b   5 not range b 5 k now we set the employee row so  now we know equals sheet 2 and dot cells what is   the row the row is the employee row what is the  column the column is employee column dot value   that's how we do we just run through that loop  and all of the data makes it 2 so you see it's   a little bit more work mapping but the code is  super simple otherwise if not this we'd have to   write 28 lines of code it would take a long time  so we're changing because we've mapped it out   we're now taking 28 lines of code and replacing  it with just three lines of code because we've   mapped it so it's a huge savings especially when  you have a lot of data all right now we're good   now what we need to do is we need just now that  we've loaded this we need to know that this is   in existing but we have our button sets here we  have our existing employee group and we have our   new employee group well since we're loading it  it's not a new employee right it's an existing   employee so we need to hide new employee group and  we need to show existing employee group so can do   that with this dot shapes and then we're gonna say  new employee group it's not a new so we want to   make sure this is not visible visible equals and  there so false and of course the existing employee   group we want to show that exist employee group  we want that to show because it's an existing   employee this is why naming is so important so  it's clear mmm it's so true okay so that's going   to be true and now we're set B 1 - false good  we've covered that and also this is not a new   employee right because we just load it so B 6 is  all so let's copy this B 6 remember we set that to   new employee or B 6 set new new employee to false  it's not a new employee sometimes we need to know   if it's a new employee but this is not because  we just loaded an existing and also if there's   an amount to show the employee picture right we  have employee pictures we want to display that   so let's copy that show employee pic and we're  gonna paste it because when we load it employee   miscellaneous ok we want to we want to run the  macro that shows that picture so that's it that   is all we need for the employer to see how small  that little macro is all that macro does loads   all that employee data let's go ahead and save  our project I would like to say before we run   macros so let's go ahead and take a look at that  and so when we change this I want to when I make   a change we're gonna move these over it's you  know it's too far over ok we don't want it we   don't want it on the drop-down list when we make  a change to this we want to load it and we change   this so let's write some code now to do that back  into the VBA under the employee manager now we're   going to write some cells so far we have selection  change but we want worksheet change this is what   we're gonna make an actual change so worksheet  change is important so let's go ahead and write   some code for that ok it's a relatively simple  code have to do is right and this is f2 right   if not intersect target , don't forget to come  here Range f2 is nothing then what are we going   to do well we need to make sure before I before  I just load the contact we need to check for two   things I want to make sure it's not blank if so  we need to write another if statement if range F   2 dot value does not equal empty we need to make  sure it's not empty and we need to also make sure   that b5 is not empty b5 is going to be the row  we need that row remember the employee row so   we need to make sure that's not empty as well if  and range b5 our employer row dot value does not   equal empty good if though both of those things  happen then we can run the employee then employee   load now we can run the macro play load so now  let's go ahead and try that out and now when   we change an employee all right we got ok let's  add a tea to that next employee and run that ok   good now it's now it's running there we go perfect  and we've already added in some some information   here good there we go our default picture because  there is no so you see that just a little bit of   code adds in all the data we want that's great  ok let's focus on our add new and I know this   trainings going quite long today but we had just  have so much to cover it's a big project we've got   a lot to cover so hang out hang in there we'll  get through it and moving on letsa added now we   know our employees load is let's go ahead and  add in macros for a new employee and I've done   this before so I'll try to add this in a little  quicker by copying and pasting sub employee new   ok we're gonna add some with sheet 1 and then we  also want to again be one I want to mark is true   b1 equals true so that's important because I want  to show it as we make changes we want to that's   in the employee load we want to make sure dot  range b1 equals true employee load we're gonna   mark that as false at the end of this macro again  do the same thing as we did before so we can just   copy that until they load to false before the end  of it and then everything gets written in between   okay all right so we always want to set because  we need that when we make changes it's important   all right now we can continue writing our mecha  Mesa Kalia do is I want to display I want to do   the opposite of this so let's copy those and just  do the opposite we're going to the new employee   group we're going to show C true we want to show  those and we want to hide the existing so that's   important and we also want to remove that we want  to show the display picture the default picture   we want to show that on new employees because  there's going to be no picture if we want to show   the default picture so let's do that dot shapes  default picture dot visible equals true we're   going to show that and we also want to delete if  there's any existing picture any existing employee   picture we want to delete it we want to remove  any employee picture and we want to wrap that in   also on air just in case there is no picture so we  want to do is dot shapes employee CEM PL m8 I want   to double check that pictures make sure we get  the name exactly right and empl picture okay so   we've got that's the picture we've assigned to  that I want to make sure we got the name right   here empl pic and then dot delete and then we're  going to also on air go to 0 okay now we've got   that set we wrapped it now what I want to do is I  want to clear all the fields and there's a lot of   them so it's just I've already written this before  I'm just gonna paste it in here all all of those   fields were clearing the contents these are the  all the fields I need to clear all the employee   fields so that's all that that is and there's  actually more because there's so many in the   employee so let's go ahead and copy that into  this is basically these lines of code are just   gonna clear all of the employee fields clear the  contents let's remove that let's shrink this up   so we get some more space for writing code okay so  now you can see everything all right next up what   we want to do is we want to set the new employee  to true and we set that be six we can copy this   but just change it to true here because this is  a new employee so we want to make sure to mark   that is true b6 is going to equal true and set  the new employee to true all right so we've got   that and also when we create a new employee if  we're on another tab I want to make sure to set   this tab automatically right because if or this  is the first tab we're going to focus on for new   employees so let's select with the code efore and  then also I want to select f6 because I want the   user to start typing in the last name so when we  do new employee I'm gonna select this tab and then   we're gonna select this cell so let's write those  into that code now so dot range efore select and   then okay good so now that selects the first let's  just write it down selects general info tab and   then dot range the first cell we want to start  typing f6 and that is going to be the last name   select last name field ok very good now we've  got the last name field selected now we've got   employee new and we've got this employed load  to false so that's good that's exactly how we   want it to go and let's assign those macros back  in to that we have our add new so let's use the   ctrl click the button and click the icon right  click assign macro employee new okay before we   run it save our code in case we have any errors  or major problems okay that looks good we have   our last name or first name and it's automatically  selected that's great now we can ready now we're   ready to type in the new name we haven't created  any save new macros or save anything else and   what we'll do is we will continue that we've  already gone way over here with an hour and   a half I don't want you to fall asleep we're  going to continue that in part 3 so thank you   very much for joining me I'll do my best to get  all of your suggestions in so please leave your   comments whether it's Facebook or YouTube and also  if you want me to keep doing this and create an   amazing application please share please like this  show your support so that I can create an amazing   employee manager with with your suggestions  so thank you very much for joining me today

Channel: Excel For Freelancers

Views: 126,263

Keywords: Excel Employee Manager, Adding Pictures in Excel, Employees in Excel, HR for Excel, Excel For HR, Adding Employee Records, Storing Employees in Excel, Adding Employees to Excel, Manage Employees in Excel, Excel Employee Mgr, Excel Staff Manager, Manage Staff in Excel

Id: lba5z5RlzxI

Length: 89min 16sec (5356 seconds)

Published: Tue Sep 18 2018