Looking to create a macro that copies text from a textbox and pastes to the cell below

[willow-mouse]

Background:
I have a number of Excel visual schedules that are effectively a grid with horizontal swimlanes and vertical 'week commencing' dates.
On this has been placed a number (hundreds) of text boxes that indicate a task milestone.

I want to move this to another system that is better suited to this application, but having spent 10 minutes doing the following I'm about to lose my mind:
- Individually selecting each text box
- Selecting all the text
- Copying the text
- Deselecting the text box
- Selecting the cell directly underneath
- Pasting it in to the cell
- Deleting the text box

I've had a look around and there are bits of code that seem to do bits of this but I can't link it together with my limited knowledge:

Copy text from textbox and paste into cell of new sheet

I searching a way to copy all the text of the TextBox that is created by a previous macro and paste it into Cell A1 on Sheet3. Any suggestions?? Here is the previous macro: Sub Copy() Dim ...

stackoverflow.com

How to copy value from textbox to cell, and auto select cell below?

Hi All, I'm just confuse about simple things, let say i have 1 text box in userform how do I copy value or text from textbox in a userform and paste it to a cell and auto select cell below after that? and also the copy paste stopped if certain row was reached. example : I can copy paste &...

www.mrexcel.com

Copy and paste content from a textbox to a worksheet cell range?

I'm working on a VBA project to automate the Excel tool for a service desk queries tracker. I want, once the command button is clicked, to copy the content from a textbox to another worksheet row. ...

stackoverflow.com

Can anyone advise? I'd like something that can either do this individually or en-masse to get all this information into a usable state for me.

 

[Gokhan Aycan]

Please test on a copy first !!! Might require some tweaking according to where the TextBox is.

I have intentionally commented the 2 lines, uncomment after checking debug.print results.

Public Sub textbox_test()

    Dim cTB As OLEObject

    For Each cTB In Worksheets("Sheet1").OLEObjects
        'Debug.Print cTB.Name, cTB.progID
        If InStr(UCase(cTB.progID), "TEXTBOX") > 0 Then
            Debug.Print "TB: " & cTB.Name, cTB.BottomRightCell.Offset(1,0).Address
            ' cTB.BottomRightCell.Offset(1, 0).Value = cTB.Object.Text
            ' cTB.Delete
        End If

    Next cTB
    
End Sub

 

[willow-mouse]

Please test on a copy first !!! Might require some tweaking according to where the TextBox is.

I have intentionally commented the 2 lines, uncomment after checking debug.print results.

Public Sub textbox_test()

    Dim cTB As OLEObject

    For Each cTB In Worksheets("Sheet1").OLEObjects
        'Debug.Print cTB.Name, cTB.progID
        If InStr(UCase(cTB.progID), "TEXTBOX") > 0 Then
            Debug.Print "TB: " & cTB.Name, cTB.BottomRightCell.Offset(1,0).Address
            ' cTB.BottomRightCell.Offset(1, 0).Value = cTB.Object.Text
            ' cTB.Delete
        End If

    Next cTB
   
End Sub

Thank you - I've given this a shot and it's not working in the first instance but I'll give it another go tomorrow and let you know how I get on.