How to treat message box inputs like an array

[MFGO12]

HI! I've searched and experiemented, but I can NOT get <acronym>VBA</acronym> to post sequential message box values into a spreadsheet. What I'm trying to do is to loop through, e.g. spreadsheet cell (1,2) = textbox1.value; spreadsheet cell (2,2) = textbox2.value; etc.

Following some advice from other forms, I tried the code below, but on this line:

Set TBarray(iCount) = Controls("Txt_WhoAction" & iCount)

debugger says: "could not find specified object"

I tried adding ".value" as in: Set TBarray(iCount) = Controls("Txt_WhoAction" & iCount).value

but this didn't work either.

any suggestions? Code below:

Private Sub Cmd_ProjEnter_Click()

Dim iRow As Long
Dim ws As Worksheet
Dim Txt_WhoAction(5) As String
Dim strControlName As String
Dim iCount As Integer

Dim TBarray(1 To 5) As Control

Set ws = Worksheets("Sheet2")
'''find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.Txt_Rep.Value
ws.Cells(iRow, 2).Value = Me.Txt_CoCity.Value
ws.Cells(iRow, 3).Value = Me.Txt_ProName.Value
ws.Cells(iRow, 4).Value = Me.Txt_DateQuoted.Value

For iCount = 1 To 5
Set TBarray(iCount) = Controls("Txt_WhoAction" & iCount)
Next iCount

For iCount = 1 To 5
ws.Cells(iRow, iCount + 5).Value = TBarray(iCount).Value

Next iCount


'ws.Cells(iRow, 4).Value = Me.Txt_WhoAction(iRow).Value
ws.Cells(iRow, 4).Value = Me.Txt_ActionWhat1.Value
ws.Cells(iRow, 4).Value = Me.Txt_ActionWhen1.Value
If CkBx_Action1 = False Then
MsgBox "You unchecked the box"
Else
MsgBox "You checked the box"
End If

End Sub

 

[MFGO12]

So, the solution was remarkably easy. Thanks to Anthony's Excel solutions website for the answer.

Where: "Txt_WhoAction1, Txt_WhoAction2..." is the array of textboxes I used for entering who does the action, ws is the object holding the name of the worksheet, and Userform1 is the active user form .:

For iCount = 1 To 9
ws.Cells(iRow + iCount, 6).Value = UserForm1.Controls("Txt_WhoAction" & iCount).Text
Next iCount

Regards,
MFGO12 (many faces of Go! queue 12)