Help with FONTS

[Guest]

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

 

[Nick Hodge]

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
(e-mail address removed)

 

[Guest]

Thanks Nick for your help.

Much appreciated

Cheers and Happy New Year

Ren

 

[Guest]

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]

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
(e-mail address removed)

 

[Guest]

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]

Ren

I fear we are at cross purposes, you are referring to ActiveX textbox
controls, I was referring to a text box (shapes object), as in all the
Examples given.

You cannot have different fonts and character attributes in a text string in
these controls.

I see no reason why you can't still collect the answers in the ActiveX type
and then display the final outcome in a Text Box, let me know. The code
below works with a Text Box called Text Box 11

Private Sub CommandButton49_Click()
Dim shp As Shape
Set shp = Me.Shapes("Text Box 11")
shp.TextFrame.Characters.Text = ""
Me.TextBox7.Value = 7
Me.TextBox8.Value = Val(Me.TextBox1.Value) + _
Val(Me.TextBox2.Value) + _
Val(Me.TextBox3.Value) + _
Val(Me.TextBox4.Value) + _
Val(Me.TextBox5.Value) + _
Val(Me.TextBox6.Value) + _
Val(Me.TextBox7.Value)
With shp.TextFrame
.Characters.Text = "You received a rating of " & _
Me.TextBox8.Value & " on this Communication competency."
.Characters(26, 2).Font.Bold = True
.Characters(36, 14).Font.Italic = True
End With
End Sub

I use the keyword 'Me' to refer to the object with the textboxes on it. (The
worksheet) and I have separated the lines with the continuation character _.
I presumed there may be more than 10 but less that 100 as any answer so have
bolded two characters, (if it's only one it bolds the space after!) and
likewise for the italics.

If you forward an email address I can mail a demo workbook to show it
working


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Guest]

Again many thanks for all your help Nick

I did the same type of project with a PDF form and I solve this problem
using spans.

But I will use your code to put my paragraphs in text boxes, what I'll do is
put in a textbox get the coordinates of the textbox and put those coordinates
in the shape textbox.

Thank you thank you

May 2005 be the best ever

Cheers

 

[Guest]

Hi Nick

Your help is most valuable and much appreciated.

My e-Mail address is:

(e-mail address removed)

What is the maximum number of characters can you put in one one these boxes?

I have a paragraph that contains 101 words and it looks like the maximum
number of characters I am able to put in is about 255 with spaces.

 

[Nick Hodge]

Ren

Just tested a shape text box and it took 32767 characters

I have sent the demo book

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)