VBA UserForm multiple comboboxes same value

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
611
Office Version
  1. 2016
Platform
  1. Windows
Hello forum friends, I have a UserForm with eight separate comboboxes that I want to have the months of the year in. Currently, I am using the code below to accomplish this. I think there must be a way to extend this to the other seven comboboxes without having to copy and paste this code seven more times. I'm hoping someone knows how I can do this. The other comboboxes in question are all named slightly different in that they start with a different character than 'B'... Thanks!

VBA Code:
Private Sub UserForm_Initialize()

    With BMonthComboBox
        .AddItem "January"
        .AddItem "February"
        .AddItem "March"
        .AddItem "April"
        .AddItem "May"
        .AddItem "June"
        .AddItem "July"
        .AddItem "August"
        .AddItem "September"
        .AddItem "October"
        .AddItem "November"
        .AddItem "December"
    End With
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
How about
VBA Code:
Private Sub UserForm_Initialize()
   Dim MonthAry(1 To 12) As Variant, ComboAry As Variant
   Dim i As Long
   
   ComboAry = Array("B", "C", "F", "H")
   For i = 1 To 12
      MonthAry(i) = MonthName(i)
   Next i
   For i = 0 To UBound(ComboAry)
      Me.Controls(ComboAry(i) & "MonthComboBox").List = MonthAry
   Next i
End Sub
Just change the initial letters in the array to suit
 
Upvote 0
Solution
Will this still display January, February... December in the comboboxes? I would prefer it if it did.
 
Upvote 0
Thank you @Fluff one more question. The code below that you gave me is for one set of three comboboxes (BYearComboBox, BMonthComboBox and BDayComboBox). Is there a way to make this work with the other seven combobox combinations or do I copy and paste the code seven times? Thanks!
VBA Code:
Dim Dob As Variant
        
        On Error Resume Next
        Dob = DateSerial(Me.BYearComboBox, Month(DateValue(Me.BMonthComboBox & "/01/2020")), Me.BDayComboBox)
        On Error GoTo 0
 
Upvote 0
You could put the dates into an array like
VBA Code:
   Dim DobAry(6) As Variant, ComboAry As Variant
   
   ComboAry = Array("B", "C", "F", "H")
   On Error Resume Next
   For i = 0 To 6
      DobAry(i) = DateSerial(Me.Controls(ComboAry(i) & "YearComboBox"), Month(DateValue(Me.Controls(ComboAry(i) & "MonthComboBox") & "/01/2020")), Me.Controls(ComboAry(i) & "DayComboBox"))
   Next i
   On Error GoTo 0
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top