Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine cell contents into a text box
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(msoTextOrientationHo rizontal, 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine cell contents into a text box
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 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(msoTextOrientationHo rizontal, 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I combine cell contents? | Excel Discussion (Misc queries) | |||
Combining cell contents when there is content to combine | Excel Discussion (Misc queries) | |||
how to combine text into a cell??? | Excel Worksheet Functions | |||
How do I combine the contents of multiple cells in one cell? | Excel Worksheet Functions | |||
Combine "Total" and cell contents | Excel Programming |