Mini Calendar Add-In for Excel and a little VBA code
HTML-код
- Опубликовано: 17 май 2023
- How to Add-In the Mini Calendar option into Excel.
Use it to quickly select a date to insert into a cell.
Also, a little VBA code to have the calendar popup when needed!
Click to Download Resource
www.officenewb.com/post/mini-...
![How To Add 3 Different Date Picker Calendars in Microsoft Excel [Free Download]](http://i.ytimg.com/vi/PLAJ08LP7kE/mqdefault.jpg)
Great little add-in! And great lesson as always. Thanks Kyle!
Thank you! Glad you enjoyed it!
This has been so helpful for a project tracker I created. Much appreciated Kyle.
Great! I'm so glad
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!
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.
really useful video! 👍 I managed to adapt it to my own file.
Awesome thank you for sharing!
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!
Love from Nepal
Thank you
Very nice very cool
Glad you enjoyed it!
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!
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
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.
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.
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?
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 video.
Is it possible to use this calendar to find date on the sheet?
It's just a regular calendar
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.
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.
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.
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 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?
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
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?
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.