H
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
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