VBA & Macro Problems

How to Populate a VBA Combo Box with Correct Days of the Month

Chanuka Geekiyanage Oct 15, 2026 868 views

Question details

The user needs to dynamically populate a UserForm combo box with the correct number of days for a selected month using VBA, accounting for varying month lengths and leap years.

How to Populate a VBA Combo Box with the Correct Days of the Month

Product: Excel
Device & OS: not provided
Scenario: Developing a custom VBA UserForm for data entry where users select a month and a day from dependent dropdown lists.
Observed behavior: The day combo box needs to automatically update to show 28, 29, 30, or 31 days depending on the specific month selected in the month combo box, rather than using static lists.

Before you start

Ensure that the Developer tab is enabled in your Excel ribbon and that you have an active VBA UserForm containing at least two combo boxes (one for the month and one for the day).

Solution 1Recommended

Use DateAdd and DateValue to Calculate Month Days

Use a Change event on the month combo box to calculate the last day of the selected month, then loop through the days to populate the day combo box.

This method leverages Excel's built-in date functions to determine the exact number of days in a month dynamically. By calculating the day before the first day of the next month, VBA automatically handles leap years (e.g., February 29th) without requiring complex conditional logic.

Access the VBA Editor

Press Alt + F11 to open the VBA Editor, then double-click your UserForm in the Project Explorer to view it.

Verify Combo Box Names

Check the Properties window (F4) to ensure your month combo box is named 'cboMonths' and your day combo box is named 'cboDays'.

Create the Change Event

Right-click the 'cboMonths' combo box on the UserForm and select 'View Code' to generate the 'Private Sub cboMonths_Change()' event block.

Insert the Calculation Logic

Inside the sub, use 'Me.cboDays.Clear' to reset the days list. Then, create a date string for the first of the selected month using 'd1 = DateValue("1-" & Me.cboMonths & "-" & Year(Date))'.

Calculate the Final Day and Populate

Use 'm = Day(DateAdd("m", 1, d1) - 1)' to find the last day. Finally, create a loop 'For i = 1 To m' and insert 'Me.cboDays.AddItem i' to populate the dropdown.

Use DateAdd and DateValue to Calculate Month Days

Automatic Leap Year Support: Because this script uses the current year via the Year(Date) function and native date calculation, leap years are automatically supported whenever the current year is a leap year.

Advanced Macro Capabilities

Run Advanced VBA Macros Seamlessly with WPS Office

WPS Spreadsheet offers excellent compatibility with Excel VBA, allowing you to run complex UserForms, dependent combo boxes, and macro scripts seamlessly. Experience a lightweight, powerful, and highly compatible tool for all your data automation needs.

1. Install WPS Office: Download and install WPS Office on your computer to access the advanced Spreadsheet tools.
2. Open Your Macro-Enabled File: Launch WPS Spreadsheet and open your existing .xlsm or .xlsb workbook.
3. Access the Developer Tab: Navigate to the Developer tab on the top ribbon to access the VBA Editor and manage your UserForms.
4. Execute Your Macros: Run your combo box macros and data entry forms exactly as you would in Microsoft Excel.

Highly compatible with Microsoft Excel VBA scripts and UserFormsLightweight application that opens large macro-enabled workbooks quicklySupports custom macro development for streamlined data entry workflows

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't my VBA combo box clear the previous days when I select a new month?

You must include the 'Me.cboDays.Clear' command at the very beginning of your month combo box's Change event. This ensures the previous month's list is completely removed before the code loops to add the new days.

Can I use a ListBox instead of a ComboBox for the months?

Yes. If you use a ListBox (e.g., named 'ListBox1'), you will trigger the code using the 'Private Sub ListBox1_Change()' event instead. The date calculation logic and looping structure will remain exactly the same.

How do I account for a specific selected year instead of the current year?

Instead of using 'Year(Date)' in the DateValue function, you can replace it with a reference to another combo box where the user selects the year (e.g., 'Me.cboYears'). This is crucial if users need to select dates for past or future leap years.

What happens to the code if the month combo box is left blank?

By wrapping the calculation logic inside an 'If Me.cboMonths <> "" Then' statement, the script safely bypasses the calculation if the combo box is cleared or left blank, preventing a 'Type Mismatch' runtime error.

Excel & Data Problems