Fully Functional Dynamic Calendar Control in VBA (Part-1)

[Music] hello friends welcome back my name is Pietro Kumar in this video you will learn how to develop a calendar control in Excel VBA so friends calendar control is very useful for any VBA project for example if you want to get started and ended then you can use this calendar control I'll show you here I want to enter these targets I'll just click here and I can click on calendar so this is the calendar which I have developed in Excel VBA so you can select any month directly from here it will change automatically or you can change the year also and you can click on previous month so it will display the previous months from here or you can just click on next month whatever month you want to select you can select from using these buttons and now you can select any of the date let's say I want to select a plan just click here you can see the fifth of trial is available here if I will click again then you can see that particular date is being highlighted that means the current value of that cell is fifth applied all right if I will change it as tenth and I will click again then it will show me tenth highlighted similarly I can change the end date so 30th April is the current value I want to change its 31st me you can use this calendar control on VBA user phone so also let me show you the example here I have created a small VBA user form so here let's say if you want to enter the start date I have given a small calendar button here you can just click on this calendar and let's say I want to input 10th April so you can see the tenth of April is here end it as 30th April if you will click again then whatever date is already available that will be high similarly here for ended also that will be highlighted you can change the date of any label also these are the tax boxes if you want to change the tale of label or you want to assign any date to one label so this is a label I want to change the date of this label okay so this is the first gen 2018 this is the current date has been highlighted I want to change it let's say 2019 alright April 11 them so you can see 11 I will click again then that will be highlighted you can put the date on a command button also so this is the command button right so I want to put here the date whatever date I will select I have put that code on this command button so just click on this and just click here that date will be changed now the thing is how can you move this calendar in your before exam this is my bb-8 projector and I have this form and I want to use in this form because this is available in different workbook so to move this calendar you can simply pick this calendar and you can using the mouse just move this here and when you will come to this project just leave your mouse you can see your calendar has been moved to this project now I want to use the connector here to put the date of joining can you do you can put knees here let's say I'm just putting this image you can resize this image this make it a little bit of a smaller like this and you can put your calendar icon here on this image and now if you will click on this image then the calendar will be opened and you will select the date in the calendar and that date will appear in this text box so let me see the text box name this is text box for if I will click here and then I will call this calendar I can use simply me door text box for dot value equal to calendar dot selected date alright if I will run this and just click on this image then that calendar will be opened you can select the date whatever you want to select the date will appear in this box but if you will click again that date will not be highlighted because we have used the method to I'll show you here the method to is you are directly assigning the value of text box for this calendar the selected date if you will use the call method then that will be highlighted now I'll show you you can use call and then calendar Lord selected date and here you can put your control name mean dot text box for simple if I will click here then click and select a negate if I will click back again then that 11 will we highlighted which means the 11th is already available here so that is how you can move this calendar in your project I will add a new workbook here this is the book one I'll go to the Developer tab and visual basic of book one and here this is the book one okay you can see now I will insert user form and change the height and width of the user formalities around this item with now I will forbade this user phone course right click go to the properties and change the name as let's say calendar and the caption also I want to change it as calendar now I will change the background color also so I want to take the white background color and the special effect I'll take here this one now I will insert to combo boxes for month and here so I go to the tool box and now first of all I will insert a label so just put the label here and rename it as month right-click go to the properties and here I will change the back style as transparent and I will make a copy of this and just paste it here rename this as here now I will insert a combo box this is the combo box this click on this and just click here so this combo box is available now I will format this combo box right-click go to the properties first of all I will like to change the name of this combo box and I will take it as let's say CMB month then I will change these special effects I take this effect and this style also I will change in place of combo I'll take here list because I don't want to allow to enter any text text should be selected always from the drop-down list now make a copy of this and just paste this here and change the size of this right-click go to the properties and we will rename this as CMB and E this is for year all right so we have created our two combo boxes just put these here align these as middle now we will add the value of these quando boxes if I will run this user form then you can see no drop-down is available here so we will add the drop-down here first to add the drop-down just double click on this form you can see user form click event has been generated but we will put our code on user form in e slides so just select here in his life we will put our code here remove this one now here I'll take a variable name i as integer and first of all I'll add the months name so you can add the month name one by one or you can use a loop I will show you how can you do that so first of all what I will do and use baby mean dot CMB month and here I'll use and with I can simply use add item then I can put here January then Feb March and one by one you can add it or you can use a loop I'm going to use a loop I will use a for loop for I equal to 1 to 12 and just press ENTER now here I'll use dot add items and first of all I'll create a date here so I'll use VV dot date serial and in the date serial I'll take year so I can put any year let's say 2019 and month I will put here I and then I can put any day one okay so this state will be created as first of all it will create first 10 2019 then first 5 2019 now from this date we will just take the name of month so what I will do I'll use here will be a dot for made this we will pass this date and from here we will pick up the complete month name and just close the bracket all right now if I will run this you can see the month name is available here but we have to use the scroll to see the all the Munsell I don't want to see these crawl here just for 12 months all right so what I will do right till L select both the control right click and go to the properties scroll it down and here you can see the list rose it is 8 by default I'll put here around 20 now if I will run you can see the all the months are visible now we can add the default month here what month should display by default so whatever the current month that should display by default so I'll use dot value equal to and here we can take B be a dot for me then for today we can use VBA door date comma format we can take mmm for time m just close the vacate if I will run this you can see the April is here which is the current month now similarly we can add years also just copy this and paste this here and this time we will use with me towards CMB year and for the year you can make this hard code or dynamic it's up to you let's say here I am just putting VBA dot year from this date today's date whatever is the current here it will give you the current year I take here let's say - 20 and similarly and put here from - plus 20 all right and it will add the item simple I because now I will be the year and here default we will display the whole time by means the current here if I will learn this you can say it is displaying the current year and the list will be currently it is 2019 so - 20 years and plus 20 years okay from current date you can change it as per your requirement how many years you want to display in the calendar so we have added month and year right now I will insert a picture here so I'll select this amis and just click here and format this image and keep this here right click go to the properties here first of all I would like to remove border styles this is single I will take care none and then I will add a picture here I'll show you the few picture which I have already created so these are the pictures which I have created the background color for this image left and right button you can create this using PPT or you can download it from Google and this small calendar icons also and this is a small star icon I will copy this path and then I will go to the this and here I'll go to the in the properties of this picture and go to the picture and just click on these ellipses and it will ask you to select the picture so I will put the path here and I will select this background image so this is the black image you can give the simple color also if you want to background color alright now I will make a copy of this and I will pull this here change the size little bit smaller and right-click go to the properties we will change the picture this time we will take this left one or previous and just enable the auto size this is the auto size and we will rename this image as I mg and this is 4 tbs month and here the control tip tax we can put previous month and we can put it here change the width of this alright so just make a copy of this and paste this here and just rename this as IMG next and here even put next month we will also change the image of this I'll change the picture and just right click on open so just put this here so we have created two buttons also I'll show you run this if I will hover this is for previous one this is for next month alright now we will put their label which will be the month name whatever month has been selected so just copy this label and just paste here right-click go to the properties just rename this as tl here we can take as month name and we can change the font also so font we will take here this one which is monotype persica and make this italic and bold size i will take around 20 texts claimant we will take center and the 4 color which is the text color and change white now we will move this here so basically it will display whatever month has been select now we will design buttons or you can say the dates so we will use the command button for that but before that I want to insert another picture so I'll select this picture just copy and paste this here and just put this picture here change the size take around this size right click go to the properties and here first of all I would like to remove this picture so just select and press Delete that will be removed and background color I will change the background color I'll take this color all right now here I will copy this label copy and just paste this here I will put this label here right click go to the properties and then I'd go to the font and I will change the font sizes 9 make this bold I will change the text alignment also and take Center align now I will be in the text of this level so I will take the text as Sunday or su I simply copy this and paste this here and just put this level here and change the text as Monday mo and quickly I will create the other levels also and will change the name alright so we have created these labels now quickly we will create the buttons then our design phase will be completed all right then we will start the coding phase so I quickly go to the tool box and I will click on the command button and just select and click here so this command button has been created first of all I will remove the tag top this command button I will put caps on as blanks and resize this take around this size and put this here alright so I will make a copy of this and paste this year and I will keep this here page 10 another copy just keep this here alright so be have created the buttons here these are the Saban button right click and go to the properties you can see this is the command button one two three and saving so I don't want to change the name of this command button because we will use this in the loop so now what I will do I will select these buttons like this and make a copy of this and just paste this here and pull this here now right click on this and go to the properties you can see this is the command button 9 and this was saving so it should be 8 here but the 8 is coming here so what I will do I will move this here and and move these buttons here and I will keep this button here alright so this is common button 8 9 10 11 12 13 14 so quickly I will copy this and paste again I will make 6 lines of buttons all right do the same activity so we have created 42 buttons here now I will see sighs the other things like I will move this slightly here and these labels also like this and change the size and this ear box also now we will align this stats with this button right click and align this as Center similarly for this all right so our design is completed if I will run this our calendar will look like this now I will change the colors or I'll do the other coding also and we will complete that coding part in the next part of this video please watch the next part of this video thank you so much for [Music]

Channel: PK: An Excel Expert

Views: 52,331

Rating: 4.8805971 out of 5

Keywords: ADVANCE EXCEL, VBA MACRO, BI Dashboard, EXCEL dashboard, User form in VBA, Dynamic Calendar in VBA, Date Picker in VBA, Data Entry from in Excel VBA, Fully Functional Dynamic Calendar Control in VBA, Image in VBA user form, Function in VBA, Loop in User form controls, Calendar icon in VBA user form

Id: e89PFCrMUfI

Length: 18min 33sec (1113 seconds)

Published: Sun Apr 28 2019