CODEWITHSUNDEEP
vbaexcel
epicUsername
epicUsername

Reputation: 1009

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

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.

Upvotes: 0

Views: 10903

Answers (4)

Tim Williams
Tim Williams

Reputation: 166316

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

Upvotes: 2

user3598756
user3598756

Reputation: 29421

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

Upvotes: 0

user6432984
user6432984

Reputation:

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.

Upvotes: 0

AndrewT
AndrewT

Reputation: 498

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

Upvotes: 0

Related Questions