Excel headers/footers won't change via VBA unless blank

Question

Disclaimer: It's been a few years since I worked (a lot) with VBA, so this might be an issue caused by confusing myself with what is essentially a very different language from what I usually deal with.

So; I've got a workbook (Excel 2010) with multiple sheets (20+), most of whom are multi-page. To make things easier when printing everything, I want to add some sheet-specific headers with amongst others the name of the sheet, number of pages and so on.

I've written a tiny function that should (in theory) do this for me by iterating over all the sheets setting the header. However, for some reason it only works if the header is empty; if it already has a value it refuses to overwrite for some unknown reason.

Dim sheetIndex, numsheets As Integer
sheetIndex = 1
numsheets = Sheets.Count

' Loop through each sheet, but don't set any of them to active
While sheetIndex <= numsheets
    Dim sheetname, role, labeltext As String
    sheetname = Sheets(sheetIndex).name
    role = GetRole(mode) 
    labeltext = "Some text - " & sheetname & " - " & role

    With Sheets(sheetIndex).PageSetup
        .LeftHeader = labeltext
        .CenterHeader = ""
        .RightHeader = "Page &[Page] / &[Pages]"
        .LeftFooter = "&[Date] - &[Time]"
        .CenterFooter = ""
        .RightFooter = "Page &P / &N"
    End With

    sheetIndex = sheetIndex + 1
Wend

Answer 1

I found a solution that seems to work for replacing text. For whatever reason, in the macro, you need to include the header/footer format character codes in order for it to work properly.

This code worked to replace existing header text with new information:

Sub test()
    Dim sht As Worksheet
    Set sht = Worksheets(1)
    sht.PageSetup.LeftHeader = "&L left text"
    sht.PageSetup.CenterHeader = "&C center Text"
    sht.PageSetup.RightHeader = "&R right text"
End Sub

Without the &L, &C, and &R codes before the text, I could not get it to work.

Some interesting behavior I found is that if you use the following code:

.CenterHeader = "&L some text"

it will actually put the some text in the LeftHeader position. This led me to believe that the formatting codes were very important.