Printable View
Hey i want to copy a combobox list to an array but cant seem to find any code or command that will let me do that. I want to put it in a loop because the list is like 200 items so something like
for i = 1 to 200
structure(i)=combobox.value.row (i)
next i
something like that but nothing i tried works
please help
[VBA]structure(i) = combobox.Column(0,i).Value[/VBA]
try something like?
[VBA]Private Sub CommandButton1_Click()
Dim a() As Variant, i As Variant
a() = ComboBox1.List
For i = LBound(a) To UBound(a)
Debug.Print i, a(i, 0)
Next i
End Sub
Private Sub UserForm_Initialize()
Dim i As Integer
With ComboBox1
For i = 1 To 100
.AddItem i, i - 1
Next i
End With
End Sub[/VBA]
thanks for the quick reply however i tried the code it gave me
and invalid argument error
heres the code i put in
Private Sub CommandButton3_Click()
'structure.Hide
'struc.Show
'ComboBox2.Value = ""
'MsgBox structure(1)
Dim structure(1 To 105) As Variant, i As Variant
structure() = ComboBox2.List
For i = LBound(structure) To UBound(structure)
Debug.Print i, structure(i, 0)
Next i
MsgBox structure(5)
End Sub
Private Sub UserForm_Initialize()
Dim i As Integer
With ComboBox1
For i = 1 To 105
.AddItem i, i - 1
Next i
End With
i didnt really understand your code so i am not sure what i am doing wrong
I am hoping that someone will soon post an article on how to get the best help. The best help is when you get it from yourself once you know how we do it.
When posting code, please use VBA code tags. This is a great feature that this forum provides that none of the others do. Other forums just use the Code code tags. Click the VBA button in the enhanced editor and then paste your code. Or just type the tags where you replace ()'s with []'s. e.g. (VBA)MsgBox "Hello World!"(/VBA).
The List method for the ComboBox control is by default a 2 dimensional array when we think in those terms. The trouble with some Lists and Arrays is that they can be 0 or 1 based. VBE offers a line at the top of the object to be:
[vba]Option Base 0 'Default
'Option Base 1[/vba] It is generally a good idea to not use Option Base 1. Some functions like Split() create a 0 based string array no matter what Option you have set.
Tip, always use as the first line of code prior to any routines:
[vba]Option Explicit[/vba] The option can be set in your VBE's Tools > Options > Always Declare Variables.
So, you should observe by code that when I assigned the List to the array, it created a 2 dimensional array. You Redimmed the array as one dimensional.
There is another problem where you filled ComboBox1 in the Initialize event but then in the Click event for CommandButton3 you used ComboBox2 and ComboBox1. I have no idea if that was your intent.
In my example, you simply needed one ComboBox and one CommandButton. Paste the code in UserForm1 and you are set.
If you are still stuck, another nice feature of this forum is that you can attach files. Simply attach by the paperclip icon in the enhanced editor the most simple example that illustrates your problems.
This worked for me, it returns a 0 based array
[VBA]Dim myArray As Variant
myArray = ComboBox1.List: Rem 2-D array indexes 0 to ListCount-1, 0 to 9
ReDim Preserve myArray(0 To ComboBox1.ListCount - 1, 0 To 0)
myArray = Application.Transpose(myArray): rem converts to 1-D array 0-based
[/VBA]