ExcelBanter - Help with FONTS

Hi Nick

I really appreciate your patients

I tried to implement the new code into mind and I still can get it to work.

Here is a sample of the code:

Private Sub CommandButton49_Click()
Dim shp As Shape
Dim wks As Worksheet
Sheet1.TextBox7.Value = 7
Sheet1.TextBox8.Value = Val(Sheet1.TextBox1.Value) +
Val(Sheet1.TextBox2.Value) + Val(Sheet1.TextBox3.Value) +
Val(Sheet1.TextBox4.Value) + Val(Sheet1.TextBox5.Value) +
Val(Sheet1.TextBox6.Value) + Val(Sheet1.TextBox7.Value)
Set wks = Worksheets("Sheet1")
Set shp = wks.Shapes("TextBox9")
With shp.TextFrame
.Characters.Text = "You received a rating of 7 on this Communication
competency."
.Characters(26, 1).Font.Bold = True
.Characters(36, 13).Font.Italic = True
End With
End Sub

The error message is: Object doesn't support this property or method.

Do you think that this would work using spans?

Thanks a million

Cheers

Ren

"Nick Hodge" wrote:

Ren

I hope I understand but the code below either (code1) iterates all the
shapes, (code2) changes a specific shape. (You can identify them by
selecting and looking in the name box

Sub IterateAllShapes()
Dim wks As Worksheet
Dim shp As Shape
Set wks = Worksheets("Sheet1")
'Iterate through entire shapes collection
For Each shp In wks.Shapes
With shp.TextFrame
.Characters.Text = "Here is my paragraph"
.Characters(6, 2).Font.Bold = True
.Characters(12, 9).Font.Color = RGB(255, 0, 0)
End With
Next shp
End Sub

Sub ChangeSpecificBox()
Dim wks As Worksheet
Dim shp As Shape
Set wks = Worksheets("Sheet1")
'Assign variable to specific textbox
Set shp = wks.Shapes("Text Box 1")
With shp.TextFrame
.Characters.Text = "Here is my paragraph"
.Characters(6, 2).Font.Bold = True
.Characters(12, 9).Font.Color = RGB(255, 0, 0)
End With
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS

"Ren" wrote in message
...
Hi Nick

I hope you get this message.

I tried your code and it works very well, I tried to impliment the code in
a
command button and put the text directly into a textbox without having to
add
a textbox and I'm having some difficulties.

Your code resembles the one that I used in the PDF version of this form.

Is it posible to implement part of your code to a command button fonction
and to put the text in question into a textbox directly without having to
add
a textbox?

Thanks again for your help

Ren

"Nick Hodge" wrote:

Ren

The code below adds a textbox to the upper left of the worksheet and
makes
the word 'is' bold and the word 'paragraph' red

Sub test()
Dim wks As Worksheet
Dim shp As Shape
Set wks = Worksheets("Sheet1")
Set shp = wks.Shapes.AddTextbox(msoTextOrientationHorizontal , 1, 1, 100,
12)
With shp.TextFrame
.Characters.Text = "Here is my paragraph"
.Characters(6, 2).Font.Bold = True
.Characters(12, 9).Font.Color = RGB(255, 0, 0)
End With
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS

"Ren" wrote in message
...
Merry Christmas to all

I'm working on a new project where I'm asked to automate a report.

The evaluators will be able to click on a number that will generate a
short
paragraph in a textbox.

The paragraph has certain words that are bold and some words are in
italics.

I know are to enter the paragraph into the textbox, but my question is
how
to make certain words bold and italic.

Thanks to all for your help.

Cheers

Ren