Calendar Design in Excel VBA | Date Picker

namaste everyone welcome to this channel er.pidia my name is sagar banzadi in this video you will learn to develop your own custom calendar using excel vba you know up to office version 2010 we are in build calendar control feature named date picker but later office version do not have date picker by default you will have to check each pc and look in reference and see if it is marked as missing if it is not you can download and install a date picker and it works properly but unfortunately from office 2016 that figure is no longer supported even after installing it does not work so for later version we have to build our own custom calendar even if you have office 2010 or 2016 i would advise you to create your own simple calendar control and include it in your project doing so your project will work on every pc whether it is office 2010 2016 or 2020 this calendar control is important to place the date let me show you how it works if i have to place a date in this cell then keep it stay in this active cell then click on this show calendar and pick the date here you can see now if you want to place a date on your user form then click on this calendar click on this date you can easily add the date on the text box and even you can easily place a date on the command box like this this is the beauty of calendar control now without any delay let's get started first i will close this excel file and open the new excel sheet this is my new excel sheet we'll first design the user form for designing the user form click on this developer visual basic and here in insert click the user form now extend this user pump a little bit we will place month and the air at the top so click the label and draw the label layer and with combo box under the combo box this is done copy this label in the combo box and paste here increase this size now change the captions of this label here month even change the font i'll take times new roman gold and the font size double okay and this one is here then the caption font times new roman bold and tubal okay and this combo box is combo box 1 and combo box 2 this is done below the month in the air we put the previous and the next button and at the middle we display the month india so select this frame and draw the frame increase this size now it seems perfect now i like to change this caption i'll put the caption empty and we'll put the previous in the next button later so before i like to keep the label sunday monday tuesday at this bottom so i will take this label and draw the label here then the caption of the label sunday and then this font times new roman font size 2 will okay now change the height and width of this level where is height here is the height i'll fight 18 and the width 36. and also i'll change the text alignment center now it seems good now copy this and paste here same copy the both and paste it now copy these three and paste done now change this caption monday here tuesday weinstein this one is friday and last one is saturday done below the name of the days we'll place the date for that click this command button and draw the command button here now change this captions of this command button here i'll keep empty and here change this font one will be two bill times new roman okay even change the height and width of this command button with 36 okay and the height i like it to 24 okay this seems okay now it seems good now copy and paste here copy and paste copy and paste and here copy and paste command button one command button two three this is six and this one is seven now copy this all and paste here it seems good now check the name of the command button 11 12 13 and 14 it's okay now copy this all and paste here we need to increase this size of our user form again copy this all and paste here we need total of 40 tools command button now if you have any doubt then check this name of the command button the name of the command button is perfect now we'll make the pbs and the next button here for that click the command button and draw the button here change the caption of this button and take the caption previous here send this font times new roman to bell bold since okay now copy this button i'll like to increase this height this is the right height i'll take 26 now it seems good now copy this and paste here then the caption add caption next and here we'll display the month in the air for that we'll take the label select this label and draw the label here and change the caption of the label month here will display there now change this font size on size and 0 okay it will be at the center line where is the text alignment takes a line when it will be text alignment center since this height i like to take the height 18 now play set this middle not now here background style transparent done now we place some color for this previous and even for this level i'll place the color for this all this label i'll put same color here i'll put the color no it's okay now for this frame i like to keep the color frame color i think i'll look now it shoots good now for this button we'll put the it does not seems good it seems okay now it looks perfect yes now this i will take is a transparent background style transparent okay now slightly more air that sims this is the complete front-end design of our calendar now we'll put some code first we'll initialize our user form it means we'll initialize this to combo box for that double click on this user form it will take you to this coding section now here select this activate now delete this first we'll initialize our combo box of month that is combobox1 for that we'll take the dim is integer here i indicates our month that is january february all this and with me dot me dot combo box combo box one yes we'll take the for loop for i i is month how many months are there one two two bill yes then we'll add item here that add item yes we have a bba format bba dot format yes vba dot format then we have a date serial number bba sorry bba dot date serial yes bba.cereal here we'll first put the year and we'll take any year you can take 2015 2016 2018 i'll take 2020 and month as integer which is our month month is our i so plus i here now day you can take any day one two three remember any integer form i'll take one and close this yes then type month then again close this section done yes then we'll next i yes then we'll end this end with done now run this code here you can see january february march uplands we initialize our month section now close this now we'll initialize the air so double click on this here i'll quickly copy all these things and paste here now delete this section with me dot combo box 2 yes then for here we take i equals bba dot air in here we'll take date yes that means the present date from present day i'll take the up to 20 year back two copy this and paste here i'll take 20 here later now dot 8 item we have to add i here item i then next i then end with this is the now run this now here you can see this how you initialize this month in the air combo box now we like to display the current month in the year for that quickly go to the coding section here here we type we want to display the present month so dot value equals vba dot format yes vba dot date in order to display the present month we'll type 4am then close this section even for to display the present air quickly copy this and here paste here then here we'll type y then done now run this code here you can see it is the february and this is 2021 this how we initialize our user phone now we will add code to display the date here to do that we have to create a soft procedure here so type solve so date we have to take two variables first date and the last date type name first date is date last date is date here this first date means the first date of the selected month and the last date means the last date of the our selected month now we'll put code for our first date our first date equals vba dot c date this seeded converts the text into date and give one and ampersand name of our month v dot combo box one dot value then give the name of the air me dot combo box to that value close this is the code file for state now before we put the code for last it will first take the last date of the next month but last date equals vba dot serial sorry date serial vba dot date yes first date and month first it and plus one or plus one we post it of the next one quarter and one close this now we'll give a message message box first date we will check whether our date has been assigned or not message box last date now call this up procedure here in the user from activate we'll call this soda now run this code and you can see this is the february 1st 2021 february 1st then click on this is the first date of the next month now we need the last date of this the current month for that will subtract one year then run this here you can see this is the last state of the current one now we'll assign our first name in the last state now we put the code to remove if any caption available in our command button to remove the caption to remove any caption available in command bus yes for that we will take i as integer and dim vtn is ms form form dot command button yes for i equals we have 42 command buttons so 1 242 we'll run a loop here and we'll set btn as me dot controls yes media controls what is the name of our command button command button is command button here am percent and indi indicates the number of our command button and button caption we have to remove the text so btn.caption equals md then next i it will remove if any text if available in our command button now we'll set the first date of our month set first date date of month for that we have to take a look for i equals one to seven here y will take one to seven uh our first date always lies in between the sunday to saturday so it will falls under one to seven within one to seven our first date will false so we'll set [Music] we'll take this copy this and paste here set btn8 media control command button i and if vba dot weekday yes first date and we have to take d capital first date equals i then button caption what is our button caption bt under caption equals 1 and we have to close the if statement in if yes now i'll run this code now run this code sorry here i forgot to put the next i next i now run this code this is our first date of the month now we don't need the message here we'll delete this message done now run this code this is the first date of the current month now we'll set all the dates yes set all the copy this and paste here vtn one will take two barriers we have 42 buttons we'll take here one two forty one set btn one equals middle controls command button now if our btn1 dot caption we know that our bt and one have number of it will show our date one so that if it is not false caption it must be less than our last date last date then then only our btn2 dot caption equals btn1 dot caption plus one now indeed also end if then next time next time is the code for hour to set all the dates now run this code and we can see this is our code here we can see all the depths here it is showing the date of 1 to 41 for that to swing till the 30 hour we have the date of 29 to 30 earth atlas 31 also for that here we have to put here we have to put pba dot day then close this bracket now run this code and you can see date of this february month is one to twenty this is record to say the date now we'll write code to change the event that's here this is the date of only february now we have to write the date of march also is not changing the date it is only showing the date of february to change the date first we'll send the event of the month for that double click on this combo box i'll cut this and take this section here now here type if me dot combobox1 that value does not equals empty and me.combobox1 combobox2 that value does not equals to empty then we'll call should it call so date yes now in this if statement hint if now run this code it is showing some error i don't know why it is now we will cut this and give a space here cut this and give a space here now run this code now you're saying february if you take this is the mars now it is showing our date in some computer we can see if we give a space it will work and if we give a dash also it will work i don't know what happens here but change that dash into the space it will work now we'll write code to change this air so curve this i'll take here ctrl v now cut this also control here sorry copy this copy this and paste here now this is the combo now this code now if you change this air you can see our date and air event has been changed now we will put code to show the current month in the year in this label and this is our level 10. uh we'll put code in our combobox one sentence and the combobox one to change here type me dot label 10 dot caption we have to change this caption caption equals me dot combo box one yes and we'll give space the sign between the air and the month and me dot combobox2 yes now we have to put the same code here also so paste it now run now you can see this is the february 2020 yes we have completed the coding for uh to change this event of month in the year and we also put code uh to show the month in air in this label now we put code uh for our next and the previous button for that click on this next button now here type if me dot combobox1 this indicates the month if our combobox1 dot list index yes combust list index equals to 11 it means if my month will be december here 11 means the list index start from zero so zero indicates january to indic one indicates february so my december will indicate by 11 if if my month is december then then when i click on this next button then i'll it will have to show me dot combo box one yes combo box one dot list index equals yes list index equals zero it will have to show the zen over yes of next year then me dot my air has been changed me dot combo box 2 s dot value equals me dot combo box 2 plus 1 else me dot combo box one the list index equals me dot combo box one dot list index plus one yes now we have to end our if statement so in if yes this is the code for our next button now run this code now here you can see next when you write next it is saying update august september october november december when you when you press the next button here you can see the air has been changed now we'll put code for our previous button for that double click on this previous now copy this code and paste here here we have to give the leaks index zero january when it comes to january yes then our list index must be of december here you have to subtract the air minus one yes yes here also you have to subtract minus one now run this code and you can see january december our code has been working properly now we'll put code to give this background color in this command button for that double click here we'll have to type soft reset color now here we will reset the color and copy this section and paste here here you have to type if button caption equals to empty if how if there's nothing in our button then we'll give button enable false yes button enables equals false and will gives the bottom color back color yes we can choose color from bba dot rgb we can choose color from color x this is uh we can choose from the color x.com i'll select this color now copy this and paste here else if there's a caption in our button then we'll take here this i'll copy this and paste here with this here we have to write true yes and i'll give here 254 254 198 198 15 now we have to end our if statement in here and next i call this reset color now we have to call this sub procedure here you have to call it caption available as it always we have to call here call reset color now run this and you can see we set the background color for our command button now when you click here we can we haven't put uh decode to click the button so for that we need a text box so increase the size now take a text box from here now remember this name of the text this is our text box one now we'll put code we'll put code here yes now type stop button click you have to take btn is ms form dot command button yes now if button caption if dot caption does not equals empty then me dot text box one yes that value equals button that caption and now foreign me dot combo box one dot value it will take three and me dot combobox2 now in this if statement and done yes privates of we'll put code for this uh command button one for that command button one command button button one yes command button one click we'll have to call the above call button click call button click me dot command button one yes need that command button one now do the same things for all the command button here i forgot to put the value yes now do the same things i'll copy from my previous file to the same thing say hey yes i'll put the code for command button two you have to change there two three three four four like this now when i run this code when i click on this here you can see one february 2021 now check whether all the button is clickable or not here this one is not clickable yes here you can see it's cool now we will put code for our targeted place where we want to put our date yes for that we'll insert a command button here this is our command button one and when if we are in this active cell and when we click on this command button and it must show our calendar and when we will select the date it must so the test in the cell for that we have to put some code type here function input is object close this yes this is our string edge string yes now we'll take dim str is string and here if bba dot type name yes my date input if it is of text box or bba dot type name date input equals range then then the string will take the value ester equals and date input dot value yes else if else if i'll copy this code and paste here else if bb dot type name date input if it is of my command button or the date input is of label yes then this thing will take the value date input dot caption now end if yes if vba dot is dead yes is a string equals true yes then me dot text box one yes one dot value equals vba dot format c date string and i have to give the format here my format will be dd i'll take 3m and over here then close this ends me dot textbox one dot value equals equals empty and end if now i will show you how it works first i'll put the command for this command button one double click on this here i have to call my calendar call first i need to change the name of my calendar here i think the user form i'll change this name as calendar yes dot sorry but i haven't changed this level i like to change this label also here calendar now here double click here call calendar dot date picker and and we have to active cell done to show the calendar here you have to call calendar. so now design inactive this design mode now type any date and the cell this is my date when i click on this command button it will say the date on this text box now we want to show the date in the active cell if you click on this command button any command button it must show the date on this active cell for that we'll put some code we'll add some code for that now copy this yes now and paste here we have to date input.value equals me dot text box one dot value text box one dot value yes and copy this and paste here text box one dot value and is as date picker equals me dot text box one dot value now in this if statement in if now i'll show you how it works now stay in this active cell click on this command button click on the stand now close this and you can see that has been added in your cell same process do the date has been added and when you click on this button the calendar must be closed for that we have to unload the calendar yes we have to unload our calendar now way to unload our calendar we have to unload on the button click the result button click this is our button click and when indeed unload me yes now test it go to command click yes you can see our calendar work properly now we are in the end of our session now we want to put when you click on this this 10th february we click on this 10 february and i don't know which is this clickable which i which is this date in the active cell date so for that i need to give a color on the color for the eclipse and date yes for that we'll highlight our date to highlight our date we need to add some quota this is the work of coding yes for that highlighted yes highlight date yes so if i like this dth date yes we'll copy some code from above from here i'll copy this code and paste here i'll delete this yes and type here if bba dot c is string yes bba dot day dt equals btn.caption yes then delete this okay then i'll delete this and button background color i'll take bb white this is my code now check this code whether it is working out properly or not i'll stay in the cell click on this command button i have one call the highlighted date i need to call my highlighted date i'll call my highlighted date in this user form activation where is my user from activation this is my user from activation i need to call here so i'll call for that i'll type if me dot text box one s dot value does not equals empty then call highlight date yes call highlight date see date and here i have to type mid.textbox1.value me dot text box one dot value i have to close this and in this if statement end if i'll check whether it is working or not i'll stay in this cell click on this so bal here is missing yes now wait isn't it sub function is not defined i liked it where is my highlighted i think yes here i is missing yes i see us li gst now now stay in this cell here you can see the data has been highlighted now you can see the date has been highlighted 10 february the date has been highlighted this is the complete design of our calendar we can also put the date on our user form also for that i'll create a new user form uh i'll insert a new user from here i'll take a text box in the command button here i'll draw simple text box and a command button i'll take another command button and i'll change this caption is command button this date and even copy this here and then i'll empty this caption i can call the calendar to show the date in two ways also the both ways now double click on this state i'll call here the text box uh meter text box yes this is my textbox one that value yes equals calendar third date picker this is the one way of calling the functions yes now when you click on this date you can see it is showing the date yes now close this now uh double click on this yes now i'll call in another way here i have to type call calendar yes call calendar dot date picker and this is the command box me dot command button one is command button one and close this now run this code on youtube click this this here and this if you call the second way you can even highlight your date this is the complete tutorial uh of designing of your custom calendar i hope you like this video enjoy this video if you have any doubt then you can drop a comment on the comment box if you like my video then subscribe my channel for such more video i hope you like this video now see you on the next video till then take care bye

Channel: Er. Pedia

Views: 11,708

Keywords: excel calendar vba, Dynamic calendar in vba, date picker in vba, fully functional dynamic calendar control in vba, custom calendar for excel automation, how to develop calendar in vba, advance excel, Excel automation, Date and time picker in excel vba, vba macro, how to make user defined calendar in excel vba, vba calendar userform, Dynamic calendar using excel vba, er.pedi, how to make calendar in excel

Id: OG0UavM3-74

Length: 60min 4sec (3604 seconds)

Published: Tue Feb 09 2021