VBA UserForm multiple comboboxes same value

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,467
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
Will this still display January, February... December in the comboboxes? I would prefer it if it did.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,467
Office Version
  1. 365
Platform
  1. Windows
Yes it will. :)
 

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,467
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,361
Messages
5,635,812
Members
416,884
Latest member
leeshjay

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
Top