Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with FONTS
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with FONTS
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with FONTS
Thanks Nick for your help.
Much appreciated Cheers and Happy New Year 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with FONTS
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with FONTS
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 HIS "Ren" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with FONTS
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 "Nick Hodge" wrote: 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 HIS "Ren" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with FONTS
Hi Nick
Your help is most valuable and much appreciated. My e-Mail address is: 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" wrote: 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 HIS "Ren" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with FONTS
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 HIS "Ren" wrote in message ... Hi Nick Your help is most valuable and much appreciated. My e-Mail address is: 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" wrote: 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 HIS "Ren" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I lengthen the drop down Fonts list to show more fonts at | Excel Discussion (Misc queries) | |||
Fonts | Excel Discussion (Misc queries) | |||
Best FOnts? | Excel Discussion (Misc queries) | |||
fonts | Excel Discussion (Misc queries) | |||
no more fonts | Excel Worksheet Functions |