Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interesting problem with VBA generated textbox (or textframe of shape) length
Here is some basic vba code for automating an Excel sheet from a PPT app. I
want to add a rectangle with some text to sheet 1. I know (because I control the file creation) that this will be the 6th shape on sheet 1. So I used this code: oExcel.Sheets(1).Shapes _ .AddShape(1, y + 10, (2 * x) + 20, y, x) _ .TextFrame.Characters.Text = _ strText Works great, up to a point. If strText gets to about 200-250 characters, it blanks out the text display in the box, nada, nothing, gone, pfft. I tried changing the shape to a textbox (AddTextBox), with no change in behavior. Since I know the text will be between 10 and 400 characters, depending on the circumstances, I am forced to run with something like this: oExcel.Sheets(1).Shapes _ .AddShape(1, y + 10, (2 * x) + 20, y, x) _ .TextFrame.Characters.Text = _ Left(strText, 200) If Len(strText) 200 Then _ oExcel.Sheets(1).Shapes(6) _ .TextFrame.Characters(200) _ .Insert Right(strText, _ Len(strText) - 200) I just wondering if I am missing something silly, or is this a standard Excel design "feature"? Perhaps this is a cross app VBA inconvenience, but it kept me up half the night, banging my head against the monitor, trying to figure out where strText went half the time. The code now works, so this isn't a crisis anymore, but I would love to find out why it does this. -- Bill |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interesting problem with VBA generated textbox (or textframe of shape) length
Bill,
No solution, but I see it happens when Len becomes bigger than 255. Excel is unable to display text in cells formatted as Text at this length also, so I would guess it has something to do with how Excel handles strings. NickHK "Bill Dilworth" wrote in message ... Here is some basic vba code for automating an Excel sheet from a PPT app. I want to add a rectangle with some text to sheet 1. I know (because I control the file creation) that this will be the 6th shape on sheet 1. So I used this code: oExcel.Sheets(1).Shapes _ .AddShape(1, y + 10, (2 * x) + 20, y, x) _ .TextFrame.Characters.Text = _ strText Works great, up to a point. If strText gets to about 200-250 characters, it blanks out the text display in the box, nada, nothing, gone, pfft. I tried changing the shape to a textbox (AddTextBox), with no change in behavior. Since I know the text will be between 10 and 400 characters, depending on the circumstances, I am forced to run with something like this: oExcel.Sheets(1).Shapes _ .AddShape(1, y + 10, (2 * x) + 20, y, x) _ .TextFrame.Characters.Text = _ Left(strText, 200) If Len(strText) 200 Then _ oExcel.Sheets(1).Shapes(6) _ .TextFrame.Characters(200) _ .Insert Right(strText, _ Len(strText) - 200) I just wondering if I am missing something silly, or is this a standard Excel design "feature"? Perhaps this is a cross app VBA inconvenience, but it kept me up half the night, banging my head against the monitor, trying to figure out where strText went half the time. The code now works, so this isn't a crisis anymore, but I would love to find out why it does this. -- Bill |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interesting problem with VBA generated textbox (or textframe of shape) length
Hi Bill,
If your string is over 255 need to insert characters into the textframe in chunks of less than 255 at a time. Sub test() Dim j As Long Dim sPart$, sText$ Dim shp As Shape sPart = Application.Rept("x", 37) & "40 " For j = 1 To 25 sText = sText & sPart Next MsgBox Len(sText) Set shp = ActiveSheet.Shapes.AddTextbox(1, 99#, 9#, 450#, 210#) With shp j = 1 Do While j < Len(sText) sPart = VBA.Strings.Mid(sText, j, 250) .TextFrame.Characters(j).Insert String:=sPart j = j + 250 Loop End With End Sub I'd be interested to know how much text others can put in a textbox. I appear to get inconsistent results, at first limited to 1024 then later, for no apparent reason, significantly more than that. Increase the loop "For j = 1 To 25" and the textbox size to accommodate. Regards, Peter T "Bill Dilworth" wrote in message ... Here is some basic vba code for automating an Excel sheet from a PPT app. I want to add a rectangle with some text to sheet 1. I know (because I control the file creation) that this will be the 6th shape on sheet 1. So I used this code: oExcel.Sheets(1).Shapes _ .AddShape(1, y + 10, (2 * x) + 20, y, x) _ .TextFrame.Characters.Text = _ strText Works great, up to a point. If strText gets to about 200-250 characters, it blanks out the text display in the box, nada, nothing, gone, pfft. I tried changing the shape to a textbox (AddTextBox), with no change in behavior. Since I know the text will be between 10 and 400 characters, depending on the circumstances, I am forced to run with something like this: oExcel.Sheets(1).Shapes _ .AddShape(1, y + 10, (2 * x) + 20, y, x) _ .TextFrame.Characters.Text = _ Left(strText, 200) If Len(strText) 200 Then _ oExcel.Sheets(1).Shapes(6) _ .TextFrame.Characters(200) _ .Insert Right(strText, _ Len(strText) - 200) I just wondering if I am missing something silly, or is this a standard Excel design "feature"? Perhaps this is a cross app VBA inconvenience, but it kept me up half the night, banging my head against the monitor, trying to figure out where strText went half the time. The code now works, so this isn't a crisis anymore, but I would love to find out why it does this. -- Bill |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interesting problem with VBA generated textbox (or textframeof shape) length
Bill, thank you for posting this problem. I had just
encountered bizarre behavior in a textbox, similarly populated from VBA. I was displaying data validation messages. It worked fine for all but one column of cells. I could see that the lenght of the title was being used, the code stepped through correctly, but the validation message of the previously-entered cell was being displayed instead!! You have solved my problem, and a LOT of potential tears that were just getting ready to flow!! Kate Bill Dilworth wrote: Here is some basic vba code for automating an Excel sheet from a PPT app. I want to add a rectangle with some text to sheet 1. I know (because I control the file creation) that this will be the 6th shape on sheet 1. So I used this code: oExcel.Sheets(1).Shapes _ .AddShape(1, y + 10, (2 * x) + 20, y, x) _ .TextFrame.Characters.Text = _ strText Works great, up to a point. If strText gets to about 200-250 characters, it blanks out the text display in the box, nada, nothing, gone, pfft. I tried changing the shape to a textbox (AddTextBox), with no change in behavior. Since I know the text will be between 10 and 400 characters, depending on the circumstances, I am forced to run with something like this: oExcel.Sheets(1).Shapes _ .AddShape(1, y + 10, (2 * x) + 20, y, x) _ .TextFrame.Characters.Text = _ Left(strText, 200) If Len(strText) 200 Then _ oExcel.Sheets(1).Shapes(6) _ .TextFrame.Characters(200) _ .Insert Right(strText, _ Len(strText) - 200) I just wondering if I am missing something silly, or is this a standard Excel design "feature"? Perhaps this is a cross app VBA inconvenience, but it kept me up half the night, banging my head against the monitor, trying to figure out where strText went half the time. The code now works, so this isn't a crisis anymore, but I would love to find out why it does this. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cutting and Pasting Cells into a shape or textbox | Excel Discussion (Misc queries) | |||
Interesting problem! | Excel Programming | |||
Editting textbox value that is grouped with a shape | Excel Programming | |||
Hyperlink within the TextFrame of Shape Object..? | Excel Programming |