Excel VBA: Populate combobox (userform type) from array

Question

I'm trying to do something (I think) easy, but I can't find it.

At the start of my Excel file I add the string values from the array (which is created meanwhile because you can't define a const string array):

Private Sub Workbook_Open()
    CompleteAddList
End Sub

/

Public Function CompleteAddList(
    For Each a In ArrAddList
        With ActiveWorkbook.Sheets("Sheet 1").Shapes("AddList1").ControlFormat
            .List = a         
        End With
    MsgBox (a)
    Next a
End Function

/

Public Function ArrAddList()
    ArrAddList = Array("Text1", "Text2")
End Function

I see the message boxes, but the data is not stored in the combobox (it's still empty). Is this because it's in a public function ? Or is it just not correct the way I wrote it?

It's a "form control" type, the second from the top left here on the image: stackoverflow.com/questions/50144020/… — smirnoff103
– smirnoff103, Commented Jul 6, 2021 at 13:16
OK. Got it. Anyhow, I tried posting two pieces of code able to deal with both sheet combo boxes... — FaneDuru
– FaneDuru, Commented Jul 6, 2021 at 13:30

FaneDuru · Accepted Answer · 2021-07-06 13:15:24Z

1

Please, test the next code. It assumes that the combo in discussion is a Form type:

Sub testDropDownFill()
  Dim sh As Worksheet, cb As DropDown, ArrAddList
  
  Set sh = Sheets("Sheet 1") 'take care of the space between Sheet and 1
  Set cb = sh.DropDowns("AddList1")
  ArrAddList = Array("Text1", "Text2")
  cb.list = ""
  cb.list = ArrAddList
End Sub

If the combo in discussion is an ActiveX type, plese use the next code:

Sub testComboActXFill()
  Dim sh As Worksheet, cb As MSForms.ComboBox, ArrAddList
  
  Set sh = Sheets("Sheet 1") 'take care of the space between Sheet and 1
  Set cb = sh.OLEObjects("ComboBox1").Object
  ArrAddList = Array("Text1", "Text2")
  cb.Clear
  cb.list = ArrAddList
End Sub

answered Jul 6, 2021 at 13:15

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

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

1 Comment

FaneDuru Over a year ago

@smirnoff103 Glad I could help!

Collectives™ on Stack Overflow

Excel VBA: Populate combobox (userform type) from array

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related