CODEWITHSUNDEEP
excelvba
David Doria
David Doria

Reputation: 10273

How to save the .List of a combobox to a variable

I would like to store all of the items from a combobox in a variable. What type should the variable be? I tried to make a 

Dim ComboBoxItems() As String ' dynamic array of strings

but then I am not sure how to copy the items from the combobox into this dynamic array?

I can do it in a loop:

for i = 1 to combobox.ListCount - 1
 ComboBoxItems(i+1) = combobox.List(i)
next i

(note, am I right that it is really crazy and a dynamic array is 1-indexed while the combobox List is 0-indexed??)

Is this the way to go? Or surely there is a way to copy the entire list without a loop?

Upvotes: 1

Views: 2790

Answers (1)

brettdj
brettdj

Reputation: 55682

For an ActiveX ComboBox on a worksheet called Combobox1 you could use:

Sub Test()
Dim vArr()
ReDim vArr(1 To ComboBox1.ListCount)
vArr = ComboBox1.List
End Sub

Same approach for a UserForm with a combobox ComboBox1 and command button CommandButton1

Private Sub CommandButton1_Click()
Dim S()
ReDim S(1 To Me.ComboBox1.ListCount)
S = Me.ComboBox1.List
End Sub

Private Sub UserForm_Initialize()
'Load some items into ComboBox
With Me.ComboBox1
.AddItem "fred, jones'"
.AddItem "mary"
.AddItem "wayne"
End With
End Sub

Upvotes: 2

Related Questions