Populate a combo box with a string array in a user form

Question

I'm trying to solve an issue with a combo box, where I don't want the listed options to create another double every time the macro is executed, which happens if the AddItem property is only used to populate the combo box. The project is to list 12 months in a dropdown menu within a user form, with the goal being to let the user display that table in print preview mode (the accompanying worksheet has a table for each month). I wrote a string array for the months and then told it to populate the combo box with the AddItem property in a loop. To wit:

Private Sub ComboBox1_Change()

Dim strMonth(0 To 11) As String

strMonth(0) = "Print April Table"
strMonth(1) = "Print May Table"
strMonth(2) = "Print June Table"
strMonth(3) = "Print July Table"
strMonth(4) = "Print August Table"
strMonth(5) = "Print September Table"
strMonth(6) = "Print October Table"
strMonth(7) = "Print November Table"
strMonth(8) = "Print December Table"
strMonth(9) = "Print January Table"
strMonth(10) = "Print February Table"
strMonth(11) = "Print March Table"

Dim mthPosition As Long

For mthPosition = LBound(strMonth) To UBound(strMonth)
    UserForm13.ComboBox1.AddItem strMonth(mthPosition)

    Next mthPosition

With UserForm13.ComboBox1

    .Style = fmStyleDropDownList
    End With

    UserForm13.Show
End Sub

For some reason, I get an error at the AddItem line saying that VBA could not find the specified object, even though the path is specified... the same thing happens if the code is run under a routine for the ComboBox or tested in a separate routine.

I appreciate your help in this respect.

Are you sure your other form is actually called UserForm13 and that the combo box on it is actually called ComboBox1? — Robin Mackenzie
– Robin Mackenzie, Commented Jun 27, 2016 at 4:06
Robin, indeed - I checked the names of things and there was no issue I could find. — epicUsername
– epicUsername, Commented Jun 27, 2016 at 20:05
Thomas - the project is divided into modules...this code is in its own module and the userform's components call the code as needed. — epicUsername
– epicUsername, Commented Jun 27, 2016 at 20:07

Tim Williams · Accepted Answer · 2016-06-27 06:08:21Z

2

Don't need an array for this (though that's likely not the cause of your error:

Dim m As Long, s As String

For m = 1 To 12
    s = "Print " & MonthName(((m + 2) Mod 12) + 1) & " Table"
    UserForm13.ComboBox1.AddItem s
Next m

answered Jun 27, 2016 at 6:08

Tim Williams

169k8 gold badges104 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

epicUsername Over a year ago

Tim, thank you much. Your solution works even better from the point of view of being a more effective use of the code...I'll probably fall back on it on this project. Thanks again!

user3598756 · Accepted Answer · 2016-06-27 05:48:11Z

0

Provided you check for actual userforms and combobox names, you can try this:

Private Sub ComboBox1_Change()

Dim strMonth(1 To 12) As String

strMonth(1) = "Print April Table"
strMonth(2) = "Print May Table"
strMonth(3) = "Print June Table"
strMonth(4) = "Print July Table"
strMonth(5) = "Print August Table"
strMonth(6) = "Print September Table"
strMonth(7) = "Print October Table"
strMonth(8) = "Print November Table"
strMonth(9) = "Print December Table"
strMonth(10) = "Print January Table"
strMonth(11) = "Print February Table"
strMonth(12) = "Print March Table"

With UserForm13
    With .ComboBox1
        .Clear
        .List = strMonth
        .Style = fmStyleDropDownList
    End With
    .Show
End With
Unload UserForm13

End Sub

answered Jun 27, 2016 at 5:48

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

2 Comments

epicUsername Over a year ago

^This suggestion works beautifully. I did not know about using the .List property. Thank you!

user3598756 Over a year ago

You're welcome. If I fullfilled your question please mark the answer as accepted. Thank you

AndrewT · Accepted Answer · 2016-06-27 04:20:18Z

0

Try Robins suggestion as it is the most likely solution if that is correct then the object it is complaining about must be array.

The follow should do the same thing as your function you could test that.

Private Sub ComboBox1_Change()
   With UserForm13.ComboBox1
      .AddItem "Print April Table"
      .AddItem "Print May Table"
      .AddItem "Print June Table"
      .AddItem "Print July Table"
      .AddItem "Print August Table"
      .AddItem "Print September Table"
      .AddItem "Print October Table"
      .AddItem "Print November Table"
      .AddItem "Print December Table"
      .AddItem "Print January Table"
      .AddItem "Print February Table"
      .AddItem "Print March Table"
      .Style = fmStyleDropDownList
   End With
   UserForm13.Show
End Sub

If it errors on the line "With UserForm13.ComboBox1" then Robin is right the user-form or combo box must have another name.

Good luck

answered Jun 27, 2016 at 4:20

AndrewT

5182 gold badges9 silver badges23 bronze badges

2 Comments

epicUsername Over a year ago

Andrew, this was exactly what I had earlier, and it was what generated doubles and triples of the month names in the menu every time I run the code; the way it's set up is that a button on the spreadsheet calls this sub when it's clicked and you go ahead and print whatever monthly table you want.

AndrewT Over a year ago

If you are getting duplicates then clear it before you add the values ComboBox1.Clear.

user6432984 · Accepted Answer · 2016-06-27 05:13:41Z

0

If you are running ComboBox1_Change() event from Userform13 then your will get this error

Form already displayed; can't show modally

Unless you set Userform13's ShowModal to false on Userform13's Property page.

answered Jun 27, 2016 at 5:13

user6432984

Collectives™ on Stack Overflow

Populate a combo box with a string array in a user form

4 Answers 4

1 Comment

2 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related