Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Line-by-line formatting in Excel 2007 Textbox
I have code that programmatically adds a textbox, and then adds text ,
line-by-line in a loop. I want to format each line as it is entered. The problem is that previously entered formatted lines change with each new line added to the textbox. Only the last iteration through the loop retains its format. I am including the code in case someone can spot the problem. Sorry for the length... Add textbox to worksheet shtRC.Shapes.AddTextbox(1, 1, .Rows(r + 29).Top, .Columns(c + 3).Left, 340).Name = "Comments" shtRC.Shapes("comments").Select With Selection .ShapeRange.Line.Visible = msoFalse .Font.Size = 10 .Font.Name = "Arial" .HorizontalAlignment = xlJustify End With For X = 0 To 1 Add notes to textbox notes = "This is a test note..." If notes < "" Then shtRC.Shapes("Comments").Select With Selection ' Selection is "Comments" textbox If X = 0 Then Add Product Name & note to Comments testbox .Text = .Text & profile1 & Chr(10) .Text = .Text & note1 & Chr(10) Format product name portion of Comments Textbox .Characters(Start:=Len(.Text) - Len(profile1) - Len(note1) - 3, Length:=Len(profile1)).Font.FontStyle = "Bold" Format notes portion of Comments Textbox .Characters(Start:=Len(.Text) - Len(note1) - 3, Length:=Len(note1)).Font.FontStyle = "Normal" End If If X = 1 Then Add 2nd Product name & Note to Comments testbox .Text = .Text & profile2 & Chr(10) .Text = .Text & note2 & Chr(10) Format product name portion of Comments Textbox .Characters(Start:=Len(.Text) - Len(profile2) - 3, Length:=Len(profile2)).Font.FontStyle = "Bold" Format notes portion of Comments Textbox .Characters(Start:=Len(.Text) - Len(note2) - 3, Length:=Len(note2)).Font.FontStyle = "Normal" End If End With End If Next X -- Regards, Warren C |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Line-by-line formatting in Excel 2007 Textbox
As far as I know, and I am no expert, textboxes cannot have mixed formatting
like Bold on one line, Underline on the nexct line etc. It is one or the other. "Warcon" wrote: I have code that programmatically adds a textbox, and then adds text , line-by-line in a loop. I want to format each line as it is entered. The problem is that previously entered formatted lines change with each new line added to the textbox. Only the last iteration through the loop retains its format. I am including the code in case someone can spot the problem. Sorry for the length... Add textbox to worksheet shtRC.Shapes.AddTextbox(1, 1, .Rows(r + 29).Top, .Columns(c + 3).Left, 340).Name = "Comments" shtRC.Shapes("comments").Select With Selection .ShapeRange.Line.Visible = msoFalse .Font.Size = 10 .Font.Name = "Arial" .HorizontalAlignment = xlJustify End With For X = 0 To 1 Add notes to textbox notes = "This is a test note..." If notes < "" Then shtRC.Shapes("Comments").Select With Selection ' Selection is "Comments" textbox If X = 0 Then Add Product Name & note to Comments testbox .Text = .Text & profile1 & Chr(10) .Text = .Text & note1 & Chr(10) Format product name portion of Comments Textbox .Characters(Start:=Len(.Text) - Len(profile1) - Len(note1) - 3, Length:=Len(profile1)).Font.FontStyle = "Bold" Format notes portion of Comments Textbox .Characters(Start:=Len(.Text) - Len(note1) - 3, Length:=Len(note1)).Font.FontStyle = "Normal" End If If X = 1 Then Add 2nd Product name & Note to Comments testbox .Text = .Text & profile2 & Chr(10) .Text = .Text & note2 & Chr(10) Format product name portion of Comments Textbox .Characters(Start:=Len(.Text) - Len(profile2) - 3, Length:=Len(profile2)).Font.FontStyle = "Bold" Format notes portion of Comments Textbox .Characters(Start:=Len(.Text) - Len(note2) - 3, Length:=Len(note2)).Font.FontStyle = "Normal" End If End With End If Next X -- Regards, Warren C |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Line-by-line formatting in Excel 2007 Textbox
It works just fine if you only go through the loop once. The last time
through the loop is formatted as expected, but previous lines are all bolded. It seems that you can have different text formats on separate lines, but perhaps not within the looping structure that I have designed? -- Regards, Warren C "exceluserforeman" wrote: As far as I know, and I am no expert, textboxes cannot have mixed formatting like Bold on one line, Underline on the nexct line etc. It is one or the other. "Warcon" wrote: I have code that programmatically adds a textbox, and then adds text , line-by-line in a loop. I want to format each line as it is entered. The problem is that previously entered formatted lines change with each new line added to the textbox. Only the last iteration through the loop retains its format. I am including the code in case someone can spot the problem. Sorry for the length... Add textbox to worksheet shtRC.Shapes.AddTextbox(1, 1, .Rows(r + 29).Top, .Columns(c + 3).Left, 340).Name = "Comments" shtRC.Shapes("comments").Select With Selection .ShapeRange.Line.Visible = msoFalse .Font.Size = 10 .Font.Name = "Arial" .HorizontalAlignment = xlJustify End With For X = 0 To 1 Add notes to textbox notes = "This is a test note..." If notes < "" Then shtRC.Shapes("Comments").Select With Selection ' Selection is "Comments" textbox If X = 0 Then Add Product Name & note to Comments testbox .Text = .Text & profile1 & Chr(10) .Text = .Text & note1 & Chr(10) Format product name portion of Comments Textbox .Characters(Start:=Len(.Text) - Len(profile1) - Len(note1) - 3, Length:=Len(profile1)).Font.FontStyle = "Bold" Format notes portion of Comments Textbox .Characters(Start:=Len(.Text) - Len(note1) - 3, Length:=Len(note1)).Font.FontStyle = "Normal" End If If X = 1 Then Add 2nd Product name & Note to Comments testbox .Text = .Text & profile2 & Chr(10) .Text = .Text & note2 & Chr(10) Format product name portion of Comments Textbox .Characters(Start:=Len(.Text) - Len(profile2) - 3, Length:=Len(profile2)).Font.FontStyle = "Bold" Format notes portion of Comments Textbox .Characters(Start:=Len(.Text) - Len(note2) - 3, Length:=Len(note2)).Font.FontStyle = "Normal" End If End With End If Next X -- Regards, Warren C |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Line-by-line formatting in Excel 2007 Textbox
I forgot to mention that this works fine in Excel 2003. Something must have
changed? -- Regards, Warren C "exceluserforeman" wrote: As far as I know, and I am no expert, textboxes cannot have mixed formatting like Bold on one line, Underline on the nexct line etc. It is one or the other. "Warcon" wrote: I have code that programmatically adds a textbox, and then adds text , line-by-line in a loop. I want to format each line as it is entered. The problem is that previously entered formatted lines change with each new line added to the textbox. Only the last iteration through the loop retains its format. I am including the code in case someone can spot the problem. Sorry for the length... Add textbox to worksheet shtRC.Shapes.AddTextbox(1, 1, .Rows(r + 29).Top, .Columns(c + 3).Left, 340).Name = "Comments" shtRC.Shapes("comments").Select With Selection .ShapeRange.Line.Visible = msoFalse .Font.Size = 10 .Font.Name = "Arial" .HorizontalAlignment = xlJustify End With For X = 0 To 1 Add notes to textbox notes = "This is a test note..." If notes < "" Then shtRC.Shapes("Comments").Select With Selection ' Selection is "Comments" textbox If X = 0 Then Add Product Name & note to Comments testbox .Text = .Text & profile1 & Chr(10) .Text = .Text & note1 & Chr(10) Format product name portion of Comments Textbox .Characters(Start:=Len(.Text) - Len(profile1) - Len(note1) - 3, Length:=Len(profile1)).Font.FontStyle = "Bold" Format notes portion of Comments Textbox .Characters(Start:=Len(.Text) - Len(note1) - 3, Length:=Len(note1)).Font.FontStyle = "Normal" End If If X = 1 Then Add 2nd Product name & Note to Comments testbox .Text = .Text & profile2 & Chr(10) .Text = .Text & note2 & Chr(10) Format product name portion of Comments Textbox .Characters(Start:=Len(.Text) - Len(profile2) - 3, Length:=Len(profile2)).Font.FontStyle = "Bold" Format notes portion of Comments Textbox .Characters(Start:=Len(.Text) - Len(note2) - 3, Length:=Len(note2)).Font.FontStyle = "Normal" End If End With End If Next X -- Regards, Warren C |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Challenge - Excel Line Feed Character CHR(10) - How to Delete and keep the text formatting without going ro single line in a cell ? | Excel Worksheet Functions | |||
Goal line or target line to Excell 2007 Chart | Charts and Charting in Excel | |||
change line formatting in line chart - Excel 2007 | Charts and Charting in Excel | |||
Excel 2007 Line Graph, Data Table, Number Formatting | Charts and Charting in Excel | |||
Textbox-read text line by line | Excel Programming |