Combine cell contents into a text box

[hinick]

I have created an excel macro that takes the current selection of cell
and combines them using a string that I just add the value of each cell
to then I create a text box, and set the text box value to the string.
It seems to work great, until I select many cells to combine - I think
I am running into the 255 character limit on this one but don't know
how to get around it. My understanding is that strings can old a very
large number of characters so I don't know why I am running into this
issue. Any help would be much appreciated.

Here is my code:

Public Sub Combine_cells()

Dim r As Range
Set r = Selection

Dim c As Range

Dim $thestring
thestring = ""

'iterate through the selected cells to make the string
For Each c In r.Cells

If thestring = vbNullString Then
thestring = CStr(c.Value)
Else
thestring = thestring + " " + CStr(c.Value)
End If

Next c


'create the text box and put the string in it.
ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 386.25,
134.25, _
288#, 90.75).Select

With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 43
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.SchemeColor = 64
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
Selection.Text = thestring

End Sub

 

[Jim Cone]

Couple of things I see...

Dim $thestring
should be...
Dim theString$

thestring = thestring + " " + CStr(c.Value)
should be...
theString = theString & " " & CStr(c.Value)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



<[email protected]>
wrote in message
I have created an excel macro that takes the current selection of cell
and combines them using a string that I just add the value of each cell
to then I create a text box, and set the text box value to the string.
It seems to work great, until I select many cells to combine - I think
I am running into the 255 character limit on this one but don't know
how to get around it. My understanding is that strings can old a very
large number of characters so I don't know why I am running into this
issue. Any help would be much appreciated.

Here is my code:

Public Sub Combine_cells()

Dim r As Range
Set r = Selection

Dim c As Range

Dim $thestring
thestring = ""

'iterate through the selected cells to make the string
For Each c In r.Cells

If thestring = vbNullString Then
thestring = CStr(c.Value)
Else
thestring = thestring + " " + CStr(c.Value)
End If

Next c


'create the text box and put the string in it.
ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 386.25,
134.25, _
288#, 90.75).Select

With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 43
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.SchemeColor = 64
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
Selection.Text = thestring

End Sub