Form TextBox Transfer to Spreadsheet

[Excellant]

I have a Form with multiple text boxes that I want to transfer to the spreadsheet once the form is filled out. I've used the following macro but it gets hung up.

Private Sub cmdClose_Click()
Dim i As Integer
Dim myNail(84)
Dim TextBox(84)
For i = 0 To 84

Sheets("Haven OSAS").Select
Range("C" & i + 1).Select
Selection = TextBox(i).Value * Here is where it gets stuck
Next i
Unload Me
Sheets("Haven ME").Select

Range("A1").Select

End Sub

It doesn't recognize the TextBox(i).Value and stops the macro. If I put in TextBox1.Value it works but I need to do this 85 times and trying to save time.

[Tom Ogilvy]

Selection = me.controls("TextBox" & i+1).Value

Assumes by Form you mean Userform and the textboxes are named Textboxes are
named line TextBox1.

If the textboxes are on a sheet.

Selection = Worksheets("Data Entry").OleObjects("Textbox" & i +
1).Object.Value

--
Regards,
Tom Ogilvy

"Excellant" <[email protected]> wrote
in message news:[email protected]...
>
> I have a Form with multiple text boxes that I want to transfer to the
> spreadsheet once the form is filled out. I've used the following macro
> but it gets hung up.
>
> Private Sub cmdClose_Click()
> Dim i As Integer
> Dim myNail(84)
> Dim TextBox(84)
> For i = 0 To 84
>
> Sheets("Haven OSAS").Select
> Range("C" & i + 1).Select
> Selection = TextBox(i).Value * Here is where it gets
> stuck
> Next i
> Unload Me
> Sheets("Haven ME").Select
>
> Range("A1").Select
>
> End Sub
>
> It doesn't recognize the TextBox(i).Value and stops the macro. If I
> put in TextBox1.Value it works but I need to do this 85 times and
> trying to save time.
>
>
> --
> Excellant
> ------------------------------------------------------------------------
> Excellant's Profile:
http://www.excelforum.com/member.php...o&userid=28963
> View this thread: http://www.excelforum.com/showthread...hreadid=486909
>

[Excellant]

Thank you for a prompt response. I changed it and it worked like a charm.