Are you looking for a macro to select the date? Recently I was looking for some type of calendar or built-in dialog box that would enable a user to select the date which I would then feed into my macro. I saw one option but that looked too involved for my purposes so I wrote my own function.
The Code for the Macro to Select the Date
Here is my macro to select the date. Copy it into your code module sheet in the Visual Basic Editor, then use it.
Private Function GetDate(Optional SelectDay As Boolean = True, _
Optional DefaultDate) As Date
' The default date is today if not specified
Dim MyResponse
Dim MyYear As Integer, MyMonth As Integer, MyDay As Integer, DayUpperBound As Integer
' Set defaults
If IsMissing(DefaultDate) Then ' Select today
DefaultDate = DateSerial(Year(Date), Month(Date), Day(Date))
End If
GetDate = DefaultDate ' If any problems then return the default
' Year
Do
MyResponse = Application.InputBox("Please enter the year", "Year", Year(DefaultDate), Type:=1)
If MyResponse = False Then Exit Function
Loop Until CInt(MyResponse) > 1900 And CInt(MyResponse) < 2100
MyYear = CInt(MyResponse)
' Month
Do
MyResponse = Application.InputBox("Please enter the month", "Month", Month(DefaultDate), Type:=1)
If MyResponse = False Then Exit Function
Loop Until CInt(MyResponse) >= 1 And CInt(MyResponse) <= 12
MyMonth = CInt(MyResponse)
' Day
If SelectDay Then
Select Case MyMonth ' Calculate upper bound for the day
Case 1, 3, 5, 7, 8, 10, 12
DayUpperBound = 31
Case 4, 6, 9, 11
DayUpperBound = 30
Case Else ' 2 = February
If MyYear Mod 4 = 0 Then DayUpperBound = 29 Else DayUpperBound = 28
End Select
Do
MyResponse = Application.InputBox("Please enter the day of the month (1 - " & DayUpperBound & ")", _
"Day", Day(DefaultDate), Type:=1)
If MyResponse = False Then Exit Function
Loop Until CInt(MyResponse) >= 1 And CInt(MyResponse) <= DayUpperBound
MyDay = CInt(MyResponse)
Else
MyDay = Day(DefaultDate)
End If
GetDate = DateSerial(MyYear, MyMonth, MyDay)
End Function
Call the Macro to Select the Date
Here is how I used it in my report:
MyDate = GetDate(False, DateSerial(Year(Now), Month(Now) - 1, 1))
I only wanted to select the month and year and not the day of the month so I set the first optional SelectDay argument to False.
If you enter just a month and a year for a date in Excel then the day is automatically set to 1, i.e. the first of the month. If the user sets the SelectDay argument in the function to False and specifies a default date, then the function uses the day in the default date for its output. If the default date is not specified, then it uses the day from today’s date. So I set the day for the default date equal to 1, i.e. the last argument in the DateSerial function.
I wanted to select the previous month so I set the default value to the current month (a number between 1 and 12) and then subtracted 1. The built-in DateSerial function automatically handles the case for when the current month is January. For example, if today was the 15th January then the default date sent to the GetDate function would be 1-Dec-2014.
You could equally call this function without any parameters and the function would just use the defaults:
MyDate = GetDate()
Here is what the dialog boxes look like when you run it using GetDate(). See how the defaults make it easy for the user to enter the date. If the value is correct then they only need to press Enter!
Selecting the year:
Dialog box to select the year
Selecting the month:
Dialog box to select the month
Selecting the day:
Dialog box to select the day (optional)
Overview of the GetDate Function
Here is the flow of the macro:
- Work out the default date if it is not specified, then set the GetDate function equal to the default date.
- Prompt the user for the year and check that it is valid.
- Prompt the user for the month and check that it is valid.
- If the SelectDay argument is True then work out how many days are in the selected month then prompt the user for the day.
- Set GetDate to the selected values.
Other References
If you are interested in a Microsoft looking date selector and don’t mind following some instructions to set up a UserForm then take a look at this site: