Need help adding Arial Bold and font size 12 to VBA

[craigwoycheesecpa]

I have tried using the combination of "& Arial, Bold after CenterHeader =, and it does not work. I am a newbie so please be patient.

Sub AddCustomHeaderFooters()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.PageSetup.CenterHeader = Worksheets("Sheet2").Range("B1").Value
ws.PageSetup.LeftHeader = Worksheets("Sheet2").Range("B2").Value
Next ws
End Sub

 

[craigwoycheesecpa]

"&""Arial,Bold""&12 will not work before worksheets

 

[rlv01]

This is a case where you can learn a lot by using the Macro Recorder. Record yourself changing the font and and bolding (to Arial). The recorded macro will reveal the needed syntax and then you can add it to your macro. And if you cannot figure it out, then posting the recorded macro code will give us a better idea of how to help.

 

[craigwoycheesecpa]

This is a case where you can learn a lot by using the Macro Recorder. Record yourself changing the font and and bolding (to Arial). The recorded macro will reveal the needed syntax and then you can add it to your macro. And if you cannot figure it out, then posting the recorded macro code will give us a better idea of how to help.

Sub AddCustomHeaderFooters()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.PageSetup.CenterHeader = "&""Arial,Font""&12 Worksheets("Sheet2").Range("B1").Value
ws.PageSetup.LeftHeader = Worksheets("Sheet2").Range("B2").Value
Next ws
End Sub

Text is bold does not work. I think it has to do with selecting a range value.

The recorder showed the following:

CenterHeader = "&""Arial,Bold""&12South Central Arkansas Electric Cooperative, Inc."

This assumes I just name the header instead of referencing a cell in a worksheet.

 

[craigwoycheesecpa]

Sub AddCustomHeaderFooters()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.PageSetup.CenterHeader = "&""Arial,Font""&12 Worksheets("Sheet2").Range("B1").Value
ws.PageSetup.LeftHeader = Worksheets("Sheet2").Range("B2").Value
Next ws
End Sub

Text is bold does not work. I think it has to do with selecting a range value.

The recorder showed the following:

CenterHeader = "&""Arial,Bold""&12South Central Arkansas Electric Cooperative, Inc."

This assumes I just name the header instead of referencing a cell in a worksheet.

One sec...I typed font instead of bold, but I don't think it will work if I change.

 

[craigwoycheesecpa]

One sec...I typed font instead of bold, but I don't think it will work if I change.

I managed to solve the problem, but I must say that VBA can be quite finicky in my opinion.

Sub AddCustomHeaderFooters()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.PageSetup.CenterHeader = "&""Arial,Bold""&12 " & Worksheets("Sheet2").Range("B1").Value
ws.PageSetup.LeftHeader = Worksheets("Sheet2").Range("B2").Value
Next ws
End Sub

 

[rlv01]

but I must say that VBA can be quite finicky in my opinion.

Indeed it can.

Glad to hear you were able to solve the problem.