CODEWITHSUNDEEP
excelvba
Jorge Molano
Jorge Molano

Reputation: 59

Copy entire column into a textbox

I need to copy all the Column L of my Sheet1 inside of a TextBox on Sheet4.
I try to do with this:

Sub ToTB()
    Sheets("Sheet4").TextBoxes("TextBox 2").Text = Sheets("Sheet1").Range("L:L").Text
End Sub

But then appear this message:

Application-defined or object-defined error

Any suggestions??

Upvotes: 3

Views: 538

Answers (2)

L42
L42

Reputation: 19727

Here's something a little bit direct. If you already have the TextBox and just needs to add the values from a range of cell:

Sub Test()
    Dim srng As Range
    Dim sWs As Worksheet: Set sWs = Sheets("Sheet1")
    Set srng = sWs.Range("L1", sWs.Range("L" & sWs.Rows.Count).End(xlUp))
    With Sheets("Sheet4").Shapes("Textbox 2").OLEFormat.Object
        .Text = Join(Application.Transpose(srng), vbCrLf)
    End With
End Sub

or if you wish it added as what Gary posted:

Sub Test()
    Dim sWs As Worksheet, dWs As Worksheet
    Set sWs = Sheets("Sheet1"): Set dWs = Sheets("Sheet4")

    Dim dRng As Range, sRng As Range
    Set dRng = dWs.Range("A1:A10") 'change to suit
    Set sRng = sWs.Range("L1", sWs.Range("L" & sWs.Rows.Count).End(xlUp))

    With dWs.Shapes.AddTextbox(msoTextOrientationHorizontal, _
        dRng.Left, dRng.Top, dRng.Width, dRng.Height).OLEFormat.Object
        .Text = Join(Application.Transpose(sRng), vbCrLf)
    End With
End Sub

Upvotes: 5

Gary's Student
Gary's Student

Reputation: 96753

With this in Sheet4:

enter image description here

Running this macro:

Sub CoverRange()
    msg = ""
    Sheets("Sheet4").Activate
    For Each r In Intersect(ActiveSheet.UsedRange, Range("L:L"))
        msg = msg & vbCrLf & r.Text
    Next r

    Sheets("Sheet1").Activate
    Dim L As Long, T As Long, W As Long, H As Long
    Set r = Range("B9:B20")
    L = r.Left
    T = r.Top
    W = r.Width
    H = r.Height
    With ActiveSheet.Shapes
        .AddTextbox(msoTextOrientationHorizontal, L, T, W, H).Select
    End With
    Selection.Characters.Text = msg
End Sub

Will produce this in Sheet1

enter image description here

Upvotes: 2

Related Questions