Copy range of cells and paste into a basic text box

[buske78]

I would like to copy the values in the range of cells A1:A17 into a basic text box, called Textbox 2. Here is the code I am using:

Sheet6.Shapes("Textbox 2").TextFrame.Characters.Text = Sheet6.Range("A1:A17").Value

It keeps giving me an error of: Runtime error 13. Type mismatch.

 

[JLGWhiz]

I am pretty sure you will need to concatenate those seventeen values into one string to do what you want. Without having tested this, it might work.

Sheet6.Shapes("Textbox 2").TextFrame.Characters.Text = Application.Concatenate(Sheet6.Range("A1:A17"))

 

[buske78]

JWhiz

I don't think Excel VBA uses that function (Concatenate) so I built my own with a loop.

Dim n As Integer
Dim s As String


For n = 1 To 17
    s = s & Sheet6.Cells(n, 1)
Next
Sheets("Temp Data").Range("P1") = s

 

[JLGWhiz]

JWhiz

I don't think Excel VBA uses that function (Concatenate) so I built my own with a loop.

Yep, the cells have to be entered individually instead of as a range for the CONCATENATE function. But you got the idea and came up with a solution.
Regards, JLG