Excel VBA:从数组填充组合框(用户表单类型)

Question

我想做一些简单的事情，但我找不到。

在我的Excel文件的开头，我添加了数组中的字符串值(它是同时创建的，因为你不能定义常量字符串数组(：

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

我看到了消息框，但数据没有存储在组合框中(它仍然是空的(。这是因为它在公共场合吗？还是我写的不正确？

Answer 1

请测试下一个代码。它假设讨论中的组合是一种Form类型：

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

如果讨论中的组合是ActiveX类型，请使用下一个代码：

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