How do you format text / lines in VBA?
In the code below, I take some input parameters, be it text or a cell, and concatenate them to form one string using the formatting I want. I need to make Task_Name bold and also text like "Lead:". I know that you cannot make the text of a variable bold, but how do I do that? This cell where I store the value is ultimately used in a Word merge.
I need to format part of a string. In the code below, I need to do Task_Name, "Lead", etc. Everything is bold.
Function GENERATE_STAFFING_SECTION(Task_Name, Lead_By, Members, Instructions)
Dim tmpSection As String
If Len(Task_Name > 0) And Len(Lead_By) > 0 And Len(Members) > 0 And Len(Instructions) > 0 Then
tmpSection = vbLf _
& Task_Name _
& vbLf & "Lead : " & Lead_By _
& vbLf & "Ambassadors : " & Members _
& vbLf & "Instructions : " & Instructions _
& vbLf
Else
tmpSection = ""
End If
GENERATE_STAFFING_SECTION = tmpSection
End Function
Also, I know this is not the cleanest code, so if there are any other suggestions for improving it, they are welcome.
Thank!
source to share
You cannot add anything to the row directly to make the cell bold.
After you've written a line in a cell, you need to go back and reprogram the cell. For example:
With ActiveCell.Characters(Start:=11, Length:=6).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
In this fragment, only part of the cell will be selected.
EDIT:
This code can be used to implement the above and give you what you want. This could have been written better, but should give you an idea of โโwhat you need to write:
Public Sub FormatOuput()
Dim i As Integer
'Format Task_name
i = InStr(1, ActiveCell.Text, vbLf)
MakeBold 1, i
'Format 'Lead'
MakeBold i + 1, 4
'Format 'Ambassadors'
i = InStr(i + 1, ActiveCell.Text, vbLf)
MakeBold i+1, 11
'Format 'Instructions'
i = InStr(i + 1, ActiveCell.Text, vbLf)
MakeBold i+1, 10
End Sub
Public Sub MakeBold(startPos As Integer, charCount As Integer)
With ActiveCell.Characters(start:=startPos, length:=charCount).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
End Sub
source to share