Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a VBA script that I've culled from the internet. It takes the contents of a cell and puts it into the appropriate header. This works exactly how I need it.

Sub InsertHeaderFooter()
' inserts the same header/footer in all worksheets
Dim ws As Worksheet
    Application.ScreenUpdating = False
    For Each ws In ActiveWorkbook.Worksheets
        Application.StatusBar = "Changing header/footer in " & ws.Name
        With ws.PageSetup
            .LeftHeader = vbCr & vbCr & vbCr &B &9 & Range("J2").Text & vbCr & vbCr & Range("J3").Text & vbCr & Range("J4").Text & vbCr & Range("J5")
            .CenterHeader = vbCr & vbCr & vbCr & Range("J1").Text
        End With
    Next ws
    Set ws = Nothing
    Application.StatusBar = False
End Sub

The problem I have is formatting the header through the VBA script. I found the formatting documentation. I don't know where to use the &B for bold or &nn for font-size in the script. Every place I've put it, breaks the script.

share|improve this question
I think the & is being treated like a concatenation. Have you tried: vbCr & '&b' & '&9'. Double quotes might work if single ones don't – Brad Patton Mar 1 '13 at 21:29
up vote 0 down vote accepted

In your line .LeftHeader = ... you basically provided Excel with a string.

In VBA, a stringmust be quoted with " unless it is an internal VB constant (e.g. vbCr) or a variable (e.g. Range("J2").Text).

Therefore, try this line:

 .LeftHeader = vbCr & vbCr & vbCr & "&B &9" & Range("J2").Text & vbCr & vbCr & Range("J3").Text & vbCr & Range("J4").Text & vbCr & Range("J5")

This will pass Excel a string that contains the enters, values from the ranges - and the text "&B &9" which Excel will parse as Bold and something else (don't know what the &9 stands for)

share|improve this answer
Peter, thank you. You're advice solved most of my problem. I was about the pass "&B" into the string, but not the font size. I think I have to pass the font name then size in order for it to work. But, thank you again. – mcoleman Mar 4 '13 at 15:16
The &9 is being used to specify font size of the header. I learned that it must be in the beginning of the statement, like so .LeftHeader = "&9" & , anywhere else, it was choking the script. – mcoleman Mar 4 '13 at 19:58
Great - so in the end we bot learned something! Thanks! – Peter Albert Mar 4 '13 at 20:08

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.