I am attempting to collect textbox input within a for loop in Sheet1.
It does not stop on the activate and just zooms through the for loop
without prompting for the field. Then it should transfer the data which
was input to a range in Sheet2. Anyone who can tell me what is wrong?

Thanks so much.
-Michael

------------------------------------------------------------------------------------------------------------------------------------

Private Sub Workbook_Open()

Dim OLEObj As OLEObject
Dim iCtr As Long
Dim CellToCheck, Cell2ToCheck, CellToGet, Cell2ToGet As Range


Set CellToGet = Sheet2.Range("d1")


' gather initial 4 textboxes

For iCtr = 1 To 4
Set OLEObj = Sheet1.OLEObjects("TextBox" & iCtr)
OLEObj.Activate
With OLEObj.Object
CellToGet.Value = .Value
End With
'dropdown one row for the next text box
Set CellToGet = CellToGet.Offset(1, 0)

Next iCtr

End Sub

Mike, Your code is doing exactly what your telling it to do. The Activate
method only select the object, but will not stop the code from executing.
Maybe you can try the InputBox function...

enjoy, Rick


wrote in message
oups.com...
I am attempting to collect textbox input within a for loop in Sheet1.
It does not stop on the activate and just zooms through the for loop
without prompting for the field. Then it should transfer the data which
was input to a range in Sheet2. Anyone who can tell me what is wrong?

Thanks so much.
-Michael

--------------------------------------------------------------------------
----------------------------------------------------------

Private Sub Workbook_Open()

Dim OLEObj As OLEObject
Dim iCtr As Long
Dim CellToCheck, Cell2ToCheck, CellToGet, Cell2ToGet As Range


Set CellToGet = Sheet2.Range("d1")


' gather initial 4 textboxes

For iCtr = 1 To 4
Set OLEObj = Sheet1.OLEObjects("TextBox" & iCtr)
OLEObj.Activate
With OLEObj.Object
CellToGet.Value = .Value
End With
'dropdown one row for the next text box
Set CellToGet = CellToGet.Offset(1, 0)

Next iCtr

End Sub