Learn How To Create Pop-Up Notifications, and User Login In Excel [Employee Manager Pt. 14]

hello this is Randy with Excel for freelancers and welcome to part 14 of the employee manager where we have created an amazing login screen for user access we're going to show you just how we created that we've also added on to the admin screen by creating an event notification step or it can handle all of our email and pop-up notifications regardless of event time we're going to show you how to create those and how to create these dynamic pop-up notifications we have had that ability in to the events list here where we check for notifications and all of our email gets created and we have a dynamic notification pop-up right here so we're gonna show you how to create those pop-ups I can't wait we've got so much to cover so let's get started all right thank you so much I'm really happy to have you we've got a jammed packed lesson for you today we've got so much to cover although it's just a few items each one of those are a ton of brand new types of features that we're gonna show you things you may never have seen before in ways that you may never have seen me teach them before so I can't wait to get started let's go over a little bit of a summary of what we're gonna show you I've created a user login and security form now you're familiar with this type of idea but we've done it in such a way where it's a little bit more streamlined where we've removed the border we've created a really nice little effect here and we've got the username password the ability to remember the password and a login so we're gonna show you how to create this really beautiful login form very professional just how we did that of course we've got a login screen here and all this is this the blank screen this is going to be our Start screen when a user logs in they're gonna see this screen when they're not gonna see any other tabs until they have logged in so that'll be important I've got all that feature but we don't have it working right now because I want you to have the application to the full but all the code is there so you'll be able to run it any time you want any admin screen we've had a lot now we've updated this user settings we've updated this little bit we have the ability to of course add in a username password email and a user type now we've set a user type and the reason is we want to show or hide or enable or disable certain features certain sheets certain aspect of this application based on a user type now I have set for different user types initially we have admin and that'll probably have the entire full feature ability on the application we have a manager and that's going to give us some limited features but mostly all and of course I'm gonna show you how to control the feature so you decide what a manager is enabled or able to do you decide what a user is able to do and then I have one that's called a time clock now remember I said if we choose time clock I wanted to have a dedicated machine just to use for time clock so if we were to choose a specific user for time clock give it a password then only the time clock only this screen would be useful and that's nice if you have a machine that you're gonna dedicate just for the time clock you may want to have this screen only so you might want to dedicate a user for this and I've added the ability to log out and a start and stop of course we have had that already but I've just add a little bit of features because something like this time clock where users it may be a dedicated machine on this machine will do nothing else but clock in and clock out employees so if you have that feature available so we've done that and I also what we want to do is we don't want to have multiple location notifications sent from multiple users we really want to set one specific user to create or send notifications otherwise you'd get multiple emails from many people with the same content so we have the ability to set which particular user will be sending out the notifications which is the emails or the having the notification pop-ups okay so that's important there also I have added oh and here I've just add a little bit of a summary of ledger it gives you a condiment what my idea is for the admin but remember you can change this how you want I'll show you how to change it's super easy to change this is just simply my idea of what you might want and admin would have full rights to all the sheets would have the ability to edit and remove all records create payroll and view all reports and of course unprotect sheets whereas a manager might have the ability to view all records and maybe create payroll reports but not the ability to remove select and maybe not delete payroll or delete employees or something like that you may want to limit that manager's access and of course a user would have very limited they would not be able to view or view any reports or do payroll they may be able to view the schedule and add employees and view employees but an add events but something basic like that so I've broken it down into three different areas and this is kind of nice it's a really easy way to create new users very quickly simply by assigning them a user type some call this user type let's call this user role or user type because user roles are also very common with that so we can call them either way so user roles so we're gonna we're going to go ahead and create that and next up on the event notifications this is a brand new tab and the idea is this based on a specific event let's say it's going to be a review or training or a new hire or something like that you may want to create a specific email template you may want to have a very specific email to BCC you may want to CC it to a manager you want to have specific attachments so for every event type you can set a specific default email notification and you can have a default pop-up this is a pop-up remember there's a pop-up this will appear on any screen at a specific date and time so that the user gets an alert for this particular event and one is an email and we can have both so that's a really new great feature we have the ability to add attachments and by selecting any of the events below you can see it up at the top now this table will be limited to the event types meaning you will only want one email default email template per specific event type all right so we have one for new hire one for training termination review and just remember in the admin screen we have the ability to choose what event types right so we can set our event types to whatever we want and then what we do is we set an email and automate an email let's say we want to send somebody a birthday wish on there birthday we could set that or maybe we want to send them maybe if they're terminated we want to say dear employee you are fired bye-bye now you know something maybe a little bit more nicer maybe that's not so nice but you get the point so you get to set whatever your and I've included a whole lot of variables that you can use within both the email message both the email subject and the pop-up notification so we can use these variables and what might those variables be well they could be an event name the event type notes when the event was created the start or next day time of the event the end day so for example if if we have a training we might want to notify the employee when their training is so for example high employee name this email student you notify you if your upcoming and then event name would be training right scheduled from and then start date to next day here are the following details and of course you could put the event notes in there and of course when the actual email gets created it would contain all the information that we put in remember in our events let's go ahead and look back in our vets it would contain all the information our event notes our event name perhaps are created on day our start date so for example if we have a let's pull up a training right which contains both the starting on and ending on we have that ability so the information is contained here and the events in the events list here and of course in the events list here is all the data is contained in this so that's where it's gonna pull the data from but we want to set the following also we have all these username user email and of course the user name and the user email those are now created based on our login screen and of course we're gonna show you that and that's going to be hidden columns a and B and we're gonna go over that but basically whatever logged in user we have is going to show up here whatever user email is gonna show up here so we have the logged in information so now we know the user that's logged in it that has been logged in so we're going to be able to use that in our notifications as well so we have all those if we want to set a new notification we just click new and then we enter the email type let's say we want to email to the you email email to the to the employee email employee that's gonna give us our email address and then we can put the event type we would of course select an event type maybe it's a company party or maybe it's a new hire and we can just set a BCC maybe we want a BCC it to the user email and then user we would just do that it's gonna send it to the user or you could put in a specific email address you could also you could easily just type in admin test company comm right so you can do that too as well so you can put it into anything right you can even have a static email address and not a dynamic and of course you could put in a subject and the subject could contain anything like new hire for and then you can put the employee name and this way you can send them you know specific attachments if you may want the new hire to fill out certain forms you can just simply add a file and of course it'll add any file you want whatever you whatever you decide to attach here you know we'll go with email and then you can put in your email message and then you could just say hello you know you employee name and then of course when we want to go into a comment and that alt enter we'll go to the bottom of the line welcome to our company okay so you get the idea you can just fill this out then when you're all done you just click Save notification and this event has a notification but okay good so the event right we only allow one event new hires here so we only have one event type so we would change them we already have that so but we don't have let's say promotion let's say we don't have that it's not gonna list so we can save that promotion okay and save new now it's gonna save remember we only want one default per type so that's important we're going to go over that oh and of course if we want to set up some pop-up text we can do that as well just we would say let's say promotion Congrats we could do this cut and grats on your promotion and of course it's going to show up right here in our in our we can put dynamic labels in here as well so it's going to show up when it does so that's a great way we could delete them new not've case so we're going to go over all of that so I'm looking forward to showing you that so we've got that and also let's go ahead and take a look at what else we've put we have the ability to send them so when we go ahead and click on the events list now notifications are based if you remember correctly they're based on this reminder we have a setter in mind for it so this is going to be set when we want the reminder to be sent whether it's email or whether it's going to be a pop-up or whether it's going to be both and here's our pop-up so if this is yes it's going to be yes if this is email and then we want to send a reminder so and if it's not been sent yet so if we click on this nothing's gonna happen right nothing's gonna happen because they've all been sent but what if we delete this reminded on delete that check for notifications it's gonna see that one hasn't been sent dear Fred Fred errs you are fired bye-bye now okay so it's been sent to Fred and then BCC Randy here so that's going to be known when we close that and normally this would not appear because we have it appearing because it's not gonna send but normally you will have it so it just gets sent automatically you the admin wouldn't need to know about it wouldn't need to appear on the screen and I'll show you how to change that so once we close that out you're gonna see that the notification now we have the termination notice for Fred us has been terminated there fred was terminated for talking to the boss nobody talks to the boss okay so we have that pop up so this will appear regardless of the screen that you're on regardless of the screen that you're on so if we close that right and let's go ahead and remove this again I'm gonna put an O for email cuz we just got the email we did we know what the email looks like so we're gonna put no we don't want to see the email again and I'm gonna remove this reminding on so that it appears again now when we go to let's say any other screen no I guess I need to run the macro let's go ahead and run the macro I have no button to run the macro so we're gonna go into the code and we're gonna go into notifications right here and I'm just gonna run the code right here so I'm just gonna run this code right here and you're gonna see that this notice appeared also on this screen it's can appear on regardless what ever screen to users on it's gonna appear and all we got to do is close it so it's really really handy maybe a little bit annoying but a very very handy and very important note that's gonna appear regardless and that pop-up can be deleted it doesn't matter because it's it's coming from a source that cannot be deleted so it's a shape but it can't user can delete it that's just fine it'll appear again no problem when actually when they could close it actually the code watch they delete that shape because it's being copied over from the source so that's fine all right so you see so you see the notification is gonna appear regardless of the screen that's wrong and that's important when you don't want them showing only on one screen when they're on another screen so that's important all right let's get into the training and see what just how we did we're gonna start with this login and we created this beautiful login form we're gonna show you how we did that so let's go ahead and get into the VBA manager and see how we did that into the developer's tab Visual Basic if you don't have the developers tab open make sure you click on the file and click on the properties right on the options here and we'll go ahead and go into the customize ribbon and you'll see that the developers tab is selected you will want to make sure you select it as well alt f11 will also get you there and that'll bring you into the VBA editor so let's go ahead and take a look there's a few modules that have been created let's go ahead and take a look down here into our code we have user login macros here and user rights macros here we're gonna go over those two and we also have a brand new user form called login form now as you know creating the login forms and creating any type of form is is not the prettiest the forms they it's kind of an older type of maybe about 20 years older actually so Microsoft has not done a great job with upgrading it so that means we have a lot to work with when we do that it's going to be we have to be very creative and the idea is basically to get it to look like this I created a background in this blue border this icon and even the borders now look at those you see how it's off you see how my buttons are actually off this blue space but when you run it everything lines up why is that well because I've added some code that is going to remove let's go back in here I've added some code that's gonna be remove this top frame here this login frame because I don't want to show that so when that gets removed everything gets moved up and that's why the buttons line up with the blue these blue now how did I get these Blue Oval's that's actually part of a background picture so what I did is I created a background picture and I and I put this icon I put it on the background picture so when we click on properties and we take a look at this picture we're gonna see that's a bitmap right and we click on it we're gonna see that's gonna actually be a bitmap that I use and I created it and I used something called Pixlr which is a really really great feature and I wanted to show you that and it's called pixel or pixel or it's a free it's like Adobe Photoshop but this is free and all good the link is like it's great so this gives you the ability to create designs and so what I did is I just found a background like a steel background now I just created some ovals like here like it let's see you can click on the shape right and you can click on an oval and then let's say we don't want to fill the shape and then we can set the border so maybe a blue border and then click OK and then we want maybe we want a border radius so we can click on the radius already 20 so that's how we did that that is how I did that let's go ahead and update that a little bit border size so you can actually we need to increase the border size so that's not enough so we can create the border size and create a border here so you see it gets larger so we can do any color and if you change the color let's change the color to blue and change it bring it over here so this gives you an idea of how you can customize your backgrounds to make them just fit them that's too much of a border somebody we want 7 so you can see here this is how we have the ability to create the shape oh and the border and everything so it's really really a handy handy tool that you can use so I thought I would show you how we did that and I basically downloaded and just pasted this icon in and you can add any images just simply add a layer and then open an image is layer basically what that's gonna do is going to give you the ability to add a specific image so let's say I wanted this Login guy here and let's go ahead and find him where I added him so we can drag this guy anywhere we want to here he is here so I can then move this guy here anywhere I want on the form so we can create these once we the picture we can save it and then it'll be our background for the picture so I thought I'd show you that because I wanted to to show you how I was able to create that background and that gives us a really professional background that I thought you would like so now we know how we got the design now let's go ahead and take a look at how we got the top border so let's close that go back into the VBA editor under the floggin form now I wanted to get rid of this top border I don't like that and I basically I just found some code on the internet because just to remove it and you could find it too and I'll go ahead and go over it here under the login macros and you'll take a look at this code right here this is not private it's a private function here and I just located this somewhere it's on this particular code is on a lot of websites the same exact code and all it does is it hides the bar form so make sure when you want to do that just copy and paste this code and then also the important thing is you see this hide bar form here hide bar form this must be located and the login form let's view the code here and you want to make sure that you have this hide bar Emme under the initialize so that means when the form gets created this particular macro high bar Emme is going to run this is gonna run for this and that way it'll when we run the code it's automatically going to remove that top bar so that's how it is and that's how it's done right we don't need to go too much into detail because you can just copy and paste it right there and you don't need to know the exact detail but that code those two macros and I'll show you those just once again so when we see the login so it's this right here this option explicit this is used the VBA seven so we need to know this for either whether they're going to be public declare or PTR say for 64-bit or 32-bit so we need those whether they're 64-bit because of course we are going to be running those functions so we need to know the for the menu bar and then of course we're gonna run this code right here which is the sub high bar and that's going to basically remove the bar from it all right so next up we have a macro called show login form all that does is just show the form we've created the form here's the form name and then show so we have that next up what we want to do is we want to check for the user when the user presses ok what do we do so let's go ahead and go over that so when we login yours is gonna put their email address and then they click one two three they're gonna click OK and I don't want to own a woman I guess I can click OK and then all of the sheets and then the login form is gonna hide automatically so let's unhide that I'm just clicking unhide right now so unhide I know it's off the screen here we go back on the screen login the login forms hidden automatically which is nice we're gonna show it for now ok so we have that so when we login it hides a login form and then it shows or hide sheets based on the user security so back into the admin let's go look at those users now we have these users we have a user name we have a password we have an email and user role so let's take a look at some of the named ranges that I've created for that into the formulas name manager and of course let me drag that over here for you user let's go ahead take a look at user we have user email and I didn't use offset because we're not using a drop-down list so the entire range is good enough for us offset for the email we have the user name here and we have the user password so I've just created these named ranges they're gonna cover us for when we use our index and match so just remember we have user name password and email user role so those things are going to be important because we need to once they put in a user name we need to check the password so that's important so back into the login screen and let's go ahead and go into the design once again so we can take a look at that here not the time clock here so we have this particular cell this particular field I should say tied to a specific cell that is tied to b5 that's the control source login is the sheet b5 is the cells that's important we know when this has changed its can affect b5 the password is connected to b6 now you'll want to make sure that these columns are hidden and locked from your users so they do not have access to these cells they cannot change this so that is very than the admin everybody else should be locked out of those that's very important that's and also we have one more option and that is the option box here and that particular check box is look is controlled with benign benign is going to tell us because sometimes I want to remember I don't want to enter my username every time so if that's the case we can select this check box and it's gonna remember so that's all you really need to know we just have two buttons one is the okay button and when we double click the okay button what's gonna happen we're gonna run this macro called check user and the close button I suggest you do like something like this workbook closed but I'm gonna comment that out or something like that so you may want to close the workbook but for our purposes we're still in the testing phases so we don't want to close the workbook really so but you may want to have something like that and then whether you want to save the workbook probably or not probably not right so you don't want to save it so this so if this work if this were commented out right if we took out that comment they'll work the login format hide and the workbook would close without saving in this case but we're gonna comment that out we don't want that to happen not especially not while we're testing not until we get everything complete but that just gives you an idea of what it will be in the future for now it's simply just to hiding the form using the form name and then the word hide it's gonna hide our form and not do anything else so this particularly uniform really just three macros one hides the bar one runs a matter called check user and the other one is gonna hide the form so let's go ahead and take a look at our only real macro that we have and that's the check user so that would be here under user login macros here and we have it all the way up the top let's go ahead and say check user not at the top check user we have that here and let's go ahead and go over that and so we walk through this particular macro to see how we've done it all right and also we want to pull up the login screen that's going to tie this login screen is gonna tie directly to that so we know already a few things we know that b5 is tied specifically to our form remember b5 is tied to this so when we change this and we tab over you're gonna see b5 change also okay so remember those are connected same thing with the password password here oh I forgot to show you one thing I want to show you how we get those stars and not there so let's close that out and go back into our user form a login form here and we click on the password field and all the way down here we'll see password character you see down here password character I've put a star and if you want to use a different character you can use a different character a new star which is the most common or asterisk as we call it so password character that is how you get that is how we mask the password with a specific character all right so we've covered that and let's go back in to our login here sheet so we've covered that we know that the password name is connected so we know as we added in two passwords and then we tab over we know that our password is gonna be here these two columns a and B are going to be hidden so nobody will see that so that's important there we can close that out so we also know let's go ahead and enter the password I want to know I want that's the correct password one two three I want to know if it's a correct password or not how do we know that well we know the username and we know the password here so using those two we can figure out if it's right or wrong with this formula here's the formula we're gonna index the user password that's what we're looking for that's what we're looking for and we're gonna match it we're gonna just run the math what are we matching we're gonna match the username and we're gonna match an exact match so you want zero we want to match the username with this named range here I showed you that name right just a moment ago so we're matching the username and it's the first column and the index meaning the user password we're only investing one column so one and does it equal b6 b6 is the password that the user entered so basically we're gonna locate the password in the table and then we're gonna check does it equal b6 if yes then it's true that is the correct password if it's not equal to b6 it's the wrong password and we're gonna put false so it's a very simple formula to check it so if I were to change this to DD that's going to go to false because it isn't correct password and of course if it's incorrect we want the user to do something what is the user type we also need to know that we can determine the user type by indexing and matching based on the user name of course we're gonna index in this case it's user type which is also in that admin screen I showed you we're going to use a run a simple match matching the user name in b5 against the named range username and then we want an exact match I should put a zero in here and then in that case we want to match the column exactly the index the first column because it's only one column so in that case admin is going to be it's gonna return our user type we're gonna need that user type because we need to show or hide specific sheets or specific ranges perhaps based on this user type so that's very important now should we remember the user name this particular field is tied remember b9 is tied to this option remember my username so if the if we unselected it's gonna go to false if we select it b9 its gonna go to true that's gonna come in handy when we run our Macker the next up i want to copy the login user if we don't remember this username this is gonna disappear but i need to know the username so i'm gonna copy this field in the macro to the logged in user so that we can always remember the logged in user because we need to know that and we've assigned that particular field app username app username that's gonna help us moving forward like when we want to send an email we need to know who the emails coming from and that's going to be this person randy that is our logged in user name and of course our logged in user email we're going to use that again use your email we're gonna index we've already named the range over an admin and again just a simple match using the user name that'll return our user email so that's how we get all the data now what do we do to it let's go back into the macro and focus on our check user in fact let me close this out and bring the screen down just see you get a look at both at the same time we can reduce these just so you have the fields on the left so that we can look through all right so first of all we're gonna calculate I want to make sure just in case I want to make sure to count because we've got a lot of formulas in here I don't want make sure they're all calculated first just to make sure all right so next up first of all we need to make sure that we actually have a user name a user type so for some B 8 if B 8 is empty that would be incorrect user name because we're using a match right we're matching this B 5 so if the user name is empty we know for sure that this is going to be n/a it's not going to be blank in fact let me just wrap that on I don't like airs if err I like to wrap them always like to wrap them so let's go ahead and wrap them in an if air and put a blank there and then we're gonna do the same thing here just in case there is an error we don't want errors because the errors could create bugs in our code but blanks will not so if air then we're gonna wrap that into a blank space just like that so we can test for blank spaces and we don't have any airs okay so if for some reason it's an incorrect username it's going to return just a blank space and that's just fine so if b8 value equals empty then it's an incorrect username then we need to alert the user message Mike please enter a correct username and then we're gonna exit the sub we don't want to move forward yes we don't want even I don't even want to close the user form I just want to exit the sub so that the user has the opportunity to enter the correct user name now we need to do is we need to run a check assuming that there is a user name we need to check on the correct password we could do that using b7 if b7 does not equal 2 remember we will we focused on that formula how to check for the correct password so b7 will tell us true or false false if it's not correct so if it's not true or we could also do equal to false then it's an incorrect password in that case give messagebox please enter a correct password and exit the sub we don't want to even close the user firm we just want to exit this up all right now if they've passed these two tests we know we have a right password and we have a proper user name so we can move forward in that case we can hide the login form and we can also definitely set the user type to be eight that's gonna set our user type we need to know that moving forward because we need to run different macros based on this user type so we need to set that and in fact we can then move on to our clearing the pass where we want to clear the password that's important we always want to clear the password so we've done that and that's in b6 so we clear the password out and next up I want to clear the user name in b5 but I only want to clear that b9 is false b9 is false means they have not requested to remember the user name so in that case if b9 is false then we're going to clear the content so this line here if b9 equals false then b5 clear the contest that means the user has not requested us to save preserve the user name and therefore we don't need to remember it therefore we can clear it out all right next up we're gonna run different macros for different macros based on the user types sure we could use a case here but that would involve more lines so this is pretty easy just for different lines if the user type is added then run this particular macro you called user set as admin if the user types manager we run the macro called user set as manager same thing for user and another one for time clock so we've got four different macros that we've created based on the user type we've also got a macro that's gonna close our workbook this is going to come in handy a little bit later on when we want to clear when we want to logout we want to close the workbook but I want to do is we know I want to make sure this login is the only one I want the user to see so that means when they log in they can see this and all we have to do is click login and the reason that we don't have the form pop up it there's some errors especially if you download if you downloaded a workbook from the internet maybe the macros will not be labeled so it's always nice to start off having the user actually do something like click a button before they see the login and that's going to be helpful so things don't run automatically we may not running macros right away onstart can be can cause issues maybe there not enabled and here you can put in like a little something you could put in some words saying you know please and please make sure macros are enabled so you know put a nice big message box or a picture or something on that because that's important because if they click the button and macros are not enabled nothing is gonna happen so we want to make sure that that's good you know you can probably do something nicer than this long and I just created it really fast cuz I had so much to do but that gives you an idea alright so because when they close the workbook here's what I want to happen I want to activate sheet 14 on sheet 14 of course is our login sheet right here I want to activate this first and the reason I want to activate is because I can't find all the other sheets like in other words let's say I'm on admin right here and we want to close the workbook I can't hide the admin she while I'm on it so we need to make sure that we're off of any sheet we're gonna hide so the best way to do that is to go to login and then hide all the other sheets so you want to first go to the sheet or activate I should say the sheet before we hide the other sheets and that's super important so we do that first and then we're going to dimension worksheet as a worksheet and then basically what we're going to say is for each worksheet in this workbook of worksheets I want you to hide everything except for the login screen except for the login screen so we can do that with just a little bit of code we can say if the worksheet name does not equal log in then worksheet visible equals very this is going to hide all of the worksheets you can set it to very hidden or just hidden either way but they should be very hidden because you don't want these users that don't have rights to easily unhide worksheets so I would suggest very well hidden especially for things like payroll and things like reporting so the things that are very sensitive in nature okay so that is our loop right there it's gonna go through it's gonna loop through every sheet in the workbook the next step we're gonna save the workbook we also want to save it and then in that case we would want to close it we want to run it but what we'll do is we'll run this closed workbook we can put this on the day work but close event so when we go ahead and deactivate it like or actually close it we would go to workbook clothes event or before clothes I should say right here and then we're gonna run that macro in here but we're not really ready to do that yet we're not ready to implement that yet but that's what we would do we would run this macro on clothes so when the user closes it that goes through that before the clothes so that's the idea of it on that particular macro here so starred workbook star workbook we want to make sure that sheet 14 is activated it should be but if for some reason the workbook got closed let's say the work that got closed without running that macro you'd at least want to make sure 14 was activated and you want to show the login form that's important so they can properly log out you may want to run you may want to run this loop again it's possible just to hide it so for example if your work but crashes and/or you save it and then it crashes and it might open up with certain things visible that you don't want because it didn't go through a proper close so you may want to run a check that if certain worksheets are visible you will want to hide those worksheets before we actually run just in case of different user logs in so that's an idea all right the next up start workbook so we're gonna start that and show the login the next up is a log out we have that button sheet 14 we're gonna activate that and again we're gonna run through all this we really don't need both of these but we'll decide one way or the other on those but we don't once for closed workbook but if they log out they actually log out we can do the same thing like for example when we have the time clock we click log out and it's gonna actually save it and close it and get it ready for login next time so that's another way to do it so I thought that was gonna be handy to be to be able to log out we may add a logout button later on so that's gonna help us so it gives his user to save it and close it and logout in just one swoop so it's kind of nice to have that wrapped in a macro alright next up we have user rights now we would over just remember we had just mentioned set as admin these are the different macros that run or based on the user type and I have just set these like temporarily but I'm not really sure we're not sure exactly what is you know and you should set your own as you want them but basically what I did is I just decided to show a certain set of sheets and hide some other sheets admin of course would have you know everything visible except maybe some other schedules these should really be sheet they're all visible and this is probably should be visible to weekly schedule these need to be visible for the admin and this one too so they're really all visible for admin and then you can decide once we get completed with this project you can decide what sheets are ever it's very easy to change my sips and I suggest you look these over and decide which sheets you want to hide and which sheets you want to show based on the role of the user based on the user type a role so we have multiple ones so that's all we did here it's very very simple we just hide the only important thing to remember is one you don't want to hide a sheet that you're active so for example the employee manager the first thing we're gonna show is I'm this particular sheet I suspect almost everybody will have visible even a user so this is sheet one so what I decided to do is to first of all make it visible make sure it's visible then activate this because this lets say this is the default screen that almost everybody will have access to then once the users on this then you can hide specific sheets because you know the users are not on that sheet so that's why I've chosen to make sheet one visible and activate xi1 and then hide or show the others so that's just how I've done this and of course for time clock what I've done is I've hidden all the sheets except for time clock that would be a for a dedicated machine more only time Coggins a sheet that you want and in that case we're gonna hide all the worksheets other than x bug and then j13 is select that means that is our that is our default where the employee IDs are gonna be entered which is j13 so I want that selected so when they click on the time clock j13 we want to make sure that j13 is selected that means it's ready to go and ready to receive employee ID numbers so for the time constrain that might be a nice handy feature so you have one specific machine dedicated for time clock and that way you can have that own unique user ID which is going to be helpful alright moving on we've covered our user rights let's go back into the admin and now let's take a look at our event notifications right here now once again the idea and the premise of this events notifications is to set up some defaults for both the email notifications and for the pop-up notifications what we in this way the reason is we set up defaults is that we don't need to set up a brand new email to attachment subject your message for every single event that we create this little big so that we can have defaults so that when we do select let's say we create a new event when we do select a specific leave event we automatically get some information here within the email - and then we can adjust this subject message attachments based on the specific leave now but at least we have a starting point because we may be creating a lot of different events and we want to have a starting point or a default notification for both and also we have the pop-up leave it simply is a simple pop-up that we can set them in the admin screen so the reason is we set up a default so that as soon as we select a specific event type we can automatically have that information and I've made that so that we can set that up just on four new it's not on existing so when we change in existing the existing event type and existing subject message all remain it's only for new this way you know that we don't erase anything that we may have already saved it's only for the new ones all right so that is the purpose of it so let's go back into the email and the admin event notifications tab and see how we did I'm really going to the developers and the first thing we're gonna do is we're going to start off the idea is that when we select a specific and previously saved event notification below it shows up above and that's pretty consistent with our application here and to do that we've done of course conditional formatting we have our selected row and we have that right here in B 507 so we've created some conditional formats based on this number so when we select a different row that number changes so when we go into the conditional formatting we will see under the home conditional formatting and managed rules we'll see that we just have two rules one is for the alternate row coloring is we have blue or white and the other is for the selected row here we have a rule here we want basically I want that under two conditions one I want to make sure there's actually a value in column D and I want to make sure that it is part of that row that we've selected so those are going to be the two conditions in which we code this so let's go ahead and edit the rule take a look at the rules and the rules are twofold we have two conditions so we use and and the row the row the selected row must be B and 507 that's one rule the second rule is D and 521 must not be Blake and why do we use 521 521 is the first row in our conditional that applies to and make sure that we've taken the dollar sign out of before 521 is only in the columns we do not want to be before the road so we want that always to remain constant and we do not want we need to make sure the row is relative and then the column is constant so that's important so remember this 521 so when we cancel out of that and we take a look will see that the our conditions also started 521 so those two must match in order to maintain this conditional formatting and of course in the alternate row there's also two conditions we're gonna edit that rule you want to call our alternate rows but I only want a color alternate rows when they contain a value in D so once again D 521 again no absolute reference on the row we know dollar sign there we want that to remain dynamic and of course mod Row 2 equals 0 so that's going to color all of the even rows so you'll see here 522 524 526 those are rows with even numbers those are going to be colors and the others are gonna remain white so those are the two conditions that's why only those rows with data are going to be colored and only those rows with data are going to be selected so if we were to select add some value automatically gets colored and automatically you are able to select it however as soon as we clear that value both of those conditions remain invalid so that's how we cover the conditional formatting we've covered that before and some videos about wanted to bring you up to speed on that all right the other thing is through VBA when we select a specific row we want that data to load in here we want to take all of the data and we're gonna use data mapping to do just that and we're gonna go over data mapping and the idea with data mapping is if you look down here below this is very important we have here data on the sheet so if you'll see here notifications mat G 507 so when we look to G 507 here we see that that's the event type right so G 507 the event type is in this column so for each column we want to put the actual field where it's mapped the actual field this way so e 507 is gonna be email to email to is e 507 and we've done that for every single field keep in mind that this is a merged cell covering two different columns so keep that in mind and this is well this is also a merged cell covering two different columns so we want that as well and of course here with this pop-up notification this is a group I've created a group and the reason I've created a group is because I need a high disk group when we select another tab when we select this tab I want to show that group so this particular group it's a group of shapes and I've named that group of shapes notification group right here here's the name notification group so you'll see within vba I'm gonna hide that group when I select any other tab and I what it's gonna display when its event notification is how do we know it's event notification well this particular column is column nine column nine this is column four five six seven eight and nine so we know that when column mine has been selected to show the event group and the event group includes various shapes such as the icons here the shape here in the delete and this shape is very simple it's a button and this is a little picture that I've imported just search Google I found this little pin cushion pin button there and this is a simple shape it's a folded corner shape it's one of those shapes that you can find under the insert shapes here and that's just the answer to folded corner I believe it's somewhere down here under folded corner it's right here and this fold the corner all I've done is reversed it so when I select that for Tikal all I've done is reversed it like this so that I can reverse it and then I've given it a color of yellow so that's how we create that little note it's just a really nice little feature though just giving it a color you know a light color like this so that's all I've done to create that and now how do I apply the text well what I've done in this content for the sample is I've selected the shape here and then I've put a formula on that of a 508 so I want it to equal that cell which is right here oh excuse me i 508 i 508 so so anything we've been changed here so if we just say from and then we say user name right it's a user name here that's the right field it's automatically as soon as we change here it's automatically gonna be displayed in this node now this is just a sample right sample when we win the actual data is is change it's gonna be in the in the form in the actual pop-up itself and these data labels are all gonna be replaced with actual data because these are dynamic field names so we put those in so they're changed so we know who has created it and all the details that we want and it's fully flexible now I've also created it so as soon as we make a change assuming it's already been saved assuming services it's gonna make the change right here so for example if I change this if I add an attachment here and I click an attachment here and I just click any file here it's automatically gonna be saved in the file below you'll see right here it's been added right here automatically here so it's automatically so any change is automatically saved now let's go into the VBA and we've got a few things to show you I'm gonna show you how to go through the macro so let's start with the on sheet macros the macros that run on the sheet itself so into the developers tab under Visual Basic and we're looking for the admin sheet the admin she were looking for on sheet macros which is right here admin sheet now we've got some macros that happen on selection change right we have some things when we select we want something to happen so let's go ahead and take a look at that particular Mac and see how we went about it did that select notifications to load on cells above it's this macro right here all we're doing a sync if we select a cell any Cell between 5 21 and l5 41 then do something not intersect is nothing with those two cancel each other out meaning if the user selects that their target is within this range then do something but there's one more thing and and what Dee and the target row must not be empty why is that dee if I select something that has it if I select something in the range but Dee has nothing we don't want there's nothing gonna happen there's nothing here only when we select something where Dee contains a value do I want to load that particular event notification so that's the only time I want to Wendy contains us so we need to make sure that that D contains a value as well so those are the two conditions the user has selected something and remember this is under selection change right this is only for selection change worksheet changes up here so we'll get to that in just a moment so when let's go ahead and maximize just a little bit bring it out a bit so when that happens what do we want to do I want two things the first thing I want to do is I want to take whatever row and put it into 507 whatever row we've selected I want to put it in 507 of course that is the row that we just discovery know we need to know that row for a few reasons because when I make a change here I need to know what wrote to save it back to so I need to know that rope and I also need this row for the conditional formatting that colors this blue dark blue back with a white font so this really two purposes for this notification row so we need to put that there so that's the first thing the second thing is I want to load I want to run a macro called notification load and what that's going to do is load that macro load that notification we're using the macro with all the details up here so that's a very simple macro we're gonna go over that in just a moment but there's a few other things that I want to show you on this worksheet change and that's with the change so worksheet changed now we're gonna go to work she changed and that means when the user actually makes a change and we're gonna leave we focused on custom field before so we've done that but now we're gonna focus right on here on change of existing notifications update the table below but and not on notification load not a notification load that means when we when we select something there's changes up here right every time we select there's changes right but that kind of changed that type of change I don't want to save back to the table otherwise we create a loop that goes on forever so there's two types of change this type of change where there's something changing in those cells but then there's another type of change there's this type of change here where the user says write that type of change I do want save so when we select out and back in I want that change to save in the table below that type of change now how can we differentiate between well what I can use is called notification load when I select here I'm gonna mark B 506 to false excuse me I'm gonna mark be 506 but just currently false I'm gonna mark it as true temporarily while it's loading and then it's pretty quick it's too quick for you to see it by eye but it goes from true to false very very quick and when that happens nothing nothing changes when there's change here if this is true nothing gets saved back to the table so we want to make sure if this is true that means while we're loading don't change anything back to the table so we need to check make sure that B 506 is false so we're gonna run that check as well right here B 506 equals false so that's one condition we need to make sure the stack conditions if a user makes a change to any of these cells they make a change and these are all of the cells within that range within that notification there all these cells these cells in white here all of these cells and we've got four or five six a total seven cells here if there's a change to any of those cells then we want to save it back to the table back to the table and we also need to make sure that it is not a new notification new notifications only get saved when they user presses save so when we click new notification let's say we make a change there's no road to save it to right so we need to make sure we save at all at the same time so new notifications do not so there's another condition it must be 508 must be false also okay so that's really important so we're gonna go over that and and here we're going to scroll over and 508 must be false right why is that because it must be on existing existing not a new not an objective existing notification so I'm just put in parentheses not new so only so this those three the conditions one it must not be loading two it must be in the existing record previously saved in that case then do something then we're gonna we need to set the Rose I need to dimension the row and the column I need to save assuming that both of those conditions are false any changes on here must get saved down to here all right so we have to save those down here so how can we do that if it's an existing so when we click here I need to make sure that those changes are saved down here so how do we do that because what I need to do is make sure when we make a change here I need that automatically saved down here in the table below so I need to know one I need to know the row and I need to know what column and you know which column so this I need to know right now this if you look here this is column five right I need to know it's calling five that we changed it in and I need to know that the email two in this case it's calling five but for example let's say event type equals column right I need to know that the event type in this case it's 4 right 4 I need to know that this is calling for to save it in column 4 and I need to know what row to save it and the row is easy it's right here but what call and what column we use that with the event mapping so for example email - is column 5 event type is calling for what's BCC BCC is right here so that would be 6 4 5 6 7 attachments or 7 so how do we get those columns we know the row and we just need to call them we use data mapping and what I know is I know this is calling 5 right but if I move 20 columns over I can map that data here's column 25 column 25 right so if I put the column where we need to go right here if I put the column exactly 20 columns to the right exactly 20 columns do you understand that it's kind of hard I know it's kind of difficult right so let's go over that one more time because data mapping is not simple but it's very easy and very important I make a change here I need this change saved here right I make a change here I need this change here right I know the row we know the road to save it we know it's going to go in 5 20 fun the rows here but what column because everyone's different this is column for this is called 5 so for example event type how do I know where am I gonna we're gonna I gonna get the 4 from I need to get the 4 I need to know ok so why don't I put the 4 exactly 20 columns to the right I'll put the same row the same row right 507 I'm gonna put that for 20 columns to the right so if this is column let's look at this one this is calm 7 equals column 7 right so that means in column 27 I'm gonna put a 4 so here's here's column 27 equals column so that means right 27 contains a 4 so I just need to look exactly 20 columns to the right and find the number here and then I know the column to save it in and I've done that with each of the fields each of these fields have a column and this can be hidden so I know to pull the 4 exactly so I can say in our code okay the column equals the current row plus the current column plus 20 what is in that column the current row the current column current column plus 20 what is in that cell in that case it's 4 so then I take that for I take this so let me say ok with row 5 21 with row with column 4 put in whatever the change was whatever the target so we do that and I'll walk you through the code right now it's very very simple in the code that's why it's very simple so let's go up 2 back into here back into where we were again in the admin right here and worksheet change now so we have we know the notification row where is that that's in B 507 so we got the row we know the road that's very easy it doesn't change but what about the column the column is cells right there's no period because we're on the sheet remember we don't need to specify a sheet because we're working with code on the sheet when we work inside a macro we must always specify the sheet but we don't need to specify a sheet because we're working inside the sheet so we know the sheet the target row we remember it's in the rope plus the target column plus 20 this right here this is going to be that number 4 that's gonna be the 4 so now we know the row now we know the column which is 4 the column is 4 now all we need to do is say cells the row and the column value equals the target value so that's how we do data mapping and this applies to every single cell all 7 of those cells that we change for existing so the 3 conditions if the user makes a change if it's not loading we want to make sure it's not loading and if it's an existing that means not a new event then do the following then do this dimension the row and the column is long get the row from B 507 get the column from 20 columns over to the right and then take that to grow and the call and make equal to whatever the target value whatever the user just changed and that's how we can do it so that when we make a change here take a little take out these SS automatically it's going to change in row 521 column four words can make that so that is how we do the data mapping okay so now we know how we save this data table but now let's focus on a few different markers called new notification deleting a notification saving it so we have there's actually four different macros now there's two buttons all we need on existing records this is an existing record it's been saved so there's really only two possibilities for a user they can a create a new notification or B delete the existing one that's two but when they're on a new notification the buttons changed now they have two options now they have two choices they can save this notification or they can cancel the new that button needs to be a little bit bigger it's a little bit small alright so and this is a little bit big so we can move those over so we can see that everything got extended a little bit so you can you get the idea so we've got cancel new and we've got the ability to cancel it so those are the four different Mac words that's only four different macros and then what we've done is we've assigned those macros to these buttons and the groups of buttons here let me get that they're grouped together as you can see which is what we want now the groups here is this let's create that make that to the right a little bit alright so now we're good and it's already there all right and so what we can do is we can also format the shapes and test the button so in case that we need a little more space we can go into the text box and right we've got point we can change the point 0 2 that's gonna give us a little more spacing there so we've got enough in case we don't have a lot of room all right so we've saved this as a group and called this new notification group this group of buttons and the other ones called existing notice so when we cancel this it's going to replace gonna bring back the others so these two are called in this group group of these two buttons is called existing notification group so there's really two specific conditions it's either gonna be in the new state or an existing state like two states and if it's in an existing stand we're gonna show the new button and the delete if it's in a new state where users ready to put a new it's gonna be the two buttons cancel new and save notifications I guess we could just use cancel but that's okay either way so we have both of those and so let's go into the VBA and look over just those four macros so we can give you an idea of what's going on individual basic now we have created a new module called admin event notification admin event notification so we double click on there we've got some macros we got save which we looked at we have load which we saw when we selected the rope before we have add new we have canceled new which is just one line we have delete and then of course we have the add email attached that's just a little macro all that does is gonna put that entire file name in sheet for range g5 way so that's all that's gonna do all right so let's go ahead and go with loading because we saw this one already and then involves data mapping as well once again we need the column and we need the row so we're gonna dimension both of those as long and the idea to when we're loading is what I'm gonna do is I'm gonna go down here I'm gonna run through all of these columns here and what I'm gonna do is I'm going to go through this cell I'm gonna say G 507 equals whatever let's say we've selected this one termination G 5 of 7 equals termination fee 507 equals employee email e 508 equals nothing you know so we're gonna go through we're gonna go through columns for all the way to column 11 this is skip this is this is column 9 and 9 and 10 and 11 so we're gonna skip 10 because 10 is nothing these are merge cells so we're gonna go right to 11 equals columns just so you can see that let's call them 11 okay so we want to show that to want to know exactly what columns are what so we can see we're gonna go through each one of these we're gonna pull this mapped range we're gonna take that figure out what it is okay so we know that in d5 11 we need to put this dear employee in d5 11 d5 11 we know to put that that's how know it so we can deal with it just a very quick loop we're gonna loop we're gonna go from column 4 all the way to 11 skipping 10 taking whatever's here in 542 row 542 and whatever column and using that range to bring up that information so let's go through the code and see just how we did did that dimension notification column is long and notification row is long we're gonna need both of those again our row is going to be located in 507 so for some reason that is empty we need to exit the sub we do need a row so in case any reason that's empty exit the sub otherwise set the notification row as be 507 that's a static row so we know we're good to go remember we've added B 507 before we run this Macker let's look at that let's look at that over in the admin again just so we know on selection change and the admin hair selection change we've added the target row to be 507 before running the load macro so it's got already gonna be there so that's really important alright next up we've set the notification row now we're gonna run our loop it's just a very simple one line number 4 to 11 and we're gonna skip 10 we need to make sure that notification column is not 10 remember we use merge cells so in this case we're gonna if it's 10 we're gonna do nothing so we're gonna go for 4 to 11 and first what I need to do is I need to find the range that range what is the cell that we're gonna be affecting what does the cell that we're gonna make the change well that cell is located in row 542 notification column we went over that just a minute ago 542 in the column so it's gonna go 4 5 6 and it's gonna pull these ranges each and every time ok so back into the code so we need to get that range and once we know that range we can save the range now the range of course this with sheet 4 with the add man so that's why with the dot range because we're already assuming it's sheet 4 here with this range what are we going to do we are going to determine the cells the row the notification row whatever row we set and the column the value that value we're gonna take that value I'm gonna bring it and put it in right in this range and we're gonna do that for each of the cells each all the way from 4 to 11 skipping 10 and the comment here is lo the row values using data mapping that's how using data mapping so we can load a lot of cells and very very quickly all right next up we know it's not no longer any new we're no longer it's no longer a new notification so we need to set 508 to false it's no longer a new 508 is right here and it says new notification we need to make sure that's set to false because it's for sure if we've loaded if we've clicked on it so for example if we click a new notification right now it's true but if we click in existing we need to make sure that be 508 goes back to false and that way changes got saved so we need to make sure so we set that back to false and also we need to make sure that our button sets we've got two button sets one for existing and one for new of course the one for existing we want to show and the one for new we want to hide so we're gonna use those two lines of code all right so that's the load and hide next up we have add new the macro this is the macro that's been assigned to the button add new so just once again when we click on right click on here anywhere in there assign the macro will see that the macro that's been assigned is and new notification add new all right so that's this macro signed so let's go ahead and see just how we built that macro and what we need to do the first thing we want to do is we want to set B 506 and b5 away to true why both we know be 508 is from new so that's important but what's B 506 B 506 tells us that we're gonna load and when we're loading that means no changes are gonna take place for example let's take let's say I have here hard job when we have a new one we want to clear these cells out right because it's gonna be new but what if I delete this let's watch right right here this BCC right here if I delete that it's going to get deleted here and I don't want that to happen when we clear this out so if we mark this true right and then we have it we have something in here it's not going to look but then now let's mark it to false for okay so let's let's put an email in we're gonna have a change okay so now we have admin here but what if what if I mark this to true set it to true then I clear it out then those changes are not gonna be made so I want to make sure that when we clear this out no changes are made in the table below then we can do that by marking B 506 as true so that's important so we've done just that and of course before the macro ends we're gonna mark this back to false so B 5 or 6 and B 5 always set to true be 508 will remain true until we save it now we're ready to clear out so I'm gonna clear all of those cells that are based on that so we're gonna clear that just clears all those cells next up we need to set our button sets the existing it's no longer than existing it's new so we need to hide our existing button set and of course the new one we need to show so this line is going to show those new buttons we're gonna set B 506 back to false once we've set it to true we've made all of our changes we can set this back to so that any changes do get saved in the table below so we're gonna set the notification load back to false and the 507 we're just simply gonna select that as the first cell we want the user to enter so it'll select that automatically so that they are ready to enter a value into the email so that is how we do that that's the add note it's a very simple macro and that's of course all with sheet 4 that's the only sheet we're focusing on all right next up we've covered load we've covered add new we're gonna cover cancel new now the idea is very simple when we're in the new state if the user decides they don't want to add a new one basically what I want to do is just select the first available cell now if they're assuming that there is events if there's no events we don't want that there's an we want them to enter one so we don't want to get out of this new state if there are no events created but if there are all we need to do is just have them select the first one and when they select this first one it goes all the way back to load so when we have a new all we need to do is when we click the cancel new all I'm gonna do is select this one d520 one because as soon as we select it it's gonna load whatever is in that first rope so that's all we need to do actually in the macro for the cancel if sheet for d520 one does not equal Impe that means there is an event saved if there is an event saved that values not equally then sheet for d-21 selected I didn't use with sheet 4 because it's just one line of code here so she 4 select all we do is select the 21 and that automatically will load the event in the field above so cancels really new delete we also have the ability to delete and again what we need to do is first in this dimension both the notifications in the last row and this one's a little bit more complicated because in this case what I want to do is let's say I wanted let's say I want to delete preview right let I'm gonna delete it actually and we copy some values over and I'm gonna create a duplicate of this just because I want to show you what it looks like when we delete so what I want to do and normally you won't have you won't have specific duplicates but in this case I want to create a duplicate because it's the fastest way okay so what I've done is I've just created two of the same now I want to delete this one but if I clear this album I don't want an empty row right I don't want an empty row I don't want to delete the row because we can't delete we got way too much here we just can't we shouldn't be deleting rows at this point because we've got a lot going on we've got all conditional formats we really want to just clear the row I'll clear it out and I want to take all of these names and I want to move them up one level so that's what we want to do in here I just want to take these values here on them move them all up one that's it just one basically I want to do like this move them up one so that's what I want to do in the macro but if it's the last row if their users deleting the last row then we don't need to do anything right they just delete it so let's go ahead and look at that and see how we did that in some cases deleting is fine but in this case we're just clearing it out so first we need an okay we need a confirmation if B 507 is empty that means there is no row so there's nothing to do right if there's not even a road to delete then we're just gonna exit now assuming that by B 507 does have a value we need to set a message box are you sure you want to delete this notification yes or no right that's the type of pop-up or you want right so when we delete it right we want that yes or no I want those two buttons to come up and so if the user says yes then delete it if they say no then don't do anything so and then the title of this is going to be called delete notification maybe you didn't miss that if we click the league we'll see the title here delete notification that's the title so we can put any title we want there so now we're gonna say if it's equal to no if the user has said no then exit the sub if which starts out here for I remember if the message box meaning the result the result if it's no then exit the sub that means a users click no so that means we don't need to do anything else we don't need to move farther okay now if we get down to below this that means they've said yes the notification row we can set that in to be 507 of course and now we're ready to delete D starting with rody and moving all the way to L using the notification row we're gonna clear the contents what that's gonna do is start at D and go all the way to L and just clear those contents okay clear them and then what I want to do is I want to determine the last row and then I'm gonna take the second the current row whatever role we are on plus one plus the last row I'm gonna copy those I'm gonna take your value and bring them up just bring them up and then I'm gonna clear the last row out so I'm gonna what I'm gonna do is I'm gonna copy these rows like this copy and then I'm gonna paste the values right here like that and then I'm gonna clear out the last row so that's all we're gonna do but within the code so first we need to determine those last row we can use d5 41 and Excel up that'll give us our last row of values now we need to make sure is the current row is it the why are we trying to delete the last row if we're deleting the last row then we don't need to move on any farther okay so that's it if the last row is greater than the notification row then then we're it's not the last row that it's not the last row then do the following otherwise no otherwise it is the last room we don't need to do anything so there's just two things that we need to do the D and the notification value the notification row D and through L and the last row minus one the last row - and what equals so again what we're going to do is we're going to say this so let's say we delete this one we're gonna say this row all the way this equals dis here the current row plus one up from the last row here equals what equals this so that's how we need to do it equals one down plus the last row so that's how we do it in the code equals the notification wrote the row that we cleared out plus one through L in the last row and that's gonna move all the values up one row then all we need to do is clear the last row with that so the last row we just gets cleared so that's how we move everything up one row and delete or you clear the content so we can do that just with a macro and we'll go ahead and show you show you so when we do that let's say we select this and we click delete and we say yes it's gonna move everything up one row just like that so that's how it's done all right let's take a look at what else we got here we have safe let's look at say the last one for this is save when we save it we want to basically take all of these values and put them in a new row on this table but the first thing is we need to make sure if we try to save and review if I if I add new new notification and put in test I don't want to we already have Revere I don't want to have more than one we only want to have one default email message and one pop-up note per event type now of course in the events here we can create multiple ones right for every different event but I only want one default per type each default has one type excuse me each type has one default all right so we do need to make a check to make sure so if we try to save this we've already had we need a pop-up that says hey the event type is a notification please select that notification to edit it means just means you cannot save it unless it's an original mussels if it's already in the table we can't save that event time and that's important we don't want multiple defaults for the same event type of course you can create more event types there's no problem with that and then you can create more defaults all right so we do need to run that check and we can do that here and first of all we need to make sure we need to make sure that gee 507 is not empty we need to make sure that there is an event type we can't save it if it's not if it's empty so we need to run a check to make sure G 507 if it's empty then we need to put please enter an event type select G 507 and exit this sub so we'll get out right there and the other check is to make sure that if if it's a new notification we need to just double check that and then if B 509 equals exists Inc what is the existing then check resisting let me show you that's a formula we have used 509 here here you see this this is a formula what I want to do is first of all I want to check to make sure it's a new if be 508 equals true this is an and there's multiple conditions yes it's a new it's got to be new and the count if what are we counting we're counting all the existing types and we're gonna count how many instances of G 507 how many instances are there the event we need if it's greater than zero if the count is greater than zero then its existing that means it's already existing if it's not then it's new so in this case review is already in the table so it's counting at one click that so in this case review is already there so let's click put back in review so it's going to say existing but what if I change this to something we don't have we don't have discipline yet so in this case it's new because discipline is not located in this list and therefore it returns new because the count is not greater than zero so in that case it is new so I need to know to make sure that we're gonna check if this says existing we know it already exists and we need to get out and not allow the user to save it however it says new we are okay so we're gonna run that check it's gonna say if carrot is right here if B 5 online equal existing then put in this message about this event type already has a notification please select a notification to edit it it means it's already in the table below and exits up so we're not gonna allow the user to move on if it already exists in the table below all right next up now we're ready to go okay we've passed the two tests and now we're right now we can set the notification row 2d 541 that's the last possible row d 541 is the last possible row and then we're gonna find the first available row which is the last row of data plus 1 in this case it would be 527 so we'll do just that d 541 the last row with data plus 1 is gonna be the first available row all right else just in case in case the user gets in a situation where they're saving it else if it's not true right if it's not true that means in existing we're just gonna set the notification to be 507 I don't necessarily think there'll be an instance where they're clicking save without it's already actually new but just in case we've said both conditions just in case it's a existing it'll show up here we can set the row to be 507 if it is not if it's false that means it's not a new notification all right so now we need to add the data to the table we can use the same exact data mapping that we did before just as we had said I mentioned for notification call them equals 4 to 11 if the notification does not equal 10 then the cells rows we can take the notification row and the notification column and put that right in there in this case cells 542 this here is the range remember 542 is the row and the column that is this right here 542 G 507 so when we save it I'm gonna run I'm gonna run and I'm gonna look for whatever is in here I'm gonna pull this I'm gonna put it right here and this in the row and run I'm going to take whatever is in this I'm gonna put it here whatever's in e 508 I'm gonna put it right here whatever's in G 508 I'm gonna put it right here so we're gonna run through this loop we're gonna check to see what's here and we're gonna put right in the first available room so we're gonna run the reverse loop using data mapping so we've got that cover right there it's very simple and next up of course we need to reset our shapes it's now no longer a new so we're gonna set the existing notification group to true and we're gonna hide the new notification group all right and be 508 it's gonna set that to false it's no longer a new notification so we need to make sure that we set it as false that is how we run it so now we know how to save it we know how to load our notifications we know how to add new cancel new delete and of course add the email attached in this little macaron right here so we've covered all of the specific macros on this particular sheet so we've got that covered last but not least let's move in to the events where we can actually run our macros so we can check for events so we can check and automate the email and notification process so we show you how we do that and basically what I want to do is this I want to check all of our events I want to run smooth make that a little bit smaller it's okay that the dates don't show up he'll bring them out a little bit just so we can see them basically what I want to do is I want to find all of the events if this is yes right they have to have a reminder we have to set the reminder that means the user has does want a reminder and this has to be blank right we need to make sure that if it's already been reminded we don't need to worry about that right so we're just gonna be focused on those that continuous so we can do that with an advanced filter so I've created an advanced filter up here and so I've put that in here so there's a few conditions now what I want to do is I want to run this macro every single day and I want to run it as many times a day as necessary at a specific time so I want to but I'm only focused on now we have set reminder for this is the third condition set reminder for this is for specific days and times we went over how this gets created so this is the specific date and time that it our reminder is supposed to run that could be a pop-up it could be an email or it could be both so three conditions the reminder must be set the reminder on must not be have a value if it's already been reminded we're not concerned about that and the third condition is we want a reminder that's equal to today's date or less for example if we came back over the weekend and there's two reminders that should have gone out but that didn't go out we need to know about those so we need to know the current day today or actually let's just say yesterday tomorrow minus one right so anything like that before so that's really important so I want to run that so those are the three conditions then we can use those in our results filters so we need to run an advanced filter I need to know that the reminder is yes I need to know that the reminded on must equal blank reminded on must equal blank right if it's already been reminded we're not concerned about that and it has to be less then it has to be less than tomorrow for example the today's the date that I'm recording is December 17th so I need to be less than tomorrow and why is it less than why not just equal today because if it's today right it's not going to involve times it's got to be less than tomorrow for example today let's say today's time could be December 17th at 5:00 p.m. right so it can't just equal December 15th it's got to be less than tomorrow so less than tomorrow's really what we want and not all include anything today any that'll include at anytime today so not only on today's date but at anytime today as long as it's less than tomorrow's date so I'm going to use that less than tomorrow's date and I'll show you how we do that in the code and next once any values that - we're gonna run that so for example when I run the macro let's go ahead and go into that and run the macro but I don't want to send the emails just yet so let's take a look inside the macro and we have notification macros and what I'm gonna do is I'm just going to comment out the code here and here these are the emails I don't want the emails to run just yet so actually I could not get a better idea why don't we just mark them as yeah that's good let's do that so just I don't want the emails in the Popat run because I want to I want to run a lot of them and I don't - so I'm gonna come out this is for the email to send and this is for the notification we're gonna go over that in just a moment so let's go ahead and comment those out and so I can show you exactly how we're gonna do that okay so now what I'm going to do so I'm going to go back into the Employee List here and I'm just gonna clear out some of the send on dates so there's so the reminders so let's go ahead and delete those wouldn't believe those four and now when we click to click check notification it's going to create all the notifications that meet those incidents all the reminders that contain yes in the reminders lets go ahead and update which is here all the ones that contain yes all the reminders that are less than December 18th so all of the ones that are set here set reminder for less so these are less than December 18th including December 17th and the reminder on equals blank right so and this reminded on must be blank so those are the three conditions now we've returned it okay so now what I need to do is now I want to sort them I want to sort them with the earliest first and all the way to the latest now let's say we have ten notifications set for today let's say we have ten all in the same day and they're gonna be all down here I only want to run one run the ones in which are either on the current time or before and if it's not if it's at a later time I want to schedule it for example let's say let's say this is December 17th right now it's set 8 o'clock p.m. but what if it was 10:00 p.m. right I wouldn't want this to run just yet I wanted to run at 10:00 p.m. so how do we get this to run at 10:00 p.m. right I don't want to do anything else I just want this to run at 10:00 p.m. so but for all of the others I need them to run now but this one I want to schedule at 10:00 p.m. so how would we go about doing that and let's go ahead and walk through the code and see just how we did that so the idea is to run all of them before the current time if there aren't any and the next one scheduled but the first thing I need to do is I need to sort these so whatever comes in the list it's not going to be sorted I need to sort them from the earliest to the latest that's the most important so let's go ahead and within the just see how we did that I've commented out a lot of code here cuz we don't want anything to happen I just want to show you specifically the first thing I want to do is I want to check the notifications what I would do is I want to run that advanced filter based on those conditions and we're focused on sheet seven of course that's the events list all right that's the best list and we're going we need the event row as long and the last row is long so I'm gonna need to run the advanced filter so I need to know of course the last row of the events in this case is 25 but we're gonna start here we're gonna run our advanced filter through all of these and so we need to focus on that so we will so we need to get those at both as long this is should be as long also okay so now we've got those as long all in separate rows so now we're ready to enter our criteria we focused on that remember C a2 is yes C a2 we have to make sure that it they do want a reminder C a2 all the way over here must be yes the next up 7 minus 4 is less then tomorrow a less than tomorrow in fact that's what I got set that up less then 4 cd to the date the current date plus 1 that's the same thing as tomorrow okay so the current date plus 1 it's got to be less than that that's very important and then of course ce2 I'm only looking for blanks so just using the equals inside the advanced filter criteria it's gonna get us only those with blank values and that's gonna be put in ce2 next up I want to clear any previous results we need to make sure that this table is clear so we want to clear all that data out just make sure it's all clear all the way actually to CW CW all the way from CA to CW so we're gonna clear out those record CA 5 2 then I just chose a large number here that's gonna clear any previous results the next step I want to get the last row of the current table just so sugar you calling a 9 9 9 and X lab that's gonna get us our last row we need that because we've got to run the advanced filter and our advanced filter is gonna start out with the data a 4 it must include those headers a 4 4 is our header row all the way through aq is the last column in our data as the last all the way here so it must include that all right here cues are last column so we need that and that's very important so let's go ahead and know it's gotta be a B sorry a B let's fix that that is incorrect a B okay so a B is the last column in our table I thought that was wrong and the last row so that is the data it's the table if it still runs just fine but you get dead yet so the last that's our initial table and we're gonna run an advanced filter and we want to copy that and want to copy that over and we need to set some criteria what's the criteria criteria is gonna be C a1 all the way to C e2 it must include the Hatters let's look at that our criteria is right here C a1 must include the headers all the way to C e 2 so this is our criteria range right here we're gonna set that and of course we need our results range our results range they're gonna be from ca4 all the way over to C actually gonna be CW these two I'll show you what they mean in just it's gonna go all the way to CW these two or for the notification I'll show you about very shortly so CW our results are gonna go right here see a 4-2 CW and we want unique records that's gonna run our advanced filter next up I need to know that's gonna run it and get us our results the next thing I need to do is I need to check if there is data for example we need to check for the last row of those results if the last row is less than 5 we know there's no data and we can skip everything else since there's no data so let's look at that on our next line of code if the last filter row is less 5 then go to no notifications what that's gonna do it's gonna skip all of this and go right down here to no notification just and the sub right there nothing else is gonna happen because there is no notifications that need to be set out so in that case there's nothing to be done ok assuming that the row is equal to or greater than 5 we can continue back the first thing we want to do is what I spoke of we want to sort the list and I want to sort it cv5 is the header that is this and on we need to set we need to Center that excuse me we need to sort that base right here cv5 which is the first row of data I'm gonna sort that base to the last row and sort them all so we can do that here first we want to make sure that the sort fields are clear so we're gonna clear that out we're gonna set our range to the key is gonna be CV five we want to sort the values and we just want to sort a sending smallest to greatest in dates we want the earliest date first and that's gonna be ascending and then I'm gonna sort normal so we're gonna set the range of CA 5 to CW we're gonna set that's entire our entire range of data that's where it starts we're gonna and we're gonna apply that filter and that's gonna sort that so that we can now when we run through the list we can run it the first thing we want to do is check C v CF v it is less than now what is c v v let's look at that's gonna be the first available the earliest reminder does is it less is it less than the current time if it is we need to send out that reminder whether there's an email notification or both we need to send out right away because we've set the reminder for for before so we need to send it out right away so just send it out however if this is at a later time on the current date then schedule it so there's two options one send it out right now because the time is before the current time or to the this scheduled time is after the current time and so we need to schedule it two options so let's look at those two options the first of which if it's less than now then gonna create the macro notifications create we're gonna do that right now if it's not else else that means the data is at a later time we can comment this out I can uncomment this application on time I just caught it out because we're not going to be scheduling anything now but this would work application on time right we're going to what are we going to do what what's the time this is the time here this is the date and time cv5 that's when we needed to run and what is it that we want to run at that we want to run this right here I want to run this macro at this time and application on time is going to allow us to do that so either a we run it right now or B we run it at this time so when it's commented out I just because I didn't want to schedule any anything happening while during the training I don't want emails to get created some crazy things to happen so application on time this is the time and what do we want to happen this so that's how we do it that's how we do it and next up so as soon as that gets done either notifications now or can so let's just assume we have three of them that need to be sent out now because the times before so then we go to the second record and that's notifications create this is the macro that creates those notifications right here so these macro so we can now we're gonna comment that out so let's go ahead and uncomment that out because now we can focus on this so now what I want to do is I want to create either an email or I want to create a pop-up based on the conditions and what are the conditions well the first condition is is does the user select it a pop-up yes if this is yes then send the pop-up if this is yes then send the email so in this case in this case let's just mark these as no no I should do this on the original data so in this case just one email will be created and one pop-up will be created so if we run this macro right here I'm gonna see that an email it gets created but of course in this particular email there's we didn't set anything as a subject in template so nothing's gonna show up we didn't have anything but also what we did have when we go look in the schedule we'll see automatically the route we have a pop-up that comes up peers and your review should be scheduled within a week email has been sent from Randy now you take a look at that tell us who where did that information come and that's based on the review template so let me go back into the admin and we look at our review our review is right here and we see here on our pop-up notification we have employee's name and reviews should be scheduled email has been sent from username so you see it's all based on this the email that we created didn't have any defaults so if we look under the events here when we look at that particular event under let's take a look at this one right here here peggy's review there's no email information here but how we said and he would have showed up all right so that gives you an idea of what we're looking to perform and let's go ahead and see how we did that in this macro now I've commented out some stuff just so it doesn't automatically create but it's gonna create a lot of them if we create if we pulled these out and we go ahead and create them it's gonna create them all as we'd created so it's going to create them all now if we run this macro it's gonna update let's go ahead and take a look at the events list and go back into there and you'll see that now we have just three now we've just three here that we've had because they've been sent so let's take a look back on that event and we'll see why didn't that show up again that particular event here in row 13 that now has a Centon right here you see that 1217 817 that's the current time that has been reminded and says so this is no longer going to come up in our filtered list because we're only focused on those that have blank than a blank and are after the current date so those are those are important right so set reminder for now we have a limited list so let's go ahead and see how we did that back into the VBA code we've got a lot of dimensions we're gonna use Outlook so we're gonna dimension the application and emails objects we're gonna do a whole bunch of Ivette information as strings we don't need to go on in some of course as dates events start on and on create we of course we have more strings employee information we need to define all that leave type all of the information associated with the event we need to information so that's gonna we also need to find an employee now I've got an employee ID here but I need to pull an employee address a human play email address right I've got the employee ID right here but what I need I need I've got an employee name but this may or may not be accurate what if we've changed the employee name that's why employee name is not so important employee ID is very important because employee ID is gonna allow me to go back into the employee list and pull out their first name the last name if I have the ID and it's gonna also allow me to pull their email address right here so we're gonna need that so based on their employee ID I'm gonna need to find their employee information so we need to set those ideas range so the first thing we want to do is set the employee ideas range that's gonna cheat - is where our employee list is located employee ideas the named range that we've set up we've been over that before just look up employee ID and name range you're gonna see that leads to the employee ID list we're gonna set the username remember sheet 14 b10 and user email b11 we've set those in the login information let's take a look at that just remember b10 is our user name b11 so i use your email that's gonna help us cuz when we send out emails we're gonna need to know both of those information all right with sheet 7 that of course is our events list we're gonna focus we've run our filter now now we need to know we know everything's going to be in row 5 we're just focused on the first one event named CB 5 that's gonna give us our event name event type event notes and cg6 so basically all I'm going doing right now is I'm going through and defining each one of these I'm going through the event name CB right event type employee employee ID and I'm just defining each one of these and putting those in variables right here so I want all those in variables so we do that because that information is gonna need their need to be in an email or perhaps in a notification so we need to pull all of those variables right because we have a whole list of possible variables here we need to get them all because that may be in an email message or it may be in a pop-up text so we need to get all that information all right so that's why we need to pull it through all right so you vent created on the start on date and on date leave type email to all of that information BCC we're gonna pull all of that right from row 5 of the filtered list so we've now they've got all that information now we're ready to put it in an email but the first thing I need to do is I need to get the employee name and email but to do that I need to pull up I need to get the employee ID we know that's in CD 5 I just showed that to you right that's our filtered list in the events list ce-5 right here that's the one focus on here we need that one time you to do is I need to find that employee wearing am I gonna find it I'm gonna find it in the employee ID range I'm gonna run use find and we're gonna find the employee we're looking in values and we're looking at whole values so once we find it if it's found we can set the row if not found is nothing remember nothing and not two cancel each other out so we're basically saying if the employee is found then set the name and set the email the name of course is going to be sheet to be remember I'm using the first name if you wanted you said like the last name use a different column right I'm using B B is where our first name is located in the employee lists right here B but if you want I'm using the last actually C is the first name B is the last name right so if you want to use the last name of our first name you can use it right here all right so that's how we do it let me set that to see I want to use their first name which is C and but if you want to use our last name or you want to use a full name you can do that as well the email is located in column am calm M's where their email so we pull that information using the found row the found row is here that's how we know the row all right next up we need to find if C N equals 5 then send an email YC and well if we look back on our events list and we look at our filtered data right in here we'll see that we have these email right here C n if this is yes yes or yes we need to send it and so we need to do that if necessary if this is yes do a pop-up and then continue on so let's go ahead and take a look at this if it's yes then we're gonna send the email so if it's yes we're gonna do all of this then we're and then after that we're gonna check for a note if the see Emma's yes that means send that pop-up notification or create that pop-up notification all right so the first thing I need to do is I need to take the email to the email to we're gonna take the existing email to whatever's been set here and we're gonna replace it with the possibility if the user has added in user email I want to replace it with the exact user email if they have added this user in the employee mail I want to replace it with the actual employee mail so there's two possible emails plus whatever they might have had that we're adding to it or replacing so that means any instance of this user email with the brackets I'm gonna replace that with the actual user email and again any instance where there's employee email we're gonna replace that with the actual employee email all right moving on BCC isn't gonna be exactly the same except we're starting with BCC we're starting with this then we're replacing any other possible instances of the variables with the actual emails the subject we're doing the same thing except in this case we're replacing lots of stuff where all of those variables event name event type we're replacing anything with brackets we're replacing it with the actual event so all of this code does is it replaces anything with brackets and replaces it with the actual notes so that's how we replace them so when the user has set specific brackets we wanted we want to take these brackets this this employee name practice and we wanna replace it with the actual employee name if we if we have specific end date and end time we want to replace it with the actual end date and time so taking those brackets and replacing with the actual values is very important all right next up what we need to do is we need to then once we have all the information we are ready to create we've created our subject the same thing we've created our message replaced all the variables and now we're ready to create the email so we're gonna set the outlook as to create the object outlet application we're gonna set create a new email with that using set outlook application and then with that new email we're gonna the two is going to be the email to the BCC is going to be the email BCC subject subject message message and of course if the attachments is not blank then we're gonna add the attachments we always want to make sure it's not blank because it'll create an error if it tries to add something that's blank so we want to make sure then we're gonna display it now in your case when you're ready to go and you're ready to launch your application you don't want this display you want to use dot sandé use Dotson to send the email without displaying first that's gonna create the email then we just cleaned it up so that creates our email next up we need to check for the notification all right we're gonna do is we're going to say if cm v equals yes what is CM v again that is in our events list cm right here that means they have requested to create a pop-up this is yes we want to create a pop-up now what I've also done here is I've created a formula I need to know the notification text what is the notification text well that is right here in the admin for example if this is training I need to know this so what I did do is I need to find the event type here in the admin incident I need to find it whether it's new hire training that's well remember there's only one each only one each so one instance so I need to find that one instances then I need to look all the way over here into column K and I need to pull this information this is the notification text and I need to take that I need to put it right here in the event so something to put it right here so to do that we can use index we're gonna index admin right this is going to be K 520 we're gonna index all those events k 521 through L 541 right here we're gonna index right here starting here excuse me indexing right here indexing those notification types right here we're gonna index those so once we find the row using match we're going to be able to locate it so we're gonna index these notification text and then moving next up on the events list once we have that index we're gonna use match we're gonna use the amass what are we matching we're gonna matching whatever's in cc5 what's in cc5 that's the event type right I need to know the event type we need to find the event type in that list find out what row it's on and then return us whatever's in that in column K throughout call Kathy rail because it's a merge style right so merge cell we're gonna use the first column which is K so we're gonna find it within the notification event types this is the named range for our admin right this is the named range so when we look here right and you scroll down all the way down to the bottom this is a named Ray right here here it is notification event type we're looking on that looking in that range we're gonna find training we're gonna find review once we find it we know it's on row 524 once we know it's on 524 we can index our notification text and put it right there alright so that is how we did that so that's basically what I need is I need to know the original information in this case of course it's set leave to begin so this case it's scheduled set leave to begin employee name start next right so that is the notification text for hold for leave for let's take a look here for leave right so when we go back on leave when we look at leave we see scheduled leave set to begin for and you'll see here as I've selected it's schedule you've set begin so we're just gonna return that information I need that information then what I need to do is I need to replace the data variables I need to replace employee name with the action employee name and I need to replace start or next date in this case just those two variables with the actual data so in the events list moving back I've done that in this we're gonna do that and I'm gonna put it right here I'm gonna put it right here so here's the default Dex write that with the variables and then I'm then using VBA I'm gonna replace I'm going to replace employee name in this case with am tzer's right and I'm going to replace the start with 9:30 a.m. to start data time so that is how it's gonna be set so that's how we replace it and now what I want to do is I want to set this pop-up to this remember that yellow pop-up I need to set it for this exact step so how do we do that well let's go ahead and take a look at that if we look and we unhide we show our panel and we see we have something called notification group if we unhide that and close this zoom out let's find where it is here it is all the way over there okay zoom back in now we see let's take a look at this so now we have this is hidden but it's very helpful we don't need it visible I just need it there so let's move it over to ourselves where we where where it's connected to move it all the way over this is our this is our hid notification group this is the one that we're gonna copy each time to whatever this one stays hidden but it's gonna be very helpful here it is now this one if we click on it we see it's connected to a cell cy5 cy5 so that means whatever isn't cy5 is gonna appear here so that way cy5 is here so that means in this case cy 5 is this CY 5 so now using VBA all we need to do is take this replace the variables and put it right in here so let's go ahead and go into VBA and see just how we did that all right so we know first of all I need to take cm 5 is just then notification text is equal to what are we starting out with the notification text where did that come from all the way up here under notification text cx-5 is our notification this is what we're starting with once in cx-5 again here cx-5 this is our starting point for our notification text includes of course the variables so we're gonna start with that but then I'm going to replace again using replace I'm gonna replace every instance of anything with brackets using this replace anything with brackets for example end date and time with brackets we're gonna replace that with the end on we may need to format these a little bit better later on so I'll check on that but we had so much to cover there may be some formatting issues we may want to format in his date times or both so we'll focus on that a little bit but it may not be perfect just yet but you get the best so basically any time there's an instance of employee name we're gonna replace that with the actual employee name and so on just like we did within the email and then what I want to do is once I have this complete with all this I want to take this and I want to put it in CY 5 cy 5 equals the notification text CY 5 is the connected cell right here so if we were to place this with you know gibberish it's gonna change automatically in that note automatically so anything we change to CY 5 is gonna be placed on that note it'll zoom in a little bit so you can see it but you get the idea then what I want to do is okay I've got the note but I don't know first of all this note is hidden right see normally it's hidden and I don't know maybe the users gonna be on the admin maybe they're gonna be here we don't know we don't know what screen they're viewing we have no idea we can use active so what I want to is I want to take that note I want to copy it so for example let's say it's here right I want to copy it ctrl C right click or right click copy and then I want to basically place it on whatever they're doing whatever let's say they're on attachments or looking something I want to paste it right here I want to paste it right on whatever screen they're looking at and then I want to assign a Mac I don't want to give them the ability to close it right click a sign Mac or this Mac has already been created it's called notification close and all that does is it deletes uses the active sheet and deletes a shape called cancel that out deletes the shape called notification group notification group is the name we've associated with that we can just delete it so closing it's actually gonna delete it we would not want to create too many we just need one so it's knows no problem to delete it because we all we did was created a copy so we can delete it no problem all right so once we're done I wanna this wraps the text the reason we do this is because if not this is a large text file under events go back into the list this is a large text file or this is it can be and this this five is gonna get this big it's you know when there's a lot of text here we don't want that right so when we wrap the text it automatically resets that road to back its normal size so wrapping the text keeps that row from enlarging we don't need it as a we just need it as a standard row height and that's fine for our purposes so this range five this covers it the entire row is gonna wrap tax equals false just so it doesn't wrap and the row remains to stay the current height next up we're gonna take the shape we know it's on this sheet right we know it's hidden but it's there notification we're gonna copy that and then active she we're just gonna select any cell in the active sheet whatever this the sheet the users on we're gonna select a one and then we're gonna act as sheet we're gonna paste we're gonna paste because we've copied that shape we're gonna paste it right in there and then of course I want to reset the height often every time when we're pasting it does get reset so I want to reset the height and I want to paste it right you know I want to set the height to the proper height and the width to the proper width just so it doesn't get realigned and then we're gonna set the left and top to the actress L that's gonna stand put that notification in the upper left then the last thing is we're gonna make it visible so that is how it works and the last thing is we're gonna act so we're gonna select the active sheet just to make sure that we've selected something other than shape I don't want that shape selected I want something else shape so this unselect or we could just select any cell to do that that's pretty easy next up what we're gonna do is we're gonna set the reminder now I need to know with whether we've set the email or whether we set the notification we need to make sure that this reminder doesn't go out again and to do that we need to know what I want to do is I want to put the current date and current time here but not in here I don't want to put in here this is our results I want to put it in right here in the original data I want to put it right here call them our I want to find the row and I want to put it right here whatever time they've been reminded on the date and time I want to put it right here and to do that we need to know the row but the good thing is we brought the row over this row is included automatically in our data table so it makes it a lot easier so when we know the row the rows here I know the original row that's why we included in the filter data so if the row is 25 and we know the column is all right we can easily bring it right over and bring it right down and put it here so that's just that we're gonna do so I'm gonna pull the row right out of CW take that row and then I know the column columns are columns aren't and put the current time in that's going to keep that reminder from running again we don't want it to run again because it's been done so we can do that by simply saying our in the event row where's the event row well lets way back up here under event row right here event row cw5 that's just gonna pull the event row so now we have the event row so now it's it's very very simple to to place that current day and we just do now that's going to place the current date and the current time and next up the next thing I want to do is I want to run these notifications once more we've only done one row this is one row but now I want to run I want to run this all again I want to run this entire check again I want to go through the entire list again because it's not gonna include the list so when we run it when we run that check we're gonna see that it goes back and forth and runs through all of them just to check in this case look approve this particular created two different ones why two different ones will free look on our reminder we had we had two different we set the emails to no so it didn't want to create any mails but it did set two we had two different termination so now we just close that and close that and close that so we had three different so now they've all been set we had those emails to set to no so it didn't create the emails but we had yes set on the notification pops us so it's gonna create pop-ups for all the notice notifications that you need so that's really really handy so that is how we covered that so that's the end of that and this of course we have one to schedule I'm gonna keep this off here but I want you to reset it just for now we don't want these applications until we're completely set but once we're ready we just uncomment it out and it's gonna automatically create those notifications on a specific time I'm gonna keep that comment about this keeps things from going on you know on your computer when they're unattended because we're not quite ready with this the application work or just developed we need to do more bug testing there's probably few bugs in here I'm going to continue to test it as I build this and then of course we'll have this ready for you shortly on Tuesday as always so we've gone over a lot let's go ahead and do a little refresher we've gone through this how we are going to run an advanced filter to those without reminded on I'm gonna sort that list based on all those and then we're gonna create whether it's an email or a pop-up reminder for those that are required at a specific date or automatically if it's before the date we've created a login screen we've created a brand new login we've showed you how to remove the border and hide the passwords okay and close how to design a special screen like this so we went over that win or Pixlr how we use that website for design in the admin screen we created a brand new tab called event notifications what we have the ability to create multiple notification defaults based on an event type and we can create one for each event type and that includes an email defaults as well as a pop up notification vaults using multiple dynamic variables we can create whether it's adding new or deleting we showed you how to do all of that so that's it we've gone over a ton of new features in this it's over extended training I hope you like it as always I do appreciate your shares likes if you do get a chance please check out my dashboard course if you can it's getting great reviews and people are really happy with it so you'll love that include the links down below for that as well alright thank you so much for joining me have a great day [Music]

Channel: Excel For Freelancers

Views: 23,959

Rating: 4.958549 out of 5

Keywords: Excel Pop-Up Notifications, Notificaitons in Excel, User Login Excel, Excel User Security, Excel User Login Form, Excel Login Userform, Excel Userform Login, Excel Notifications, Employees In Excel, Excel Email Notfications, Sending Emails In Excel, Dynamic Pop-up Notifications, Excel Notfiications

Id: o1KdIaug6ac

Length: 120min 41sec (7241 seconds)

Published: Tue Dec 18 2018