Learn How To Create This Yearly Activity Scheduler In Excel [FREE Download Inside]

hello this is Randy with Excel for freelancers and in this week's training we're going to be creating an amazing yearly schedule in this scouts can be a full year users will be able to change the year or change the start date and they're going to be able to schedule any type of activity on any day and have that scheduling data saved automatically as they entered it and automatically pulled back up as they select any day in the schedule it's gonna be a great training heavy on formulas very light on VBA so it's gonna be great for those beginners let's get started all right thanks so much I'm really glad you could join us today each and every week we create these dynamic and amazing applications right in YouTube so if you have not yet please go ahead and subscribe and don't forget to click on that notifications icon Bell that's gonna alert you that we have a brand new training each and every week and also just as with every week that brand new application is gonna be available to you using the links in the description so you can always download it for absolutely free we have through Facebook or through email just click on the links in the description and it's absolutely yours in this week what we're gonna do is we're gonna create a really dynamic yearly activity scheduler and what I want to do is I want to create a calendar base have all 12 months visible on the screen along with the daily activities so the idea is for us to be able to click on any specific date have those activities perhaps and display here enter any of those activities in a column and then have those activities automatically saved so when we click on that date again they're all there I'm putting in some conditional formatting we want to know the current day we want to know the selected day and I also want to have a dynamic starting day maybe you want your day starting on Monday or Sunday or whatever day we're gonna have that and of course changing for a year so that's what we're gonna be covering and I can't wait to show it to you schedules can be really incredibly powerful especially in Excel and I can't wait to show you how to do that before we get started I just want to let you know it is a difficult time out there I do understand that n is no better time than to learn your skills to up those skills so you can work from home or from wherever you are in the world to help you do that I've created these training videos each and every week I also have a mentorship program that's gonna help you define design develop and deploy your own applications so if this is something you like you don't have to get fired from any job or let go from any job ever again once you increase your skills learn how to make and sell your own applications and work from home so I hope this and this training can help you the mentorship will take you that extra step and get you all completely financially independent so I really hope to help you also just in case if you like these applications I've got over 100 of them for just $37 I'm gonna include the link down below that really helps us out that keeps these training videos free each and every week so thanks alright well let's get started on this week's training so basically the only thing I've done this is kind of sighs some columns to help us out a little move things a little bit quicker other than that everything we're gonna be doing during this live training so let's get started what I want to do is I'm gonna highlight all of the columns here and then I want to just give it a nice look just as we always do in this one in this one we're not gonna hide columns and B as we have in the past so just so we know there's really not going to be any hit and this one's gonna be extremely light on VBA and there's gonna be a little bit of on cheat code and we're gonna do a little bit more of specific types of formulas so we're gonna be doing a lot more formula work on this sheet as opposed to VBA so it's kind of a nice change of pace and you get to see something really creative that we can do that you can use in your business or in your home so all I did was I first format of these two rows and I'm just gonna give it a title here something like let's call this yearly activities scheduler and then I'll increase the font and bring it let's see the font we probably put it I want a large fun on this so we'll do something like 22 and then I'm gonna choose something like Arial rounded which is a nice one then one of my favorite ones Arial rounded empty so that's nice and I'm gonna merge the center that maybe all the way to let's just say a H merge ascend to that and I want to put a little icon on I've got one saved here just something and I'm gonna change the color and the size of that so that's gonna give us a nice look not too big just something small and I'll change the color something closer to our theme here same thing with the font give it a give it a nice color closer to our theme like this alright good now we're set up what I really want to do is I want to have the year up here here so we're gonna make C B and C the year so I want users to be able to select from different years so let's merge and center that and then I wanted them to be able to choose the year so let's put that in D and E for merge the center and then on the left I'm gonna create a data validation just something that we can have basic years so you can choose of course you could increase this let's just go to 0 to 0 to 0 to 1 and of course you can increase this as much as you like just going to give it a basic so users are going to be able to choose between the year that's going to be our year in fact I want to assign a name for this right away just so we don't forget we're gonna call this scheduled year let's give it a name rates I also want to give the user the ability to have a start date so in F G and H let's just say F G and H I'm gonna merge this into this we're gonna call this a start date the date of the week is important start let's just call it day and then what I want to do is also a drop down list of days of the week so let's merge that so we have enough room because we're gonna be putting in days of the week here some merge the center of this I also want to create a list of days of the week that's very important so let's do it over here let's call this start out with Monday and then we'll drag it down so we can include all the days of the week all the way through Sunday Monday is gonna be our first one and we're gonna put that in AJ 5 through AJ 11 I'm gonna sign a named range on that also and we're gonna call that days so as we clear I also want smaller days what I mean by smaller like just maybe abbreviation so mo maybe two words tu something like that because those are going to be the headers for our days inside our calendar so let's just create those W E and then th fr-s and then su so what I want that is a lot of small abbreviations because those are gonna be for the day headers inside the calendar I'm also gonna give this a name I'm gonna call this day abbreviation so let's just say da y a b b because we need to know those because we're gonna have dynamic days so we're gonna have the calendar is gonna change based on that so we've got two of them set up okay let's continue on let's let's color this and colored blue basically what I want to have now is once we have the start dates and I want to put this as a data validation so I'm going to enter data validation here of the days of the week so that users can schedule any of those it's going to be a list and then we're going to call it just days equals days that's the one we just set up now we can schedule it will start it off on Monday why not and we're gonna ready to start building out our calendar now so we're gonna put the first calendar in cell let's try B far let's move this up we've got enough space here let's move this up a little bit too and create them a little bit more space let's put it in b4 b4 okay so we're gonna create our first one's gonna be I want January to appear in here so I'm gonna put yeah supposed to be January I'm gonna put that but I don't want just any January I want to actually put the date in here I want to put the date why because we're gonna use that date so for example I want one one and then let's say but I don't want just one one I want to make sure the year is dynamic so how do we do that we can get over the formula equals date and then what do we the date the year is the scheduled year cuz that's gonna change one one that's gonna put us January but now I don't want to show this I want to actually have merchants senator there's all I'm going to use seven columns because that's gonna be for the week so we're gonna merge and center this merge and center and then I want to show just the month only so how do we do that we go into number formats we show something like mmm mmm and then for M's is going to get us that that's what I want January okay that looks nice and we can bring this back down to what the others are so we can 3.29 32.8 okay so we get the idea now what I want to put the days in here but those days have to be dynamic right because it's gonna be starting if it starts on Tuesday I want to show T you here if it starts on Monday show em oh here so how do we do that well we created the day abbreviation so we can index it so the first week day would be an index so let's take a look at this equals index what are we gonna index Inc we're indexing the rate we want the abbreviation so that would be the day abbreviation then what is the row number will the row numbers based on whatever they have in the start date right so we need to use a match for that so match what are we gonna match we're gonna look up Monday and we're gonna find it under the days remember we created the days here and we want an exact match so it's gonna be 0 and then comma 1 just the column is 1 there so that way that looks right so if I change this to Tuesday it's gonna change the Tuesday and Monday ok good that's exactly what I want for the first one but what about Tuesday and the remaining days so let's focus on the next day formula it's gonna be based on this right so what's gonna based on the first one because we want it one after that so equals if match we're looking for the mess let's find this Monday this day abbreviation under the day abbreviation right I want to find that day abbreviation this one right here I'm gonna match that we're looking up for that we want an exact match once again if that equals 7 what would that mean that would mean it's the last day if it's the last day right if it's found that it wouldn't be 1 more then it would be this one if it equals then what then if it's in fact and this is Sunday then I want to go to Monday otherwise basically it's the match plus 1 so for example if it's found on Monday it would be Tuesday plus 1 so how do we get that in that case it is index we're gonna be indexing 1 the day abbreviations because that's what we want to find the day abbreviations and we're gonna match the row match of course plus 1 we're going again we're gonna match this day breathe the B 5 again comma with the day abbreviations right we're looking that comma 0 so basically we're looking up Monday but we're gonna supply 1 day after match this if we have the match but in this case the row would be plus 1 so we don't want to find Monday I want to find Tuesday in this case so plus 1 and then comma 1 would be the number perfect okay good so we just added the additional parentheses on so now it's Tuesday excellent so how does that work so let's go over that again remember we're looking up Monday I'm looking it up if this day is the last day the next day would be Monday right but if it's not the last day if it's a second or third all we need to do is look for the second one the second the one right after the current one that's all gonna doing so if it's the last one we're gonna go to Monday but if it's any other one we're just gonna go to the next one down on the list now we can drag that over to all of our days and it brings out our now we have Monday Tuesday Wednesday and we can Center that now we have it so now let's just change it to see if it's working Tuesday it's gonna go Tuesday Wednesday it didn't work on the last one so we have to update that code there why is that so in this case well we have to make sure that this day here B 5 this a K is fixed so f 4 that now when we drag it over we want to make sure that that never changes so now we drag it over it works automatically Tuesday Wednesday Thursday now we change it to Friday Saturday okay good let's get a little bit of formatting now that we got this I'm gonna create some I'm gonna add some formatting to make it look a little bit better so we get the idea we're gonna create that one month and then we're gonna be able to easily duplicate it for 11 more months let's increase this fonts lay something like 14 and I'm gonna give it a format let's drop it in the middle and then won't get as format this I want to give it a white font and then a fill so let's go with a font of white and then maybe bold and then let me give it a fill maybe a dark blue something in the same theme with our current so we'll give it a let's say this dark blue color a fade out and then I want to do the bottom one the one below a little bit the same again white font and then right click and then format the cells and then I was just getting you give it the same I'll give it this one a solid fill which is dis here and then we can also go to bold on this so we can see how our first months can appear that looks pretty good let's make sure everything's centered which it is okay good now we can build it out now we want the total number of rows possible of course we want six total rows possible so let's count down six rows and of course seven over and that's going to build out our total Slits format this I'm going give it a format and then I'm gonna do a raw a border of a solid blue it's the same as our theme right around here but and then I'm going to give it an inside of a dotted line a little bit like this nice okay I'm gonna do the same thing up here I'm gonna give it the format the cells but I'm just gonna use the solid blue here all the way around and then inside we know it's not necessary we're good to go on that okay so that gives us a nice look now we have understand of our calendar it's looking good and we just need to add in some data for the formulas for the day so let's give let's give that a try before we do just fill in the fill in this and this and I spelled that wrong so I'm going to update that pay special I'm gonna paste the format's on that and then let's just merge and center this and then we will change the spelling on that okay good so we have that and also want to give this the same theme so right click format the cells and then the border using that same blue border surround it and then put a dotted line in the middle like that and that's gonna give us the same look perfect that's the way I want merge and center this one and then right justify it make sure this is also right justified okay so we can select between years we can select between the days now let's go ahead and fill in the days on this so we need to know based on the days of the week let's put it back to Monday because I'm familiar more with that so let's go and add some code for this what is the first one what do we want to do I want to know if the first day of the month is on a Monday then put one here otherwise put nothing here so that's the code the formula that I want to write so how do we do that we're gonna use the weekday often the week is gonna really help us out but let me familiarize yourself with the weekday so when we use week tick equals weekday and then we put in the date let's just say it's any weekday equals today right then what is this is what I want to show you when this is important was it one two three or four look at our date show they start with Monday and they go to Sunday starting on Monday go to Sunday so that for that reason we need to use number two and we're looking for specific days of the week we're going to be using this code that starts with a 2 which means Monday through Sunday 1 through 7 so 1 me Monday - means Tuesday and so on so that is the code so when we use weekday function we always must use the two here okay so that's very important and it's gonna let us know that the current date of today which of course is Saturday is 6:00 Sunday being 7:00 so that's perfect that's what I want to show you so that's important to know that we always use that too and that is why because our days start on Monday so with that said what I'm looking for is January 1st on Monday or not so we can write code to determine that equals if weekday of what of B 4 which is January first comma - remember we're using the 2 for weekday equals what is it equal it equals the start date number let's write some additional code so we can do that I want to make sure that we know the start date number I need to know now I can use match here right I can use match to determine which day but I can do something let's write something even easier that that's automatically done and let's put that right here so I want to know the start date and I don't want to add in a formula and an image so that we can refer to that automatically so let's write that in but first I want to apply a named range to this and we can call that start day so that's gonna be the start day so whatever that changes so now we can add that into formula we can put that up here all the way over here so basically what I want to know is there's a starting one two three or four I want to put it in a numerical format we can do that using a formula equals match start day days right we're looking for the days the days of the week we're looking to see which number that comma exact I want an exact so I want to know what is it so if the start day is Monday I want this to be one let's put that over here if the start date is Tuesday I want it to be 2 so just like that just like that's really important okay what else do we need so this is going to be called the start date in numerical order okay so and now I want to put this in a named range so we can easily refer to it and I'm gonna call that start day number start day number that's important because I want to refer to this start date in the formula or this is gonna change as we change it it's gonna change so that way we can refer to it we don't need to use this match inside the formulas makes our form is a little let's get back to the month so now the first day would be equal we can use it much easier equals if weekday and then what does that weekday of before remember that's the first day of the month B for coming to remember twos for our month week it would be this one right here too if that equals what if it equals the start date number equals the start day number then what do I want to do then I want to put in b4 which is the first day of the month otherwise leave it blank so basically if it doesn't match its gonna be blank in this case of course January doesn't January with the first is on a Wednesday so it's gonna be blank ok so that's for the first day but what about the second day and that would be for Tuesday the second day is a little bit more complicated because it's gonna be based on the if this has a value or not so the first thing we need to do is say equals if what is it b6 does not equal camp T then what then b6 + 1 right b6 + 1 if it's not empty we notice the first Alba need to add one but what if it is empty if it is empty then basically what I need to do is I need to check that if the first day of the month happens to land on a Tuesday or happens to have the start date of this number here so how do we do that same thing if weekday again of what again same thing to start the start day of the month this one right here before January first on the 2 we're always gonna use 2 equals what in this case what I want to do is I want to use mod cuz that's gonna get us the remainder so I'll show you how that works in a minute mod mod start date number seven because that's gonna be our divisor seven we wanna add divide it by seven days plus one then before then show so basically I'm determining if the current day divided by seven plus one equals what equals the current week there the first of the month then what I want to do is I want to say okay that's the right day of the month and then in that case it would be b4 otherwise the blank and then double parentheses to close it let's take a look at that again it starts on a Tuesday so that but let's take this a little bit further and that's copy this formula in here I'm gonna copy it now to Wednesday but this one's a little bit different why is it different in the third day what we want to do is we're gonna actually but in this one it would be plus one start day plus one in this case is 4 3 3 1 so this is right now we have the first one but we don't want 4 3 3 a 1 I don't want I want to format this date so let's in fact format them all they're all gonna be date so let's highlight those because we don't want this this is gonna actually show the data if we show the date is it right let's check it out it's right that's exactly what I want but I don't want to show the full date I only want to show the day so how do we do that we're gonna highlight that and go into more number formats and of course we're gonna go into custom actually and then just gonna type D all I want to show is one day and click OK and that's perfect I want to show one and then I'm gonna Center that that's exactly what I want to show very nice ok so what about for the remaining days it's exactly the same except we're gonna change this so we're gonna add two three and four for the Romania's so let's do that let's just copy this and then I'm going to go to the next one and then instead of plus one I'm going to change this to two ok very good but we do need to update this this of course is no longer we need to make sure that this is gonna show c6 it's actually going to be c6 because it's the next one over and change this to c6 and now what we can do is we can drag this over but we need to make sure that b4 stays the same we don't want to interrupt we don't want to change before so it's got to be changed at the c6 but we do want I don't want to keep it because I want to copy this for all them so what we're going to do is we're just going to make sure that we update it c6 so down update it to c6 and then what we're going to copy this and I'm just gonna update it just for these rows here and then I'm gonna make this of course d6 and then update this to +2 that's it that's all we have to do tab over that same thing paste it in and of course in this case it would be a 6 so it just changes to e we're almost done here and place this two numbers we're gonna add three good and one more time again in this case it's going to be f6 f and it's gonna be different for the formulas F and then change this to four and then we have five and then the last one is going to be five so this one's going to be based on g6 which is the cell below it g6 change this to add five and that's gonna get us our first row of dates perfect and let's change the start date to make sure we know it's working exactly and four start dates on Wednesday perfect that's what I want I want those days to show no matter what the start date is so what about our remaining rows our remaining words let's take a look at that in this one basically what I want to know is equals if and this is not necessarily gonna be the case but it's easy to copy down this formula this equals empty then of course make sure it's empty that's the first thing we have to do also but this formula wouldn't apply necessarily to this sub this can apply to every so it's really easy to copy and paste this down because generally the eighth day of a month is never always never gonna be blanked but in this case it's easy to copy down so what do we want to do if it's not empty then I want to run a check if what I want to do if what if the day before and the current day aren't same months then leave it blank so how do we do that if the month so we can do that the month of h6 in this case h6 does not equal the month of h6 plus 1 H 6 plus 1 in that case what do we want to do we want to show blank blank nothing because it's not different month otherwise assuming it is the same huh all we can use h6 plus 1 and then close the parentheses close parentheses one more double closed and enter perfect that's what I want so I've got the six what about the next day the same thing pretty much the same thing we just need to change the cell so let's copy and paste this and instead of h6 we're gonna be it's going to be considered b7 so instead of this we're now focused on b7 b7 and now we can easily cop get over b7 again and then change this to b7 one more time and then b7 okay so now we've changed them all so now we know so now all I need to do is copy we're almost done with the formulas here and then paste the formulas across paste those formulas now I can copy an entire work week copy that and paste the formulas all the way down here and paste the formulas perfect that looks really good let's just change the date Tuesday nice very good so now it's starting to come together now let's work on a little bit of the formatting I want to make sure that the formulas are correct once I have the formulas once I have the formatting it's easy to copy and paste the different cells so let's do that all right the fonts are a little bit small what are we updated to let's say twelve one up bigger and make sure everything is centered in the cells just the way I like it now what I want to do is I don't want to show white for the days that don't exist I want to use conditional formatting let's apply that so what I'm going to do is use a conditional formatting and I'm gonna create a new rule and I'm gonna use a formula basically it's if it's blank I'm gonna format it and just give it that blue color the blue background oh we're using it so there we go that's the way I like it now let's add this blood blue here okay good it's starting to come together I also want to create an activity schedule here so let's do that I want to create something called I want the activities to appear here so let's do that call that daily activities and then I want to put the date here a specific date here of the activities now I want the activities to list down here so let's just put in temporary date here but I want it to be a long date I want it I want it to show the full day so a long day because we've got lots of space and then I'm gonna Center these two and give them basic the same look and feel as these so format those cells and I'm gonna give it exactly the same Phil so Phil effects go down and then use this blue and then use this darker blue and then we're gonna use the same one because I want the same look and then I'm gonna change that to white font in a second format those cells actually this was just a single color so we can do that with this single color then highlight both of these and change those to white and bold that's nice I like the way it looks but it's got to be a little bit bigger because I want that to show I want to be a little more clear so we can change this to maybe 14 okay good and will increase this row a little bit that's a little bit too much for the month okay I think that looks really good that looks nice now we can see it clear so basically I'm gonna create let's just go down to say row go down to 29 and I'm gonna format this not on our daily activities to be shown here so I'm gonna give the border maybe thick border a little bit clear so it's clear using the same color that we have a thick border all the way around and click OK and then I'll do this maybe that's pretty good at way it is I can give it a border a little bit dinner border here here and here okay we're good we're good with the formats and what color this will do some more coloring and formatting let's do the conditional formatting now we've got more so basically what I want to know is when you user selects a single date here I want that date to show up here in the code and I want all the activities for that data show up here and I want to know exactly what date was selected so how do we know that well we can use conditional formatting let's click here man add a new rule and this is going to be an and because there's two conditions one it can't be blank and two it has to be the date so let's show you we'll use a formula that equals and I don't want we want to make sure that it's going to be based on the this is not blank and what is the other condition that this the first day the first day possible day equals age so when those two happen I want this to go red but we can't it's not it's gonna be for every single day in the calendar and meaning every single day in the calendar so what we need to do is get rid of the absolute before the B and before the six cuz it's gonna be used for every single day however this age five will never change regardless so that should stay absolute just like it is so we're gonna format those when I want something very distinct maybe this background and then a font of bold and white so now I want that data color to change as we select it it's gonna be very distinct that's what I want perfect so if I change this to three two one three January third is to change here and that's what I want so when we select a day that day is gonna change here and then all the activities gonna show up and we'll know what day is like it one more I want to know the current day it's not currently in January but I want one more so we're going to add a brand new conditional formatting and new just gonna use this something and I want to know if the cell contains a value a specific value so we're gonna sell value is going to be equal to and then it's going to be today equals today so we can have a formula on that so in that case if it equals today I want to make sure that give it a specific color make sure that doesn't go into quotes fill give it a fill called that's enough for just a little something a little bit slight okay let's just I want to check that again to make sure they didn't quote surround it no it looks good just the way it is okay because today is in March so obviously it's not going to show up until we get to March all right I think we're really good with the calendar now we can go ahead and duplicate this for February so let's just duplicate this and make a change I'm going to skip one and then paste it here now what I'm gonna do is I'm going to change this to the month of February this case I wanted February and what do I do for the date in this case I want to make sure that the date got properly matched so we need to if you'll see it's the wrong Center so what we need to do is make sure that we add some absolute here I want to add some absolute right here because we need to make sure that I - never changes right that's important but we also added a date didn't we add a date it's also if you notice remember if we look on I - that is the start date so we can do is we can just add that to that instead of instead of using the formula we can now add it to start date so we've added that now it's still gonna work so now we can copy this paste it over paste the formulas perfect good now we've gotta just the way I like it and we can now ready let's just let me update the column to 3.29 so that they're all the same all we need to do is highlight them and then set them to 3.29 that's the way like everything I want the same one to do the same thing for this making sure that they're all the same so that they're all exactly the same good okay so what I'm going to do is I want everything to fit on the screen so what I'm gonna do is I put four months here and then I'm gonna go four months by three months so that that way everything is once I expand everything will fit here so how do we do that well let's just copy now we can copy the entire month now that we've had it copy this and paste it over there paste everything including the format's that looks very very good all right so we know that February starts on Saturday and all we need to do is now we need to just copy this and paste it over again paste it here and then change the month all when you just change the month very very easy now and look it's March 21st that's the current date according and we see that our conditional formatting is working just fine on our current date and one more month I want to add April in so again skip a row and then add in April change this to the fourth month very good now all I need to do for the remaining months is just copy this see how quickly it builds now that we got the calendar straight and then change each individual month this of course will be five this will be six seven eight one more time to get all over Kalam's right copy this paste it down again nine tab over ten eleven and twelve that's it we're done now perfect so we have a very nice calendar and it looks really good and we created it rather quickly just by using the right formulas each time let's add in some formatting so that the rest of it looks good I'm just gonna add in that blue background to the remaining cells so it gives it a nice look and feel we can go all the way down here and then all the way up here so that we can give that blue background and it's looking really good okay so we have that we can extend this we don't need to see that that's what I want our scouts just to look like let's add the middle rows here and here and then we're gonna get rid that's nice okay so we don't see any more gridlines our schedule is is coming together quite nicely we can extend this sorry now what I want to do is now we're ready for the VBA let's just check a few things let's change the year yeah it updates everything updates accordingly just the way I wanted to so what I want to do now is I want when I select a specific cell using VBA I want this date to change and I want to load all the activities that were scheduled on that specific day and for that we're gonna use VBA so it's ready but it's only a little bit of VBA today so we're gonna be able to cover it really quickly if you're new to VBA don't worry about it I'm gonna walk you through every single step so how do we get that done into the developers if course you don't have the developers tab you can get that available just clicking the options and then going in to the customize ribbon you can select the developers make sure that's selected there's a shortcut to get you into the VBA that is all f11 you can also select here using VBA and we're gonna not even going to create any modules today all of our Macker are gonna be on the schedule sheet we're gonna create basically macros when we make a change we want something to happen when we make a change we want something to happen we want the information to be saved where do I want to save it well basically what I want to do is I want to create a brand new sheet either automatically with VBA and if it doesn't exist each sheet is gonna be for each year and all of the information is gonna be stored on the year and you can probably hide that sheet you won't ever need to look at it but what we're gonna do is sort of create one chief for 2020 one chief for 2020 one and so that way excels store all the information for these activities on the other sheets based on the year number when we select a specific day it's gonna locate that data store based on the year based on the day and it's gonna bring it all back here when a user makes a change it's gonna take whatever it is the user made and it's gonna store that in the appropriate cell in the appropriate sheet automatically we can do that with a very little bit of code I've perfected this process I've been doing scheduling for 20 years now so I'm gonna show you just how we do that so let's go into the VBA and the first thing I want to do is when a user makes a selection on any cell the first thing I wanna do is change a h5 to that date whatever they selected and what are they selecting they're making a selection based on anywhere from b6 all the way over to a F 29 so that's the first thing that we're going to write and that's gonna be based on selection change so how do we get to that worksheet selection change it's already selected for us first thing I want to write if the user makes it a very large selection I don't want to have to create an issue so if they select anything other than a single cell we want to get out of that we don't want to do anything else so we can write some code if the target count large what that means is if they've selected a larger cell number of cells is greater than 1 then exit the sub just in case I want to make sure that we're not using that exit this up okay so what do I'll store I want to do on selections change if not intersection the specific and I use autohotkey to automate that in case you saw me type really fast I use a software called autohotkey it's free look it up and it's easy to make automated typing like I just did okay so if it's a specific range where does that range again it's b6 I had mentioned all the way through a f29 once again if a user makes a selection within that range what do we want to happen or we actually what I really want to do is not only don't want to make it blank I want to make sure it's actually a date so if the user makes a change to a date I want to make sure that it's actually a date they selected or not here not here not here did are they selecting a date if they do then take that data put it here so how do we do that well we can use something called is date for that if is date what is that the date target value and that means is what they selected an actual date we can use is date for that equals false if it's false then where there's nothing we can do then exit sub that means if it's not a date if it's anything other than a date then exit it sub there's nothing else we can do so we must leave okay moving on it is a date at this point because otherwise they're gone so then what can we do then what I want to do is I want to actually define the selected date we're gonna be working on that we're going to be working out with a few different match dimensions some macros I'm gonna do it up here X we're gonna use that both for selection change and worksheet change so let's we're gonna eat some macarons look dimension some macros I'm gonna walk the know the year name has long yes it's long because it's actually a number and then I want the day column because we need to save the information in another sheet and I need to know what column it is so it's gonna be day column as long and I also need to know the row that they're making the change on cuz I need to know where to save that so we're gonna say day day row as long also what else do I need well I need the data sheet as a worksheet dimension the data sheet the sheet that they're gonna be saving and remember they're not saving it they're saving on a different sheet as worksheet and what else well I also need to know to select a date dimension the selected date as a date okay that's all really we're gonna need the variables that we're gonna need so now we can say the selected day is equal to the target value and that's selected date date okay so we have the selected date so now I also want to know the year name that's gonna help us the year number excuse me your number your name same thing either way because it's gonna name of the sheet but the number of the Year equals what does it equal equals D - right D - right here don't worry about that D - so okay so that equals D - let's put that in range D 2 dot value what's another way we can write it there's another way we can write it we can use the scheduled year as a variable since you defined it we could also do SC year that's gonna work - in brackets that works just fine as well just an alternate way this way is easier to read so you know what it's like since we've already make sure you name the range just as this what else also range a5 remember aah five hits aah five this one needs to be this one needs to be the selected date right age 5 so let's do that let's finish up that ah5 dot value equals selected date okay that's good let's just let's just run that code and see where we are making sure that we're getting that's changing accordingly so we're gonna select something perfect you see how the select date changes to automatically to orange that conditional formatting and apply no matter what we select and has changed that's what I want my birthday in case you want to get me a gift okay so moving on we also have more to write so we also what else do I want to do I want to actually load the information in here right so would we use let's just say 2 o'clock p.m. lunch with Fred okay so once I write that if I load all the information in I want to save this somewhere where do I want to save it well I want to save it on another sheet why do I want to do that because if it saved on another sheet when I click on that day again it's gonna come back I want to all come back to this so how do we do that but there's something else I want to add while I'm added here I want to add some conditional formatting when the time slot is filled I want to conditional formatting that's really easy let's add that conditional formatting new rule right format cells that are not blanks not blanks and I just want to put a format of glue so it gives it so I know that the time spent filled okay that looks nice okay let's save it oops almost forgot to say that would not be good all right moving on what else do we need in our code well I want to do more things what do I want to do I want to know if the worksheet exists remember what I'm gonna do is I'm gonna create brand-new worksheets automatically through and I want the name of those worksheets based on the year based on this year so that way every year it's gonna save different data so we always have a different date it doesn't get confused so the first thing of what I want to do is I want to determine if the worksheet exists because if it's not I have to create it we can do it through VBA so determine if worksheet let's just call it data worksheet exists so let's how do we do that well the first thing what we can do is write on Air resume next we need to do that because it's gonna be create an error if it doesn't exist so this is a good way to check set the data sheet which we've defined up above as a worksheet equal to what's it going to be equal this workbook just one way workbook dot sheets what's the sheet name the sheet name is gonna actually be double quotes it could be an issue our sheet name is a number it's created it could create an issue because it's only a number but when we add double quotes before it and after it it doesn't create an issue and it's automatically considered text year name right we've already defined that as a scheduled year and double quotes because that way it's going to treat it as a text okay so we're gonna set that and then on air go to 0 now we just need to do a run test if if the data sheet is nothing we know it doesn't exist so how do we do that if data sheet is nothing then we know to create it so we got to create it because it doesn't exist if it does exist it will be something that we don't need to continue on so how do we created this workbook dot sheets dot add we're adding a workbook and what are we gonna add it well I want to add it after a specific sheet after equals which one sheets I've already named it scheduled after the scheduling sheets scheduled and then what I want to do I want to give it a name dot name what name don't want to give that sheet equals the year name so that's it that's all we need to let's try that out and see if that works so we're gonna go and now what we're gonna do is we're gonna select it and automatically a brand new sheet 2020 is created well that's good but the only thing I don't I don't want it's perfect that's the way it is but I don't want to activate I really keep the activation here I don't want to go there's no need to go to 2020 we just need to keep it on the schedule so let's activate the current sheet because we're on the schedule sheet all we need to do is right activate okay good so now we've created a sheet because it doesn't exist and we've activated the current sheet so now we can continue to write code so what do I want to do now now that I've select the date now that I know that the sheets been created and I want to make sure just in case it doesn't now what I want to load any data that may have been saved for example what I want to do is I want to put January first in column a I want to put January 2nd and column B so based on the column number January 3rd in column C that's what we're gonna save the data so it's gonna be based on the day number that way we always get and the rows are gonna be the same in other words something that gets saved in row 6 is gonna automatically get saved in row 6 here it's just easier to keep it that way just simple you know it's just for data so we can always you never need to worry about the roads always gonna be the same row January 1st January 2nd we don't even need to name these we don't need to do anything with this just stores the data it's automated so how do we do that well let's continue the first thing what I want to do is I need to make sure that we get the day row I need to know the raid a row is gonna equal the target row okay so we have the row that would be the selected that would be the row that's important and then the day column so how do I get that good the day bro may not be so important on the target but will be when we select it when we actually make a change so the day column is actually more important day column its equal to what is that how do we get the day column let's see how do we get that column let's go over this if they select January 2nd right we know that we wanted and column 2 we want to put it in call them combi here or call them two so how do we get that well the best way to get that is to determine what the first day of the year is let's say it's January first what's the difference between January 2nd and January first of all it's one day so it's one but I really want to put it in column two so how do I do that I just add one so it's a simple subtraction elected date - the first day of the year plus one and that's gonna determine our column so how do we get that inside VBA we can do it right here the date column is equal to the selected date - what the first of the year how do we get that we can use date serial date serial and what is the date serial of course the first thing is a year we know the year number year name cuz it's used in both sheet name theater what is the first day the first month of the year in the first day of the year great so that's all we get it and then plus one that determines the column this becomes our column determines column for data sheet now we have our column so now that we have our column we can easily bring it back easily simple so all we need to do now is say let's say it's called gender all we need to do is say let's say it's January 2nd 6 take 6 through I don't know 29 I guess 6 to 29 and bring it over to 6 through 29 here that's all we need to do because we know the column and we can do that inside VBA it's quite simple so range aah 6 through aah twenty nine dot value equals what equals data sheet we've already determined the data sheet we know what it is now dot range what is the range in this case it's going to be the data sheet we need two cells because the columns dynamic cells six right six is the first royal through what is the called day column comma that's the first part of the range what's the last part of the range well it's just the row change is 29 so again data sheet dot cells this time it's all the way through row 29 same column day call that's it dot values so that's what we're gonna do so basically we're going to say gonna take the information from our data sheet from row six to row 29 on the column and bring it over into aah 6 through a 29 of course there's nothing there now but if we were to select let's let's do that let's put something in column 7 test and then schedule on click on sheet 2 and let's fix this okay obviously - not equals okay let's run that again and should work fine now and - okay and our test came up right as we'd have okay excellent so it's working now it's loading properly the only thing we need to do is we need to add automatically add a change on to when we make a change I want to save it so how do we do that good this time so let me put one more I want to set the datasheet this has to be after we create it we also need to set it again because if there was an error so now that we've said it because I want to make sure it's created automatically that means if it's not created here we do need to careen you need to set the datasheet after we created something that's important okay next up now what we're going to be doing is we're gonna be making a change so when a user makes a change here if I make a change here test - I want that change to show up right here right here in the bead so we just need to do that so if the user makes a change from a h6 through age 29 we want something to happen that's unchanged event so how do we do that that is going to be worksheet change event so we can select here and go to change and it's going to be focused on worksheet change event and if not we're also gonna be based on the selection of row basically the same row ah-6 through eh 29 if we make a change then do something what do we want to do well then I want to save it to the datasheet so how do we do that again we can use we need to define the same thing we defined here again the same thing we're gonna define the day column the day we need to copy basically all this and define all of that up here copy that and also I want to know the selected day in the year name we need to basically define all the same things that we did there just as we did below so copying and pasting the same thing that's going to help us so we have the selected name we have the year name and now we also again we need to determine just in case we need to determine if the worksheet exists so again we need to copy all this I also need to make sure the worksheet exists just to make sure so we can copy that same thing it's not gonna change okay so great now that we've determined the day row now that we determine the target column we've got the day calm we just copy that all we need to do it's so simple all we need to do is change make what change on the data sheet so data sheet we've already defined that either here or here now we say data sheet dot cells and what is the row we know it's the row because it's the selected row I copied and pasted it just as we did the row is gonna be here based on the target row day row comma day calm we know the column right because the selected date - not equals - dot value equals what target dot value that's it super simple all right let's save it and let's go back to our code so now what I'm going to do is I'm going to call this test three and a fix this air this should actually be a obviously the selected day is going to be an age range aah five that's our selected date dot value that's gonna be the selected date right because the date is not in calendar selected date selected dates here now aah five that's what I want to focus on so good we'll reset that code and then now when we make a test let's just update that now we tab over look into 2020 perfect it's came up so all we need to do is select a different day clears out I want to make sure it clears out and go back into the second and our point returned very good all right so now automatically let's try this again with a different month let's on today here Randi's 23rd birthday nearly oh I'm almost 23 just kidding alright so we got on that select another day and go back to the 11th perfect it's working out our daily activity schedule is now complete make a few more for Matt just miss this format in here make sure everything's nice and perfect make sure you can download this using the links in the description of course if you like this I've got over actually a 20 workbook bonus so 124 just 37 dollars so it makes you pick that up or check out our mentorship program I always appreciate your likes you follows you shares you can check us out on Facebook or join our Excel for freelancers Facebook group over 25,000 numbers thanks so much I appreciate all your comments likes have a great week and we'll see you soon [Music]

Channel: Excel For Freelancers

Views: 94,572

Keywords: Yearly Excel Schedule, Yearly Calendar, Yearly Excel Calendar, Annual Calendar Excel, Excel Annual Calendar, Excel Yearly Activity Scheduler, How To Make A Calendar In Excel, How To Make An Excel Calendar, Excel Calendar Formulas, Create A Yearly Calendar, Excel Annual Excel Calendar, Create Your Own Excel Calendar, Excel Yearly Calendar, Excel Full year Calendar, Calendar Excel Yearly

Id: _4Ew2LdAXMs

Length: 52min 49sec (3169 seconds)

Published: Tue Mar 24 2020