VBA & Macro Problems

Set the Default Year in a Microsoft Office VBA UserForm Calendar

Anum MushtaqAnum Mushtaq Nov 14, 2026 868 views

Question details

Set a UserForm calendar's default year dynamically, such as 23 years before the current year.

Set the Default Year in a Microsoft Office VBA UserForm Calendar
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the UserForm code

In the Office application, open Developer > Visual Basic, find the UserForm in Project Explorer, and open its code. Locate the UserForm_Initialize procedure.

2
Select the calculated year

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

3
Check the calendar update

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.

Find and select the relative year in the UserForm list
Include the target year in the list: The example shows a message if the target year is missing. Expand the code that populates the list if necessary. Selecting a year is separate from setting keyboard focus on the control.
Free Microsoft Office alternative

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.

Open and edit Microsoft Excel XLSX and Word DOCX filesUse a familiar interface for everyday spreadsheet and document workMove common Office files into a lightweight productivity suiteKeep calendar-related spreadsheet data accessible for everyday editing
QA img-9

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.