Excel VBA - Calendar on UserForm (Editable template available)

How to create calendar in Excel?
How to create calendar that allows users to select a date by using UserForm in VBA?

In this video, we will also learn, how to use the following functions:
1. DateValue
2. MonthName
3. Weekday
4. ActiveControl
and many more.

Welcome to join 🤗

#Excel #VBA

[ahsingtv]

Hope you like it. Thanks for watching and have a nice day.

[stevemack]

Excellent. Simply everything I was looking to understand. Thank you very much for this excellent video. I have now subscribed. 😁

[Huseyin_Cakici]

Teşekkürler, Gayet güzel bir içerik olmuş.

[Yo_NickyD]

Awesome work this is amazing. Is there away to get different dates in multiple different cells.

[gucluiskender]

Hi! First of all thank you for this lesson. I have a problem about a line (a=DateValue...) in Assigndate sub. I get this error ⇒Run-time error '13'
Type mismatch

I have seen error in this code:
a = DateValue ("1 " & " . " & userform1.labelmonth & " . "userform1.labelyear)

I have tried these:
Dim a as string
dim a as Integer
Dim a as Boolean
etc like this.

Could you help me please!
Thank you again

[fatimahslimany]

I have question
the sequences of the frames from the second line become 33 and not like what it suppose to be but I do the same with you
how can I fix it

[michaelallen]

Is it possible to add frames to the form with vba code.

[lamraft]

Hi teacher
Thank you for these tutorials, I made such thanks to you, but when I want to move from December to a new year, or from January to a previous year, I find a problem, and the same thing even in the file you created. What is the solution please.

[trader.fx]

Hi sir. From which site can I download DatePicker and Microsoft MonthView Control 6.0 for Excel Office2016? Thanks

[AntonioDettori-fp]

Very nice video.
I tried to make a worksheet by my self with your tutorial.
The calendar shows up, but the frames don't work. Also the label2 goes only from January to December: commandbutton3 doesn't work from January 2023 to December 2022 and commandbutton4 doesn't work from December 2023 to January 2024. I don't understand seems all codes you made are correct.
I would like to show you my worksheet but I don't know Please help very Have a nice day

[domjamie]

Hi, can we amend the frame sub routines to include multiple cells whereby we want to add a date? eg Sheet1.Cells(1, 2, "A") etc? If so, what is the syntax for this? Thanks

[mymish]

Hi sir, i have question. how can i set multiple date picker in 1 userform? i use command button instead of frame.

my calendar userform :

Private Sub CommandButton1_Click()
grn.TextBox2.Value = & " " & calendar1.Label2.Caption & " " & calendar1.Label1.Caption
Unload Me
End Sub

[fufete]

Sir, i got 2 more questions:
1- Is there a way to keep the month label in the form as a name ("january, march, etc.") but when the date is paste in our sheet is pasterd as numers (i.e. "01/04/2023")?
2- Is there a way to change the display (visualy) of the calendar when we click on the button without moving the button? (say i have the button in A1 but when i click it it pops up in M28 (aprox).

[NurAishah-llgf]

hi, may i ask u why the sub assigndate() become yellow colour and "variable are not defined?

Sub Assigndate()

a = DateValue("1 " & UserForm1.Label2 & " " & UserForm1.Label1)
firstsun = a - Day(a) - Weekday(a - Day(a), vbSunday) + 8

If Day(firstsun + 1) <= 2 Then
firstday = firstsun - 1
Else
firstday = firstsun - 8
End If

Dim Frame As Control
For Each Frame In UserForm1.Controls
If TypeName(Frame) = "Frame" Then
i = i + 1
Frame.Caption = Format(Day(firstday + 1), "0#")
If MonthName(Month(firstday + i)) = UserForm1.Label2 Then
Frame.Enabled = True
Else
Frame.Enabled = False
End If
End If
Next



End Sub

[marounaziz]

Thank you for the video!
I am having some issues, spent a day and i can't figure out where the problem.
The calendar is showing wrong first Sunday, while i did the same steps and wrote the same things as you did in the video.

Do you think you can assist in figuring out where is the error?

Sub Assigndate()

a = DateValue("1 " & Calendar.Label2 & " " & Calendar.Label1)
firstsun = a - Day(a) - Weekday(a - Day(a), vbSunday) + 8

If Day(firstsun + 1) <= 2 Then
firstday = firstsun - 1
Else
firstday = firstsun - 8
End If

Dim Frame As Control
For Each Frame In Calendar.Controls
If TypeName(Frame) = "Frame" Then
i = i + 1
Frame.Caption = Format(Day(fristday + i), "0#")
If MonthName(Month(firstday + i)) = Calendar.Label2 Then
Frame.Enabled = True
Else
Frame.Enabled = False
End If
End If
Next

End Sub

[kamonratpromjiam]

Error 13 type mismatch at code : a=DayValue("1 " & calendar.lable2 & " " & calendar.label1) ?

[Elizabeth-qpuj]

This is the best calendar video, I learned so much. Thank you.
I'm having an issue and can't find an answer.

I have a set button on my Userform2 to show the calendar (Userform3).
The calendar works as expected & the date is entered into Userform2.TextBox1, as expected via Private Sub Frame02_Click(), for example.

The problem occurs when I begin at my main menu (Userform1), then show Userform2, then click on the set button.
The calendar (Userform3) acts as expected, but the Userform2.TextBox1 is no longer populated.
I get the error: Userform2.TextBox1 = <Object variable or With block variable not set>

I'm thinking it is a simple error that shows my inexperience with userforms, since it works with 1 userform, but not when I'm 2 userforms deep.
I tried moving the code from Userform3 to Userform2, but then it doesn't recognize the Userform3 variables.
I've tried making the Userform3 Public vs Private. I've tried Modal and Modeless. I've tried declaring the Userform2 while in Userform3. I've tried Userform3.Hide instead of Unload Me.

Can you please help me solve the error?

[EwanMcgillivray]

Hi, I cant see what the problem is here ? with this message code (run-time error 13)

Private Sub CommandButton3_Click()
If UserForm1.Label2.Caption = "January" Then
UserForm1.Label2.Caption = "December"
Else
UserForm1.Label2.Caption = MonthName(Month(DateValue("1 " & UserForm1.Label2 & "2022")) - 1)
End If
Assigndate

[proyecto]

Awesome Mr.
Now, it seems to me that frames make the userform slow :(
Could you change the days(frames) for labels? but that it only works on the days, I say this because it can affect other labels such as the month and the years, thanks.

[kentchong]

if i'm using one label with this "monthname(month(now))&" "&year(now)", how should i do with the only two command button?