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

disclaimer: it's been few years since worked (a lot) vba, might issue caused confusing myself different language deal with.

so; i've got workbook (excel 2010) multiple sheets (20+), of whom multi-page. make things easier when printing everything, want add sheet-specific headers amongst others name of sheet, number of pages , on.

i've written tiny function should (in theory) me iterating on sheets setting header. however, reason works if header empty; if has value refuses overwrite unknown reason.

dim sheetindex, numsheets integer sheetindex = 1 numsheets = sheets.count  ' loop through each sheet, don't set of them active while sheetindex <= numsheets     dim sheetname, role, labeltext string     sheetname = sheets(sheetindex).name     role = getrole(mode)      labeltext = "some text - " & sheetname & " - " & role      sheets(sheetindex).pagesetup         .leftheader = labeltext         .centerheader = ""         .rightheader = "page &[page] / &[pages]"         .leftfooter = "&[date] - &[time]"         .centerfooter = ""         .rightfooter = "page &p / &n"     end      sheetindex = sheetindex + 1 wend 

i found solution seems work replacing text. whatever reason, in macro, need include header/footer format character codes in order work properly.

this code worked replace existing header text new information:

sub test()     dim sht 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 &l, &c, , &r codes before text, not work.

some interesting behavior found if use following code:

.centerheader = "&l text" 

it put some text in leftheader position. led me believe formatting codes important.