How to control font size in excel custom header in VBA

I have a macro that I use to enter a date from a cell in the worksheet to the left header in the custom header section.

This is the code and it works almost as I want:

Please Login or Register  to view this content.

The problem is the date that I plug in to cell CA3 does appear in the header but the font size is 10 and I want to change it to 18 - I also want to make it bold. I've tried various
options which I've been finding on google but none have worked. Here is some code I used which doesn't give me what I want.

Please Login or Register  to view this content.

Thanks for any help you can give with proper syntax

Try

ActiveSheet.PageSetup.LeftHeader = "&""-,Bold""&18" & Range("A3")

One way to determine the code is to "record macro".

Hi John,

Thanks for your reply. I did try the code you sent and it worked except the date appeared as "/24/2016" even though cell CA3 contains "06/24/2016".

Also the bold 18 was about 2.5 inches high and bled over into the print of the first several rows and I had to reduce it and put it as Bold""&1"

Here is the code I have at present:

Please Login or Register  to view this content.

I'm confused as to why the font size is so different.

By the way I tried recording a macro but got similar results.

Thanks again for your help.

Thanks for your help.

I made the change per your code but there are 2 problems.

1) The font size is an issue - when I put &18 it appears about 4 times as large and covers about 4 rows of the printed document. When I put &1 it looks approximately the right size - very confusing.
2) The value I have in cell CA3 is: 06/24/2016 however what appears in the LeftHeader is: /24/2016 - the month does not appear.

Here is the code I ran and it is the only code line in my macro:

Please Login or Register  to view this content.

*** FYI - I know I can use the date icon manually in the header but I'm going to use this routine to change multiple worksheets and prefer not to have to change them all manually.

Could this be a problem in excel 2016?


Thanks again

shg,

Thanks for your help - that did it and gave exactly the right results. Good Job!!!

I will close the thread now.