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

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

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

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