How do you format text/strings in VBA?

Question

In the code below, I take some input parameters, either text or a cell, and combine them to form one string using the formatting I need. I need to make Task_Name bold, as well as text like "Lead :". I know you cannot make text in a variable bold, but how do I go about this? This cell I'm storing the value in is eventually used in a Word mail merge.

I need to format part of a string. In the code below, I need to make Task_Name, "Lead : ", etc. all 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 it's not the cleanest code, so if there are any other suggestions for improving it, they are most welcome.

Thanks!

Sk93 · Accepted Answer · 2009-09-10 16:16:32Z

5

You can't add anything to the string directly to make the cell have bold characters.

Once you've written the string out to the cell, you'll need to go back and reprocess 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

This snippet will set only a portion of the cell to bold.

EDIT:

This code could be used to implement the above and give you what you want. It could be written better, but should give you an idea of what you've got 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

edited Sep 10, 2009 at 16:16

answered Sep 10, 2009 at 15:42

Sk93

3,7185 gold badges42 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

achinda99 Over a year ago

So if in the spreadsheet, I set a cell to =GENERATE_STAFFING_SECTION(.....), how do I go about formatting its text based on the contents?

RBarryYoung Over a year ago

+1: Very cool with the partial display formatting Sk93. I've always wondered how the partials were done.

Sk93 Over a year ago

without writing the full code, you know the "task name" will start at 1 and roll on until the first line feed, so use "Instr" to find the first linefeed char. you'll now have your start and end points of taskname to use in the above code. Next, you know that "Lead:" follows on from the linefeed you already have, and it's five chars long... theres you're next co-ords. And so on :)

achinda99 Over a year ago

Right, but how do you set the ActiveCell's value? ActiveCell.Text = "something" and ActiveCell.Value = "sdd" don't work

Sk93 Over a year ago

ActiveCell.Characters.Insert <STRING>

|

Collectives™ on Stack Overflow

How do you format text/strings in VBA?

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related