Excel pop Up Calender

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Powered by WordPress.com.

Up ↑

Luke Turner

Just another WordPress.com site

Bramall Gardens

Design & Landscaping Specialists

MudlThru: life & tech situations

Not just another WordPress.com site

Heritage Calling

A Historic England Blog

Luke Turner

Do the dumbass thing your scared shitless of doing, then get the courage to do it after

Discover

A daily selection of the best content published on WordPress, collected for you by humans who love to read.

%d bloggers like this: