Hi Kyle, I purchased your excel beginner to advanced course on Udemy and am on the last portion of it - VBA. I’ve already been able to put what I’ve learned into practice at my work. I absolutely love your teaching style and am thoroughly enjoying your course. So much so, that I went on to purchase your introduction to HTML and Python courses. You’re an awesome teacher. Thank you so much!
Neat tool! If I am working to create a form with multiple sections that I wish to enable a date selection, how do I need to edit the VBA to add those sections? For example, I want the calendar to appear when cells F2 & G2 are selected, but also cells A10:A29 and cells A32:A41.
I am having troubles with the If Not Intersect(Target, Me.ListObjects("ProjectTable").ListColumns(3).DataBodyRange.Resize(, 2)) Is Nothing Then line. I am wondering if you could explain the components of this line? The sheet I am trying to use this in has two date columns in column 6 and 9 and the amount of rows fluctuates. Does my sheet need to be named "Project" or should I be typing the name of my sheet into this part of the code?
There are several steps that need to be defined, so applying this process with that code and table as it is will not work on your table unless your table is exactly like the one in his example called ProjectTable. You need to name your table and define the cells spanned (go to the Fromulas tab and under Defined Names section, Define your table). If you do this, name your table ProjectTable and you won't have to change the code. That is as far as I got and I am sure you got no answer because Kyle sells his time.
change List.Columns(3) to whatever your first date column is. Mine was the 4th column in the table, therefore, i changed it to List.Columns(4) and that seems to have worked for me.
Another option to insert date is using date validation. Once you double click on the cell, the calendar will pop up, and you just select it. Just two clicks! No code required.
Very useful tool, thanks for the video. I adapted the VBA code you shared for the mini-calendar, all worked ok, except that when I clicked out of the column I selected, the mini-Calendar don't go away. Any suggestions to fix the inconvenient? here is the modified code: ' Paste this procedure in the Worksheet object in the VBA Projects panel Private Sub Worksheet_SelectionChange(ByVal Target As Range) ' check if the activecell is found within the "J" column (J8>>J42)of the Active Sheet If Not Intersect(ActiveCell, Range("J8:J42")) Is Nothing Then ' display the calendar ActiveSheet.Shapes("Calendario").Visible = True ' position the calendar next to the activecell ActiveSheet.Shapes("Calendario").Left = ActiveCell.Left + ActiveCell.Width ActiveSheet.Shapes("Calendario").Top = ActiveCell.Top + ActiveCell.Height Else ' hide the calendar if a cell is selected outside "J8:J42" column (it does't work, the calendar does not disappear) ActiveSheet.Shapes("Calendario").Visible = False End If End Sub -----------------------------Thanks so much
To make the mini calendar close you need to click on the upper right corner of the calendar and you will see a little arrow click on that arrow and click delete.
Hi Kyle, I finished your Beginner to Advanced course and enjoyed it very much and thank you for that. I have a question. Let's say i have a weekly work roster with workers named in column A, and the top row from column B on has Sun to Sat. The next row has the dates underneath the days. If i have 15 workers and across from each of their names and under the dates are the specific duties each of them are on for the week. How do i automatically update the dates each week as well as shift down each worker one row each week. For example, worker 1 this week is on row 1, next week he is on row 2 whereas worker 15 this week will, next week, move up the roster to row 1 position. I fully understand if you do not have time to help and it's ok if you cannot. It's just something i am designing for my workplace. Kindest regards, James.
Kyle, This vid has been a great help to me. I am really interested in the formula that calculated the number of days from the dates in the two columns. I see the cells have been named - I tried with cell ref but didn't work. I there a video that can help me do this. Thanks Martin
Hello sir, thanks for the informative video, i wanted to ask if is it possible to add a data validation on that calender object through vba, wherein the user won't be able to select a date earlier than today's date?
Yes, it's possible to add data validation to a calendar object in VBA to prevent users from selecting a date earlier than today's date. You can achieve this by using a combination of the `BeforeUpdate` or `AfterUpdate` events of the calendar control and a condition to check the selected date. Here is an example of how you can implement this in VBA: Example with `BeforeUpdate` Event 1. Open your VBA editor (Alt + F11 in Excel). 2. Insert a new UserForm if you don't have one already. 3. Add a Calendar Control to your UserForm. If you don't see the Calendar Control, you may need to enable it from additional controls. 4. **Double-click the Calendar Control** to open its code window. vba Private Sub Calendar1_BeforeUpdate(Cancel As Integer) Dim selectedDate As Date selectedDate = Calendar1.Value If selectedDate < Date Then MsgBox "You cannot select a date earlier than today.", vbExclamation Cancel = True End If End Sub Example with `AfterUpdate` Event 1. Open your VBA editor (Alt + F11 in Excel). 2. Insert a new UserForm if you don't have one already. 3. Add a Calendar Control to your UserForm. If you don't see the Calendar Control, you may need to enable it from additional controls. 4. Double-click the Calendar Control to open its code window. vba Private Sub Calendar1_AfterUpdate() Dim selectedDate As Date selectedDate = Calendar1.Value If selectedDate < Date Then MsgBox "You cannot select a date earlier than today.", vbExclamation ' Optional: Reset to today's date Calendar1.Value = Date End If End Sub Explanation - Calendar1_BeforeUpdate: This event is triggered before the calendar value is updated. The `Cancel` parameter allows you to cancel the update if the condition is met. - Calendar1_AfterUpdate: This event is triggered after the calendar value is updated. Here, you can check the selected date and, if it's earlier than today, you can show a message and reset the calendar value to today's date. Both methods effectively prevent the user from selecting a date earlier than today's date. You can choose the one that best fits your application's flow.
Great idea - thanks for sharing - I have slight problem in that when I select a cell that already has a date in it, I get a message that pops up in the calendar - "Overwrite Contents" - OK or Cancel" . I there a way to suppress this message?
You can try using a little bit of VBA 1. Open the VBA Editor: - Press `Alt + F11` to open the VBA editor. 2. Insert a New Module: - In the VBA editor, go to `Insert` > `Module` to create a new module. 3. Add VBA Code: - In the new module, add the following code: Sub CalendarClick() Application.DisplayAlerts = False ' Your code to handle the calendar click and update the cell goes here ' For example, you might be setting a date in the selected cell: ActiveCell.Value = Date Application.DisplayAlerts = True End Sub This VBA code turns off alerts before setting the cell value and turns them back on afterward. You will need to adapt the middle part of the code to match what your calendar add-in does when a date is selected. 4. Assign the Macro to the Calendar Add-in: - If you are using a custom calendar add-in, you will need to find the event or button click that triggers the date selection and call this `CalendarClick` macro instead of directly setting the cell value. 5. Save and Close the VBA Editor: - Save your VBA project and close the VBA editor. 6. Test the Macro: - Go back to your Excel worksheet, trigger the calendar add-in, and select a date. The "Overwrite Contents" message should no longer appear. If the calendar add-in is a third-party tool, you might need to check its documentation or settings to see if there’s an option to integrate or call custom macros upon date selection. If this option is not available, you might need to handle the date selection process entirely through VBA as demonstrated. Remember to save your Excel file as a macro-enabled workbook (`.xlsm`) to ensure the VBA code is retained.
Nice video, thanks. I want to use it in my file, but it gives me error message, my table has 6 columns and 79 rows, May I know if I should change this line to "If Not Intersect(Target, Me.ListObjects("ProjectTable").ListColumns(6).DataBodyRange.Resize(, 79)) Is Nothing Then..." Thanks.
Hello Katie - The #6 in the ListColumns represent the column the date value is found. If the date is in another column you will want to change that number to reflect the column #. If there is only the one column then you can remove the .Resize(,79) If Not Intersect(Target, Me.ListObjects("ProjectTable").ListColumns(6).DataBodyRange) Is Nothing Then
Great effort sir, but I am not able to see add ins in insert tab. I tried going to File - options - Add ins - in manage enable disable items. But still I couldn't find Add-Ins option anywhere. I am using Windows 7 Home Basic (64-bit).
If you down the practice file from the OfficeNewB Blog you can see the formula used to get the amount of days. We are just subtracting one date from the other.
can i know how to use data validation to prevent when we click calendar picker (not the way we fill the date in cell) and we only want only 1/1/2021 until 31/12/2024 only, if enter more than that it will be turn out to be clash. please help me
@@OfficeNewb its not that the date is clash.. now i need to make the form information for wedding event.. so i need to show to my lecturer when i click the date other than 1/1/2024-31/12/2024, it will show us the clash date to us🥺
***SOLVED*** Error 9 in line 4: the code gives an error in line 4: If Not Intersect(Target, Me.ListObjects("ProjectTable").ListColumns(3).DataBodyRange.Resize(, 2)) Is Nothing Then I have renamed my calendar object to "Calendar" as the video instructed, and pasted everything in as directed, but no luck. SOLVED IT: Ok answered my own question. This code has 2 references that each user will have to specify that aren't mentioned in the video. First, the OBJECT is a table that must be inserted ( click the "Insert" tab, then choose "Table") and then named ( rename your tables by clicking the Table Design tab, then on the left side set Table Name (ex: "Table15"). Second, the columns with dates need to be referenced. In this script, it is done by targeting the first column with a date and assuming the one immediately to the right of it is also a date (Resize(,2)). So, if I had named my table "Table15" and wanted the 2nd and 3rd columns of the table to have the date selector, then this line should read: If Not Intersect(Target, Me.ListObjects("Table15").ListColumns(2).DataBodyRange.Resize(, 2)) Is Nothing Then
Debug Error, Run-Time Error '9' Subscript out of range. Something wrong with "If Not Intersect(Target, Me.ListObjects("ProjectTable").ListColumns(3).DataBodyRange.Resize(, 2)) Is Nothing Then" It worked when I downloaded your file, but not on my own spreadsheet.
![How To Add 3 Different Date Picker Calendars in Microsoft Excel [Free Download]](http://i.ytimg.com/vi/PLAJ08LP7kE/mqdefault.jpg)
![[FINALE] มีคนคิดแค้นลักพาตัวเด็กไป🔪หญิงชราใช้ชีวิตตัวเองเข้าแลก👩🦳 #โลกการละคร](http://i.ytimg.com/vi/C_BPHPoZFOg/mqdefault.jpg)
Hi Kyle, I purchased your excel beginner to advanced course on Udemy and am on the last portion of it - VBA. I’ve already been able to put what I’ve learned into practice at my work.
I absolutely love your teaching style and am thoroughly enjoying your course. So much so, that I went on to purchase your introduction to HTML and Python courses. You’re an awesome teacher.
Thank you so much!
Awesome, thank you!
I want to get your Course also
This has been so helpful for a project tracker I created. Much appreciated Kyle.
Great! I'm so glad
Great little add-in! And great lesson as always. Thanks Kyle!
Thank you! Glad you enjoyed it!
This is what ive been looking for, vivid and valuable. Massive appreciate, Kyle . Yada yada yada, booom!
Thank you!
Neat tool!
If I am working to create a form with multiple sections that I wish to enable a date selection, how do I need to edit the VBA to add those sections? For example, I want the calendar to appear when cells F2 & G2 are selected, but also cells A10:A29 and cells A32:A41.
I am having troubles with the If Not Intersect(Target, Me.ListObjects("ProjectTable").ListColumns(3).DataBodyRange.Resize(, 2)) Is Nothing Then line. I am wondering if you could explain the components of this line?
The sheet I am trying to use this in has two date columns in column 6 and 9 and the amount of rows fluctuates. Does my sheet need to be named "Project" or should I be typing the name of my sheet into this part of the code?
There are several steps that need to be defined, so applying this process with that code and table as it is will not work on your table unless your table is exactly like the one in his example called ProjectTable. You need to name your table and define the cells spanned (go to the Fromulas tab and under Defined Names section, Define your table). If you do this, name your table ProjectTable and you won't have to change the code. That is as far as I got and I am sure you got no answer because Kyle sells his time.
change List.Columns(3) to whatever your first date column is. Mine was the 4th column in the table, therefore, i changed it to List.Columns(4) and that seems to have worked for me.
Great video.
Is it possible to use this calendar to find date on the sheet?
It's just a regular calendar
really useful video! 👍 I managed to adapt it to my own file.
Awesome thank you for sharing!
Another option to insert date is using date validation. Once you double click on the cell, the calendar will pop up, and you just select it. Just two clicks! No code required.
Thank you for sharing!
how do you make it please?
Very useful tool, thanks for the video. I adapted the VBA code you shared for the mini-calendar, all worked ok, except that when I clicked out of the column I selected, the mini-Calendar don't go away.
Any suggestions to fix the inconvenient?
here is the modified code:
' Paste this procedure in the Worksheet object in the VBA Projects panel
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' check if the activecell is found within the "J" column (J8>>J42)of the Active Sheet
If Not Intersect(ActiveCell, Range("J8:J42")) Is Nothing Then
' display the calendar
ActiveSheet.Shapes("Calendario").Visible = True
' position the calendar next to the activecell
ActiveSheet.Shapes("Calendario").Left = ActiveCell.Left + ActiveCell.Width
ActiveSheet.Shapes("Calendario").Top = ActiveCell.Top + ActiveCell.Height
Else
' hide the calendar if a cell is selected outside "J8:J42" column (it does't work, the calendar does not disappear)
ActiveSheet.Shapes("Calendario").Visible = False
End If
End Sub
-----------------------------Thanks so much
To make the mini calendar close you need to click on the upper right corner of the calendar and you will see a little arrow click on that arrow and click delete.
Hi Kyle, I finished your Beginner to Advanced course and enjoyed it very much and thank you for that.
I have a question. Let's say i have a weekly work roster with workers named in column A, and the top row from column B on has Sun to Sat. The next row has the dates underneath the days.
If i have 15 workers and across from each of their names and under the dates are the specific duties each of them are on for the week.
How do i automatically update the dates each week as well as shift down each worker one row each week. For example, worker 1 this week is on row 1, next week he is on row 2 whereas worker 15 this week will, next week, move up the roster to row 1 position. I fully understand if you do not have time to help and it's ok if you cannot. It's just something i am designing for my workplace.
Kindest regards,
James.
Thank you glad you enjoyed it!
Did you ask this question in the q&a section of the Excel course?
@OfficeNewb Thank you Kyle, yes I did. It was unfair of me to ask as you are too busy
Kyle, This vid has been a great help to me. I am really interested in the formula that calculated the number of days from the dates in the two columns. I see the cells have been named - I tried with cell ref but didn't work. I there a video that can help me do this. Thanks Martin
If you look below the video, you will find a link to the Office Newb Blog that you can go to and download the resource file for this video.
see, there is always smarter people to teach you smarter ways to do things......👍
Thank you! Glad it was helpful!
Hello sir, thanks for the informative video, i wanted to ask if is it possible to add a data validation on that calender object through vba, wherein the user won't be able to select a date earlier than today's date?
Yes, it's possible to add data validation to a calendar object in VBA to prevent users from selecting a date earlier than today's date. You can achieve this by using a combination of the `BeforeUpdate` or `AfterUpdate` events of the calendar control and a condition to check the selected date.
Here is an example of how you can implement this in VBA:
Example with `BeforeUpdate` Event
1. Open your VBA editor (Alt + F11 in Excel).
2. Insert a new UserForm if you don't have one already.
3. Add a Calendar Control to your UserForm. If you don't see the Calendar Control, you may need to enable it from additional controls.
4. **Double-click the Calendar Control** to open its code window.
vba
Private Sub Calendar1_BeforeUpdate(Cancel As Integer)
Dim selectedDate As Date
selectedDate = Calendar1.Value
If selectedDate < Date Then
MsgBox "You cannot select a date earlier than today.", vbExclamation
Cancel = True
End If
End Sub
Example with `AfterUpdate` Event
1. Open your VBA editor (Alt + F11 in Excel).
2. Insert a new UserForm if you don't have one already.
3. Add a Calendar Control to your UserForm. If you don't see the Calendar Control, you may need to enable it from additional controls.
4. Double-click the Calendar Control to open its code window.
vba
Private Sub Calendar1_AfterUpdate()
Dim selectedDate As Date
selectedDate = Calendar1.Value
If selectedDate < Date Then
MsgBox "You cannot select a date earlier than today.", vbExclamation
' Optional: Reset to today's date
Calendar1.Value = Date
End If
End Sub
Explanation
- Calendar1_BeforeUpdate: This event is triggered before the calendar value is updated. The `Cancel` parameter allows you to cancel the update if the condition is met.
- Calendar1_AfterUpdate: This event is triggered after the calendar value is updated. Here, you can check the selected date and, if it's earlier than today, you can show a message and reset the calendar value to today's date.
Both methods effectively prevent the user from selecting a date earlier than today's date. You can choose the one that best fits your application's flow.
Love from Nepal
Thank you
Great idea - thanks for sharing - I have slight problem in that when I select a cell that already has a date in it, I get a message that pops up in the calendar - "Overwrite Contents" - OK or Cancel" . I there a way to suppress this message?
You can try using a little bit of VBA
1. Open the VBA Editor:
- Press `Alt + F11` to open the VBA editor.
2. Insert a New Module:
- In the VBA editor, go to `Insert` > `Module` to create a new module.
3. Add VBA Code:
- In the new module, add the following code:
Sub CalendarClick()
Application.DisplayAlerts = False
' Your code to handle the calendar click and update the cell goes here
' For example, you might be setting a date in the selected cell:
ActiveCell.Value = Date
Application.DisplayAlerts = True
End Sub
This VBA code turns off alerts before setting the cell value and turns them back on afterward. You will need to adapt the middle part of the code to match what your calendar add-in does when a date is selected.
4. Assign the Macro to the Calendar Add-in:
- If you are using a custom calendar add-in, you will need to find the event or button click that triggers the date selection and call this `CalendarClick` macro instead of directly setting the cell value.
5. Save and Close the VBA Editor:
- Save your VBA project and close the VBA editor.
6. Test the Macro:
- Go back to your Excel worksheet, trigger the calendar add-in, and select a date. The "Overwrite Contents" message should no longer appear.
If the calendar add-in is a third-party tool, you might need to check its documentation or settings to see if there’s an option to integrate or call custom macros upon date selection. If this option is not available, you might need to handle the date selection process entirely through VBA as demonstrated.
Remember to save your Excel file as a macro-enabled workbook (`.xlsm`) to ensure the VBA code is retained.
Great teacher!👍👍👍
Thank you! 😃
I have one of your courses and it’s good to learn from instructor like you! I have not finished but plan to continue
That's Great thank you!
Nice video, thanks. I want to use it in my file, but it gives me error message, my table has 6 columns and 79 rows,
May I know if I should change this line to "If Not Intersect(Target, Me.ListObjects("ProjectTable").ListColumns(6).DataBodyRange.Resize(, 79)) Is Nothing Then..." Thanks.
Hello Katie - The #6 in the ListColumns represent the column the date value is found. If the date is in another column you will want to change that number to reflect the column #. If there is only the one column then you can remove the .Resize(,79)
If Not Intersect(Target, Me.ListObjects("ProjectTable").ListColumns(6).DataBodyRange) Is Nothing Then
Is there a time picker also? I need a date and time picker for a time sheet project I'm working on. Thanks.
Have you tired searching Get Add-Ins to see if there is one for Time?
There is a button on the calendar that will insert the current time.
Very nice very cool
Glad you enjoyed it!
Great effort sir, but I am not able to see add ins in insert tab. I tried going to File - options - Add ins - in manage enable disable items. But still I couldn't find Add-Ins option anywhere. I am using Windows 7 Home Basic (64-bit).
If you go to the File tab then Account what version of excel are you using?
Thanks for the video, can you please share the formula to get the amount of days, thanks
If you down the practice file from the OfficeNewB Blog you can see the formula used to get the amount of days. We are just subtracting one date from the other.
@@OfficeNewb thanks
Great tool. Calendar add in works fine but the vba code gives me a run time error pop up and doesn't work.
Are you using the VBA Code on the worksheet Provided or on your own worksheet?
@@OfficeNewb I'm having the same problem, and I'm using it on my own worksheet
How to resize calendar? when i drag using cursor, only the white background enlarge not the calendar itself
The Calendar size is default. But there is a button below the calendar that allows you to toggle between 2 sizes.
can i know how to use data validation to prevent when we click calendar picker (not the way we fill the date in cell) and we only want only 1/1/2021 until 31/12/2024 only, if enter more than that it will be turn out to be clash. please help me
Date formatting in Excel is pulled from the region/language settings on your computer, that is the default.
@@OfficeNewb its not that the date is clash.. now i need to make the form information for wedding event.. so i need to show to my lecturer when i click the date other than 1/1/2024-31/12/2024, it will show us the clash date to us🥺
Does this work with 365 excel also?
Are you work with the Desk top app or does your excel open in a browser?
***SOLVED*** Error 9 in line 4:
the code gives an error in line 4: If Not Intersect(Target, Me.ListObjects("ProjectTable").ListColumns(3).DataBodyRange.Resize(, 2)) Is Nothing Then
I have renamed my calendar object to "Calendar" as the video instructed, and pasted everything in as directed, but no luck.
SOLVED IT:
Ok answered my own question. This code has 2 references that each user will have to specify that aren't mentioned in the video.
First, the OBJECT is a table that must be inserted ( click the "Insert" tab, then choose "Table") and then named ( rename your tables by clicking the Table Design tab, then on the left side set Table Name (ex: "Table15").
Second, the columns with dates need to be referenced. In this script, it is done by targeting the first column with a date and assuming the one immediately to the right of it is also a date (Resize(,2)).
So, if I had named my table "Table15" and wanted the 2nd and 3rd columns of the table to have the date selector, then this line should read:
If Not Intersect(Target, Me.ListObjects("Table15").ListColumns(2).DataBodyRange.Resize(, 2)) Is Nothing Then
Will it work in Excel 2010?
Looks like it may only be available in excel 2013 or later. Have you tried to add it and it's not there to add?
Is this code work inside a table only?
Can't find the formula anywhere here...😒
nevermind! found it.
Debug Error, Run-Time Error '9' Subscript out of range. Something wrong with "If Not Intersect(Target, Me.ListObjects("ProjectTable").ListColumns(3).DataBodyRange.Resize(, 2)) Is Nothing Then" It worked when I downloaded your file, but not on my own spreadsheet.
Unfortunately, it's a little difficult to help with problems on here because I cannot see your code or what is happening.
I need vba code
Did you click the link below the video to go to the Blog and download the Resource?