Set the Default Year in a Microsoft Office VBA UserForm Calendar
Question details
Set a UserForm calendar's default year dynamically, such as 23 years before the current year.

- Product
- Microsoft Office VBA UserForm calendar
- Device & OS
- not provided
- Scenario
- A VBA UserForm uses a year list to choose the year displayed by a calendar.
- Observed behavior
- The calendar opens on the wrong year because the year list uses a fixed list index.
Open the UserForm in the Visual Basic Editor and identify the control that contains the year list and the code that fills it. Confirm which column contains the year and how selecting a year updates the calendar.
Find and select the relative year in the UserForm list
Use this approach when the calendar year is controlled by a ComboBox or ListBox containing year values.
Calculate the target year, then search the populated list for that value. This avoids relying on a fixed ListIndex, which can select a different year if the list order or range changes.
In the Office application, open Developer > Visual Basic, find the UserForm in Project Explorer, and open its code. Locate the UserForm_Initialize procedure.
After the code that populates the year list, add the following code inside the existing UserForm_Initialize procedure. Replace ComboBox1 with your year control's name and change the column number if the years are not in the first column: Dim i As Long Dim targetYear As String targetYear = CStr(Year(Date) - 23) Me.ComboBox1.ListIndex = -1 For i = 0 To Me.ComboBox1.ListCount - 1 If CStr(Me.ComboBox1.List(i, 0)) = targetYear Then Me.ComboBox1.ListIndex = i Exit For End If Next i If Me.ComboBox1.ListIndex = -1 Then MsgBox "The target year is not in the list: " & targetYear End If
Run the UserForm and check that the selected year and calendar display match. If selecting the list item does not update the calendar, adjust the form's existing calendar-update code to use the selected year.

Work with Office files in WPS Office
WPS Office is a free, lightweight alternative with a familiar interface for opening and editing Office files. Its format support makes it easy to move everyday document and spreadsheet work, though it does not fix Microsoft Office VBA UserForms.

Frequently Asked Questions
Why does a fixed ListIndex select the wrong year?
ListIndex identifies a position, not a year. If the list's starting year or order changes, the same index can point to a different value. Search the populated list for the calculated year instead.
How do I choose a year other than 23 years ago?
In the UserForm_Initialize code, change 23 in Year(Date) - 23 to the desired number of years. For example, Year(Date) - 18 selects the year 18 years before the current year.
What if the calculated year is not in the list?
Check the code that fills the year ComboBox or ListBox and expand its range to include the target year. The example displays a message when it cannot find a match.
Why does the list show the right year but the calendar does not?
The year list and calendar may use separate update code. Check the UserForm's existing change event or calendar-update procedure and make sure it applies the selected year to the calendar.




