How to format existing text in headers/footers?

[hanspnw]

Hi there. First, my apologies if this has been covered and I failed to see it when I searched; if so, I'd be grateful for a link.

Is there a simple way to apply formatting to existing text in the LeftHeader? The text will have been manually typed and (sometimes) formatted; it is not added via VBA from elsewhere in the document outside of the header, so I can't add formatting attributes first. Also, the text and formatting are going to be different from one sheet to the next, so I can't repeatedly look for the same content. Ultimately, the macro will be used to format thousands of headers in hundreds of workbooks.

When I record the process, I see that the macro picks up precisely the content in the header, including the formatting attributes I apply, as follows, for example:

.LeftHeader = "&""Arial,Bold""&12TEST LEFTHEADER TEXT"

But, as noted, the text and formatting are going to be different from one sheet to the next, and I don't see a way to programmatically apply the formatting irrespective of content.

Anyway, I'm deep down a rabbit hole now, trying to use regex to search formatting in the LeftHeader string and replace it with the proper formatting. But after figuring this out for three attributes, I've realized that there are five attributes that might precede the string (font name, font size, emphasis formatting [bold/italic], underling, and font color], and sometimes there are none! So now it appears I have to also test to see if they exist, remove them, and replace with the formatting attributes I dictate. It all seems completely overwrought at this point.

On the off chance that the way I'm trying to do this is, in fact, the way that it must be done, here is my incomplete code:

Sub LeftHeaderFormatText()


'Get pre-existing text from the LeftHeader and apply new formatting attributes.


    Dim strOldText As String
    Dim strNewFont As String: strNewFont = "TEST-strNewFont" ' I actually need this to be "Segoe UI,Bold"&12" but I haven't figured out how to type it yet...
    Dim strNewText As String
        
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
            
    'If there were a way to strip out the formatting info I get from the LeftHeader string, then I wouldn'be be trying to use regex.
    'Earlier I'd figured out a way to search for font name, emphasis formatting, and size, but I did that not realizing that there
    'are five possible attributes...
    With regex
           
        'Find the formatting attributes at the beginning of the string.
               
        .Pattern = "
" 'Need to figure out how to anticipate the following potential string variants and combinations:
        
            'There are five font attributes that could show up prior to the text in a string:
            
            '1. Font name applied (Excel automatically includes emphasis type formatting if none chosen, i.e., adds "Regular")
            '2. Text size
            '3. Emphasis type, i.e., bold, italic, or both
            '4. Underline
            '5. Text color
        
            'Note: If no formatting attributes are applied by the user, then the string comprises just the text.
        
            'For reference, here's an example with all five attributes: &"Arial,Bold"&12&U&KC00000Test Text
            
    End With
         
    strOldText = Sheets("Sheet1").PageSetup.LeftHeader
                
    'Execute the Replace Method
    strNewText = regex.Replace(strOldText, strNewFont)


    'Debug.Print regex.Replace(strOldText, strNewFont)


    Debug.Print strNewText
        
    'The replacement is commented out until I can figure out the rest of the macro...:
    'Sheets("Sheet1").PageSetup.LeftHeader = strNewText
    
End Sub

It's undoubtedly obvious that I'm in the dark here, so thank you for help.

 

[hanspnw]

Perhaps posting a question on a Sunday night was ill-advised.

A thought I had this morning: rather than trying to "sniff" out any existing formatting in the LeftHeader string, is there a way to just strip it all out before I apply the formatting I want?

Thanks.