I have a Form with multiple text boxes that I want to transfer to th
spreadsheet once the form is filled out. I've used the following macr
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 get
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
put in TextBox1.Value it works but I need to do this 85 times an
trying to save time

--
Excellan
-----------------------------------------------------------------------
Excellant's Profile: http://www.excelforum.com/member.php...fo&userid=2896
View this thread: http://www.excelforum.com/showthread.php?threadid=48690

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" wrote
in message ...

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

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


--
Excellant
------------------------------------------------------------------------
Excellant's Profile: http://www.excelforum.com/member.php...o&userid=28963
View this thread: http://www.excelforum.com/showthread...hreadid=486909