VBA>custom footer>cell ref + font size

[Guest]

I checked some earlier postings, and thought I found some solutions, but I
must still be missing something, because I am getting some funky results.

I am attempting to reference a cell value in my footer, which works fine,
however, when I try to also set the font name (type) and size, the footer
value prints HUGE on my page, like one character across the entire page (at
least that what seems to be happening). I can't seem to accomplish both
tasks at the same time, but am fairly certain there should be a way.

I have tried a number of various lines of code, which are below, and figure
I'm just slightly off with my syntax. Any assistance anyone can provide
would be appreciated. I have commented them all out while I was testing each
one. The first line works, but does not give me the font name/size I want.
The next two were meager attempts at an alternate solution, the fourth one is
the one giving me the crazy output, and the fifth one gives me the footer,
but does NOT seem to give me the font name/size.

With ActiveSheet.PageSetup
'.LeftFooter = Range("H1").Value
'.LeftFooter.Font.Name = Garamond
'.LeftFooter.Font.Size = 9
'.LeftFooter = "&9" & Range("H1").Value
'.LeftFooter = Range("H1").Value & "&""Garamond,Regular""&9"
End With

 

[Dave Peterson]

What's in Range("H1")?

If it starts with a number, it could be messing up this line:

..LeftFooter = "&9" & Range("H1").Value

If H1 contained "7 will do it", it would look like this to excel:

..LeftFooter = "&97 will do it"

And you've got a giant font size.

I add an extra space:
..LeftFooter = "&9 " & Range("H1").Value

 

[LtLeary]

Try setting the font and size before you place your value like so...

With ActiveSheet.PageSetup
.LeftFooter = "&Garamond &09"
.LeftFooter = Range("H1").Value
End With

This ensures the font name and site are set before adding any text.

LT

 

[LtLeary]

I forgot the formatting earlier...
This one does work...
With ActiveSheet.PageSetup
.LeftFooter = "&""Garamond"" &09" & Range("H1").Value
End With

I forgot about the quotes around the Fontname

Lt

 

[Guest]

Thank you, LT and Dave.

Yes, I apologize for not mentioning the content of H1; it did start off with
a number (it is a formula with text). I could not get LT's solution below to
work for me (the font name/size did not seem to set), but Dave's solution
worked. Both of the lines below worked for me (putting the space after the
'&9,' and separating out).

..LeftFooter = "&""Garamond,Regular""&9 " & Range("H1").Value
..LeftFooter = "&""Garamond,Regular""&9" & " " & Range("H1").Value

Thank you.

Regards,

Adam.