Collecting textbox input within a for loop

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


<[email protected]> wrote in message
news:[email protected]...
> 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
>