CODEWITHSUNDEEP
excelvbacharacter
vlp-1
vlp-1

Reputation: 35

excel vba Insert formatted symbol in front of text keeping original formats of pre-existing text

How do I modify the code below to insert the formatted symbol at the start instead of the end?

Sub I___TickRedAFTERText_KeepsOtherCharFormatting()

ActiveCell.Characters(ActiveCell.Characters.Count + 1, 1).Insert (" P ")

'specify location and format the new character
     With ActiveCell.Characters(ActiveCell.Characters.Count - 1, 1).Font 'second to last character in cell
        .Name = "Wingdings 2"
        .Bold = True
        .Color = -16776961
    End With
End Sub

Upvotes: 1

Views: 555

Answers (1)

Robson
Robson

Reputation: 2032

ActiveCell.Characters has two parameters: start and length

On the first line you want to insert at the beginning, with no changes to existing text/formatting. So the arguments should be 0 (i.e. the beginning) and 0 (i.e. no length, so we don't overwrite anything).

On the second line you also want to start at the beginning (so 0 again) and then format all of the characters that have been inserted (we can use the length of the text to get this).

End result:

Sub I___TickRedAFTERText_KeepsOtherCharFormatting()

    ActiveCell.Characters(0, 0).Insert(" P ")

    'specify location and format the new character
     With ActiveCell.Characters(0, Len(" P ")).Font
        .Name = "Wingdings 2"
        .Bold = True
        .Color = -16776961
    End With
    
End Sub

Output:

enter image description here

Upvotes: 2

Related Questions