Show all open attachments we have a new events tab where we're going to be going over that in the employee manager we have also made updates to the Employee List we have added an employee archive to archive employees so it's going to be an epic training I can't wait to show you all the new features let's get started all right I'm excited to bring you this week's employee manager we have so much to cover in fact so much I've made a list so here's our list of things we're gonna try to get it all in will probably go well over an hour in this training so make sure you have your.
Coffee get ready and let's get started we're gonna cover the attachment open that's something that we did not cover with the attachments so we want to make sure to cover that as well as delete so when we delete attachments we have that macro assigned now and completed so we can show that to you events sheet I created a brand new event sheet and it's in the design phases right now it's not functioning yet in fact before I make it completely functioning I want to get your ideas your feedback and your comments on this we have so far an event.
Name the event type where we have a list of types and these lists originate from our new admin screen where we have different event types so we can add from there we also have an employee and assigned the ability to assign an employee so that's going to help us and we have a created on date which will default to the current date and time when you create an event or when one is created automatically based on the recurring status we have events notes we have a really cool function an ability to add checkboxes now I wanted to show.
You a different way to add checkboxes in fact this is a single cell a single click on this is going to show you how to do that and it's really cool I know we have the ability to add checkboxes through both form controls and ActiveX ActiveX has some issues with mac form controls doesn't really look very beautiful I don't really like the way the check box style is here so I wanted to just try something different now checkbox would work just fine of course but you know me being different I want to try things.
Differently and show you a different way to do things it may be a little bit more complicated but it's very cool because you can use just one cell and it doesn't use any form controls it doesn't use any active cells so I'm excited to show that to you along with the conditional formatting that shows you this and the same thing for reminders and of course reminders we have the ability to add emails and so if we add emails that we become active then we can add a most this is not functioning yet as far as because I wanted to get your feedback let's see what else we should add to.
Delete employee but I think most likely we don't want to delete employees we want to archive them I want to remove them from this list but I still want to have them there are information available in case we need it in the future so we created a brand new list called employee archive and any employee that gets archived will be located here in the future we can't have a wait we can have a button up here that says add back so let's say an employee leaves and then they come back I would love the ability to add this employee back to the main employee list here so I think we're.
Going to add that so something like a button up here that just says add back to employees or non archived or something like that and that'll put their back inside the main list so that they're available in case the employee returns so that's an important feature I'm going to show you how we did that also we have let's go ahead employee named range we have that and delete so we're going to go with that we also have the ability to search for employee just by typing in so we.
Have the new that just by typing in I'm going to show you that features a really cool we did have that request to search now in this particular training we have so much to cover that I am going to I've already done the work but we're gonna walk you step-by-step through what we did as opposed to me creating it now I know you all prefer me to do it live but we have so much to cover and you know this is going to be a 20 part series unless I do some of the work beforehand and walk you through steps of course.
Many applications I will create them live just for you to see but in this one this feature look at look at all these features it would take me 10 hours to show you to create these so I will walk you through step by step of what we did it'll save a little bit of time and it'll show you how to create lots of features we also have runtime macros I'm gonna show you that but just basically speeds up our code so we'll show you that employee ID search I just went over that a group hide buttons on tab change we've grouped so in other words I've.
Grouped these into a group here so we could hide those and we also have the ability to add a payroll history and I've added payroll on to these so that we can show the from and to date both in the payroll history and the time clock history we generate payrolls and that's going to be great let's take a look at this I want to show you this feature we're gonna delete these just for now and we have the ability to create pay periods in fact let me extend this shape that's gonna be a little bit wider there.
Alright pay periods and so we have the ability to create pay periods which is
Going to be a really cool feature so that when we create pay periods we can create them at any frequency so let's say we have a weekly pay period and we want to create 10 peers so we're going to start it all in January first we have that ability just to click the button we can add create a new list or add to the existing list creating paper it's is going to automatically create pay periods based on that so I'm really really excited to show you that feature.And then once we do create those papers they become available in this list right here so we can easily create from - and create advanced filters based on that it's really powerful really really powerful because it's flexible for whatever your type of pay period is we can create those some pay periods we're gonna use those for creating payroll we're gonna use those for creating time clock because the time clock history will also be based on those from in to dates so it's a really great feature.
We're gonna get to that soon let's see what else we've got we've got so much to cover employee status name range these are all in the admin screen which we created time clock history we've gone over that I just said add to archive we've gone over that briefly hourly or salary option that's kind of a cool thing let's take a look in the payroll detail earnings detail now we have payroll type we have salary and hourly but if it's salary there's a frequency here right so if we've entered salary we.
Have a specific frequency here that we may want to add in and we have a salary amount here but if it's hourly this field shouldn't exist right so when we change this to hourly I want to use conditional formatting to clear that and I also want to change this label based on this so we're gonna show you that real quickly and we'll get to all of that I believe that is it let's take a look at that and the event screen design so we're gonna cover that let's get started we've gone through the summary.
Of what everything we're gonna cover let's start off with the attach open that is the first macro and sheet that I'm gonna show you so let's do that now we can add the ability to add an attachment of course we went over that and we went over that and we'll go ahead and let me update this that's too big so let's take a look at what that is and we've also added the ability I forgot this we had so much let's take a look I've also added the ability to preview larger files like PDFs let's take a look.
At that we'll find a PDF that we can show you we have the ability to preview that let's take a look at that PDF and we have the ability to preview PDFs which is going to be a really cool feature on a larger scale and you see here now we have the ability to preview a PDF just by selecting it so we have that I've also got that for a Word document - I almost forgot about that so we've got that and then the pictures of course let's go ahead and set the size for those pictures that's too big from testing into the developers tab we go Visual Basic all right into the employee.
Attachments here we have display thumb now let's go ahead and set that to 90 so that'll be a less of a width and a little bit less of the height and now when we take a look at that there we go now it's set up all right so now we're also going to show you the preview we also have the ability to do that with a word document - lets go and see if we can find a word document and oK we've got that one here and we have the ability to preview a word document and that'll position itself over on the right so we've got previous four word documents we've got previews for PDF.
Documents and we've got previous four pictures now now I left off Excel Excel was a little bit slow you can try it and we'll go over that code but I found that a little bit slow and not really workable very well so we kind of left that off as far as previewing excel files but PDF and Word the most two most common we have those available so I'm gonna show you that as well so let's get started on the attachment open let's go ahead and go into the VBA and basically what that does is it lets us click on it.
Click on any line item and click open and it's gonna open the individual picture in a larger file as you can see here and that would work for any type of file even a PDF so if we were to select on a PDF and then click open it'll actually open that PDF so we can see that here you know in a little bit larger form they'll reduce that so it's gonna open that and we do that through a macro and once again right-click assign macro and I've already assigned this macro to a mad girl called attached open.
So let's locate that macro see how that works it's crew very very simple so into the employee
Attachments here and if we scroll down we see something called attached open so let's focus on this macro with sheet 1 of course we're working on sheet 1 that's the only one we want to make sure that we actually have a value in b7 that is the row that is the attachment row that we've selected let's go ahead and review that every time we select a specific attachment we are going to be.Able to put that row through VBA code in b7 here b7 changes and of course if we select outside of it it disappears so when we selected b7 equals 21 that is the row that we select so we need to know that row because we need to pull the file path from column G so we need to know that row G and the row will give us that file path in order to open that document or that file we need to know to make.
Sure that we do have that row so that's important so if it's empty if b7 is empty then it says please select an attachment to open for example if we try to click here and be someone's empty and then we click open attachment we're gonna get that message please select an attachment why because b7 is empty so that's how that works moving on if now we have the attachment row we can assign that to b7 so it's very simple it's pretty much single line of code G remember column G that's where our file is located our file path is.
Located and the attachment row that value that is our full path all we're gonna do is follow the hyperlink and that will open the document regardless of the type of document whether it's picture word file excel or whatever it will open that up so it's a very very simple code we just need to make sure that there's a value in b7 so we can pull that row we've assigned that alright moving on to attachments to Lee we did not cover that last time so let's go ahead and cover that again we're gonna define two different variables and.
Employee ID and the attachment row so we need that employee ID may not be important at this point but the attachment Row is so if first of all we want to make sure that the active cell row is not empty the j.jz excited why is jaye Jaye's the attachment row that we have signed we need to have that here that has been assigned right it is here now before it was a difference so J is very important we need that this is our attachment row you may want to hide these remember once.
You have this set if you're going to be distributing these you don't want these numbers to show up simply just change the font color to the same as the background color here and of course it will disappear but we need those those are the rows in the attachments file for example 12 if we look at the attachments we see row 12 this is the profile jpg this is it this is the file so if we're gonna delete this row we need to know the row number and that row number is stored here in J and the active cell.
Column active cell is the one you've selected so that's important we need to get that 12 so we know what row to delete all right back into the code so for example if J in the active cells empty areas can say please select an attachment to delete first if it's empty so we need that next up we've got an on-air resume just in case there is no attachments thumbnail to delete okay we'll want to delete any thumbnail that's displayed because we're gonna delete the file we don't need to show the thumbnail as well so we've deleted that and then we're gonna set the.
Attachment row to sheet 1 J and the active cell row we could just as well as use b7 here as well because that's so it's just a different way to display that but b7 also includes that same row however we're just showing you a different way to do that as I like to show you multiple ways to do the same thing so you can choose which method is best for your project next up all we need to do now is delete the actual row in sheet 3 that is our attachments that is our attachment sheet.
He or she 3 and I was like she's sheet 3 because we don't want to use the name of the sheet because that might change this could change names so we don't have to change the code if we stick with the sheet number and the attachment row and quotation marks : and quotation marks and the entire entire road delete next up we're gonna run the attacks refresh macro and that removes that's gonna run that advanced filter and remove that actual line item from the attachment list and next up we'll select.
A nineteen to select and display whatever thumbnail is in that if there is any for example if we were to delete this document here we'll go ahead and select on that and then we'll go ahead and click delete once again right-click assign macro this is the macro attach delete is the one we just went over that is assigned to that button and icon so when we select a specific line item we click attachment delete it's automatically deleted and then of course the nineteen is selected.
Here which displays whatever we have selected in this case it's a PDF so it I've displayed here you know I didn't want to display a small thumbnail it was almost especially for something as big as like that because if you look at if we reduce that size down to just a thumbnail it's gonna be pretty ugly and almost useless right because if it's a big right that's almost useless so I decided to make it big for for PDFs and Word and then and then bring them here so you could actually see what's in it now this functionality we did go over in.
Our file manager if you remember that we did go do a file preview so if you want to learn more about how to preview these files we did go over that in our file manager service so you can check that out on YouTube so that's a great feel so where we actually were able to preview file larger files like PDF Word Excel and of course pictures videos and of course sound type of AVI so we covered that as well in the file manager alright let's dive down into the macro that creates these previews for both the PDF.
And Word documents and we'll take a look and to see how we did that so into the VBA under the module employee attachment we have the attached display thumbnail now we have this before and we had the picture file but now we're going to focus on additional some code that we had additional now you'll see here if have the file type and the file type is defined here for F in the attachment row that's gonna get us our file type back in the file you'll see our file type we'll scroll it over to the left a.
Little bit you'll see our file type is located in F so we're focused on this file type so we've defined that into the file type variable here and so once we set the file type we can determine if it's jpg PNG gif jpg or BMP those are the picture type and we can set this picture types we can then go ahead and do the following which sets it as a picture and puts it in a specific position on j19 and then of course the.
Specific size either 90 maximum on the width or 80 maximum on the height and then increment left move it over a little bit to the right and just a little bit down however else if we're using the else/if file type is do C X document or PDF or do C then do the following and of course on-air resume next just in case there's an issue with this and basically what we're gonna do is we're gonna create an object and olee object we're gonna add on that and it's gonna.
Be based on the file path and of course we define the file path here in G which is a file path of the attachment we're gonna we're not gonna link it and we're gonna we're not gonna displays an icon is false we don't want it and we're gonna give it a name and this name is gonna be the same exact name that we would give it as a picture the reason we give it the same name so that we cannot delete it or hide it and we would be hiding either the picture or the.
Thumbnail attachment or excuse me the PDF or word attachment thumbnail a larger thumbnail picture so we can hide it using the same name because both of these are can exist at the same time it can be either this or this so we can assign the same name and then we can delete it also using the same name and then of course with the shapes now it's the shapes and we've attached a thumbnail just as we did here except we were going to place in a different position as you know our attachments of course for PDF and documents gonna be over to the right.
They're gonna be largest we're gonna be basing that off I believe 0:04 which is the anchored cell and then we're gonna place it over there so we're gonna use excuse me l4 we're gonna use l4 is the anchored cell and then we're gonna place it over that so we're gonna set l4 is that anchor right that's where it's gonna be located then we're gonna move it a little bit over to the right once again we're gonna display the height and the width based on so we're gonna say if the.
Width is greater than the height then the width is 300 otherwise the height is 500 and basically what this does is it sets maximum so it sets the maximum height of 500 and the maximum width of 300 and it keeps that aspect ratio we want to lock that aspect ratio so that we don't get some funny types of aspect ratio so that we cannot read we want the aspect ratio to remain that's very important so all we're gonna be doing is setting it based on l4 setting that picture okay it's now a picture or our.
Shape and then we're gonna set the width and then we're gonna move it a little bit over to the right from L from l4 and a little bit below the top of l4 so that is it that's all we have do it now if you wanted to try Excel you can just do something like or file type equals x it will work but not very good depends on the type of file XLS X or X I'll ask them you can do that and try and see how it works I had some issues it was very slow to open and it just wasn't great.
Right it kind of works but not not great so I kind of left it out but you can try it you know and if you're opening simple very simple basic Excel files it probably would work okay if you're opening up files that involve macros or larger it probably would be too slow so it's kind of something you can try to see but with Word documents and PDF the works pretty well so for that that's fine all right great I'm glad we got that covered let's get back into the application now and go on from there so moving along we have that now we've covered delete we've covered.
Open so we've got those two things and let's cover the event sheet let's go ahead and go over so we can do this systematically what we have covered we'll change the font on that so we can go event sheet alright we've got that I've got both of them here the sheet and the design let's go ahead and go over the events part in this payroll manager here we've added a new tab and you'll see everything has been switched over and everything's been redesigned based on this new tab so I've extended fields here to make more room and I've.
Completely redesigned it and I've call so updated the mapping now when you update it I've updated this mapping for the proper columns in our attachments you know our Employee List sheet here so all the mapping has been updated accordingly and we have added in a few small things but everything's been updated so this mapping has also been updated and please make sure if you want to add columns add rows this mapping here must coincide f/8 for example in.
The dress must coincide with the mainsheet here for example f/8 address f/8 must coincide always with this here f/8 so make sure everything as well as mapping if you're going to be adding columns or adding rows this much so f/8 for example f/8 in the employee list well that is call them five right five one two three four five is the address so we want to make sure that when we.
Make any changes all the way over here is also equal to column five okay so it's very important when you make changes you want to update and these buttons are not let's remove these these are not necessary this is only for mapping purposes so we don't need that okay so we just removed that all right so moving on we've covered a mapping and so and now I've updated I've added a brand new tab events this is not functional yet but basically the idea is that all the events for that specific.
Employee are going to be low-key here perhaps not all of them but at least 17 of them and then of course we'll put a show all button here so that when we show all the events it's gonna automatically go over to the events and then show all events we'll put in a filter here for that employee name and then we'll go ahead and down maybe we'll add an employee ID or I don't love the employee name is nice but remember I employee names tend to change employee IDs will not so I've added a column for employee IDs here so we'll do a lot of.
Filtering by employee ID because employee IDs don't change but it's you know when you want it most times you don't know the employee ID or you haven't memorized it so it's nice to have the ability to filter by employee names so we want kind of both and all right back into the employee manager so let's go over the updates so I've extended that I've extended this in the timeclock history I've added notes field now we have some extra field so we've added the ability to have notes I've also added to start time end time total break regular hours overtime that hasn't changed now I have added this we're.
Going to go over the ability to automatically add from and to dates based on the user selection I also want to put a pop-up calendar here so the user can put in custom dates I want to do the same thing for time clock at payroll history these are gonna be the same right I've also added notes here under the payroll history so that's gonna be really better so that additional tab really helped us out because we needed a little bit more space on many of the screens so it kind of helped us out adding that events tab worked well it was a good amount of work.
So that's why I did it beforehand because it would be a little bit slow if I'm doing it while it but basically you understand the methodology I went through based on the first one so all I did was add additional conditional formatting and updated the code for that scheduling of course I've also added a stop break start and a break end so we've expanded that we have start time and end time and now we can add the break before we just had total break break now we can add break times which.