CODEWITHSUNDEEP
excelvba
smirnoff103
smirnoff103

Reputation: 328

Excel VBA: Populate combobox (userform type) from array

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?

Upvotes: 0

Views: 723

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

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

Upvotes: 1

Related Questions