vbabeginner92
New Member
- Joined
- Jul 25, 2018
- Messages
- 13
I am trying to use VBA to insert a yellow textbox that has Procedures, Tickmarks, and Conclusion on separate lines. I have been able to do so.
Now I am trying get font to change after the headings. Below is the an example and code for trying to get the font unbolded and black after "Procedures:". So for below "Procedures:" will be red ad bolded. Everything after it will be black an unbolded.
For "{a} -", text will be red and bolded and after hyphen, text is black and unbolded.
Example
Procedures: All text after the semicolon is now unbolded and black.
Tickmarks:
{a} – all the text after is unbolded and black
Sub PBC_and_Textbox()
Dim PBC As String
PBC = "PBC"
ActiveCell.Value = PBC
With ActiveCell.Font
.Bold = True
.Color = vbRed
End With
ActiveCell.Interior.Color = RGB(255, 255, 0)
ActiveCell.HorizontalAlignment = xlCenter
'This add PBC to the activecell
'============================================================
Dim Textbox As String
Set s = ActiveSheet.Shapes.AddTextBox(msoTextOrientationHorizontal, 500, 20, _
400, 145)
With s.Fill
.ForeColor.RGB = RGB(255, 255, 153)
End With
With s.TextFrame
.Characters.Font.Color = RGB(255, 0, 0)
.Characters.Font.Bold = True
.Characters.Text = "Procedures: Insert Procedures" & vbNewLine & vbNewLine & "Tickmarks:" & vbNewLine & vbNewLine & "{a} - " & vbNewLine & "{b} - " & vbNewLine & "{c} - " & vbNewLine & vbNewLine & "Conclusion:"
End With
With s.TextFrame.TextRange.Find
.Text = "Insert Procedures"
.Replacement.Text = "Insert procedures"
.MatchWildCards = True
.clearformatting
.Replacement.clearformatting
With .Replacement.Font
.Bold = False
.Color = vbBlack
End With
.Execute Replace:=wdReplaceOne
End With
End Sub
Now I am trying get font to change after the headings. Below is the an example and code for trying to get the font unbolded and black after "Procedures:". So for below "Procedures:" will be red ad bolded. Everything after it will be black an unbolded.
For "{a} -", text will be red and bolded and after hyphen, text is black and unbolded.
Example
Procedures: All text after the semicolon is now unbolded and black.
Tickmarks:
{a} – all the text after is unbolded and black
Sub PBC_and_Textbox()
Dim PBC As String
PBC = "PBC"
ActiveCell.Value = PBC
With ActiveCell.Font
.Bold = True
.Color = vbRed
End With
ActiveCell.Interior.Color = RGB(255, 255, 0)
ActiveCell.HorizontalAlignment = xlCenter
'This add PBC to the activecell
'============================================================
Dim Textbox As String
Set s = ActiveSheet.Shapes.AddTextBox(msoTextOrientationHorizontal, 500, 20, _
400, 145)
With s.Fill
.ForeColor.RGB = RGB(255, 255, 153)
End With
With s.TextFrame
.Characters.Font.Color = RGB(255, 0, 0)
.Characters.Font.Bold = True
.Characters.Text = "Procedures: Insert Procedures" & vbNewLine & vbNewLine & "Tickmarks:" & vbNewLine & vbNewLine & "{a} - " & vbNewLine & "{b} - " & vbNewLine & "{c} - " & vbNewLine & vbNewLine & "Conclusion:"
End With
With s.TextFrame.TextRange.Find
.Text = "Insert Procedures"
.Replacement.Text = "Insert procedures"
.MatchWildCards = True
.clearformatting
.Replacement.clearformatting
With .Replacement.Font
.Bold = False
.Color = vbBlack
End With
.Execute Replace:=wdReplaceOne
End With
End Sub