Loop through Combobox

[PKennedyG77]

Hi,
Absolute amateur here.

I have created a form to allow information to be entered that will be put into a spreadsheet.

I am trying to loop through the comboboxes on the form to have the values assigned to a variable

Code is

Private Sub Cmdenter_Click()
Dim theDate As Date
Dim Member(1 To 50) As String
Dim MoneyIn(1 To 50) As Single
Dim c As Integer

TxtDate.Value = Format(TxtDate.Value, "dd-mm-yy")

theDate = TxtDate.Value

For c = 1 To 50
Member(c) = cboMember(c).Value

MoneyIn(c) = TxtMoneyIn(c).Value

MsgBox Member(c)
MsgBox MoneyIn(c)
Next c

End Sub

Getting an error on the combobox when running this.
No doubt something simple but cannot fix this.

 

[JEC]

Hi try this!
PS: your combobox is 0-based and your Members array is not

Private Sub Cmdenter_Click()
 Dim Member(1 To 50) As String, c As Long
 For c = 1 To 50
   Member(c) = cboMember.List(c - 1)
 Next
End Sub

To compare give it a try, this would just write the combobox list to the variable "a".

Private Sub Cmdenter_Click()
 Dim a
 a = cboMember.List
End Sub

And this is limited to 50 items:

Private Sub Cmdenter_Click()
 Dim a
 a = Application.Index(cboMember.List, [row(1:50)], 0)
End Sub

 

[PKennedyG77]

Thanks for your help.
I really appreciate it.

When I run the first code I am getting a runtime 424 Object Required error.
My combo boxes on the form are named cboMember1, cboMember2 and all the way up to 50.

I have checked spelling etc and all is OK

 

[Artik]

Try it

Private Sub Cmdenter_Click()
    Dim Member(1 To 50) As String
    Dim MoneyIn(1 To 50) As Single
    Dim c As Long

    For c = 1 To 50
        Member(c) = Me.Controls("cboMember" & c).Value
        MoneyIn(c) = Me.Controls("TxtMoneyIn" & c).Value

        MsgBox Member(c)
        MsgBox MoneyIn(c)
    Next c
End Sub

Artik

 

[PKennedyG77]

Try it

Private Sub Cmdenter_Click()
    Dim Member(1 To 50) As String
    Dim MoneyIn(1 To 50) As Single
    Dim c As Long

    For c = 1 To 50
        Member(c) = Me.Controls("cboMember" & c).Value
        MoneyIn(c) = Me.Controls("TxtMoneyIn" & c).Value

        MsgBox Member(c)
        MsgBox MoneyIn(c)
    Next c
End Sub

Artik

Worked perfectly.
Thanks

 

[Peter_SSs]

Worked perfectly.
Thanks

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.

BTW, I suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)