using Variable in control names on user form

[Florence1550]

I have a user Form and I want to cycle through the controls and only put the values to the sheet that are not empty but can I use for example below

for I = 1 to 8
If Not Me.Combox(i).value = vbNullString Then
next i
ws.Cells(lastrow, 1).value = Date
ws.Cells(lastrow, 2).value = me.txtname.Value
ws.Cells(lastrow, 4).value = me.txtWBS.Value
ws.Cells(lastrow, 5).value = me.txtRef.Value
ws.Cells(lastrow, 6).value = Out
ws.Cells(lastrow, 7).value = me.combox(i).value
ws.Cells(lastrow, 8).value = me.txtQty(i).Value
ws.Cells(lastrow, 9).value = me.txtRef.Value
End If

Many thanks for the time.

 

[RoryA]

You need to use the controls collection:

If Me.Controls("Combobox" & i).value <> vbNullString Then

 

[Florence1550]

You need to use the controls collection:

If Me.Controls("Combobox" & i).value <> vbNullString Then

Many thanks for this and thee fast response

 

[Florence1550]

You need to use the controls collection:

If Me.Controls("Combobox" & i).value <> vbNullString Then

Sorry to be a pain would it be as below I think i have the loops wrong? or a next or else missing

For i = 1 To 8
If Me.Controls("Combobox" & i).Value <> vbNullString Then
ws.Cells(lastRow, 1).Value = Date
ws.Cells(lastRow, 2).Value = Me.txtName.Value
ws.Cells(lastRow, 4).Value = Me.txtWBS.Value
ws.Cells(lastRow, 5).Value = Me.txtRef.Value
ws.Cells(lastRow, 6).Value = Out
ws.Cells(lastRow, 7).Value = Me.Controls("Combobox" & i).Value
ws.Cells(lastRow, 8).Value = Me.Controls("Textbox" & i).Value
ws.Cells(lastRow, 9).Value = Me.txtRef.Value
Next i
End If

 

[RoryA]

End If should be before Next i

 

[Florence1550]

End If should be before Next i

Many thanks

 

[Florence1550]

Many thanks

Many thanks

Coming up with error on

 

[RoryA]

What are the actual names of the controls? I just noticed you had Combox1 in your original code while I assumed Combobox1 etc.