Some time ago I wanted to add a pop up calendar to an Excel workbook I was developing. After looking through the Add-On’s and then spending a reasonable amount of time searching the internet, I decided it would just be easier to create my own.
I styled the pop up using the desktop calendar as it seemed a familiar layout that people would understand.
My toolbox was limited to vba and userforms so ultimately the UI would feel basic in comparison to the modern Windows 10 styles. None the less I’m quite proud of my little pop up which can easily be imported into any workbook.
The above images show the main view and the year view or month selection. The month view displays the current or selected month dates with the previous and next months dates greyed out/disabled and today shown in red. The spin buttons allow for a quick scroll through the months.
Clicking on the month and year ‘header’ will switch the display to the year view where a different month can be selected. The year can be changed using the spin button and after selecting a month the view will switch back.
An example of the vba code used,
Public Sub UpdateCalander(selectedMonth As Integer, selectedYear As Integer) Dim thisDay As Integer thisDay = CInt(Format(Date, "dd")) Dim thisMonth As Integer thisMonth = Month(Now()) Dim thisYear As Integer thisYear = Year(Now()) Dim currentMonthStart As Date currentMonthStart = CDate("01/" & selectedMonth & "/" & selectedYear) Dim dayMonthStarted As Integer dayMonthStarted = weekday(currentMonthStart, vbMonday) Dim firstMondayFirstWeek As String firstMondayFirstWeek = Format(currentMonthStart - (dayMonthStarted - 1), "dd") Dim nextMonthStart As String nextMonthStart = DateAdd("m", 1, currentMonthStart) Dim currentMonthEnd As String currentMonthEnd = DateAdd("d", -1, nextMonthStart) Dim currentMonthMaxDay As String currentMonthMaxDay = Format(currentMonthEnd, "dd") Dim previousMonthEnd As String previousMonthEnd = DateAdd("d", -1, currentMonthStart) Dim DayButton(42) As CommandButton 'create array of buttons For i = 1 To 42 Set DayButton(i) = Controls("CommandButton" & i) Next i 'the month buttons can be styled using the following loops 'you can access the buttons as follows "DayButton(i).ForeColor" 'apply styling to all buttons For i = 1 To 42 DayButton(i).Enabled = True DayButton(i).ForeColor = RGB(9, 75, 122) Next i 'apply styling to buttons before current month starts For i = 1 To dayMonthStarted - 1 DayButton(i).Caption = CInt(firstMondayFirstWeek) + (i - 1) DayButton(i).Enabled = False Next i 'apply styling to current month buttons For i = dayMonthStarted To CInt(currentMonthMaxDay + dayMonthStarted) - 1 DayButton(i).Caption = i - (dayMonthStarted - 1) If i = thisDay + (dayMonthStarted - 1) And selectedMonth = thisMonth And selectedYear = thisYear Then DayButton(i).ForeColor = RGB(256, 0, 0) Next i 'apply styling to buttons of next month For i = currentMonthMaxDay + dayMonthStarted To 42 DayButton(i).Caption = i - (currentMonthMaxDay + dayMonthStarted) + 1 DayButton(i).Enabled = False Next i 'update the header text Select Case selectedMonth Case 1 HeaderText.Caption = "January" & " " & yearClicked Case 2 HeaderText.Caption = "February" & " " & yearClicked Case 3 HeaderText.Caption = "March" & " " & yearClicked Case 4 HeaderText.Caption = "April" & " " & yearClicked Case 5 HeaderText.Caption = "May" & " " & yearClicked Case 6 HeaderText.Caption = "June" & " " & yearClicked Case 7 HeaderText.Caption = "July" & " " & yearClicked Case 8 HeaderText.Caption = "August" & " " & yearClicked Case 9 HeaderText.Caption = "September" & " " & yearClicked Case 10 HeaderText.Caption = "October" & " " & yearClicked Case 11 HeaderText.Caption = "November" & " " & yearClicked Case 12 HeaderText.Caption = "December" & " " & yearClicked End Select End Sub
Here is a workbook showing the calendar icon and the result of selecting a date:
To get the pop up calendar, download a copy of the workbook. After downloading the workbook you’ll have access to the vba files which can be exported and saved.
Alternatively feel free to contact me if you want to know more or require