Formatted Date in header applied with a macro

[Kay]

Hello all,

I was just looking at the post for formatting the date in a header. My
question concerns how to get the date to update without re-running the macro.
If you use the default date as added via the header footer toolbar, it
updates fine, but format(date, mmmm dd, yyyy) does not.

 

[Joel]

Maybe run the macro using one of the events like Workbook Open or Before Print.

 

[Kay]

Joel,

IF you could get me started on how to do that I would appreciate it, but, I
also want to know if my assumption is correct or did I just do something
incorrectly in writing the code? Does adding the format function to the code
cause it to become a static date?

Thanks!

 

[Joel]

Your question could be answered both ways. If you manually create an header
you can put the date into the header two ways. One by simply typing in the
date. the second method is to use the date function to add the date to the
header. Typing in the Date is static, using the function is not static.

Let me expain futher. If we were working on the spread sheet there are
three ways of entering a date from VBA code:

1) Range("A1") = "9/13/08"
2) Range("A1") = format(date,"mm/dd/yy")
3) Range("A1").Formula = "=Now()"
Range("A1").NumberFormat = "mm/dd/yy"

The 1st item is static and will never change. the second method will change
every time the macro is run. The third method will change every time the
workbook is opened.

The same will apply to the header depending on which method you use. The
qustion you asked I couldn't answer because I didn't know if you were
refering to the 2nd or 3rd method.

 

[Kay]

Joel,

thanks so much for taking the time to explain the different techniques. I
recorded the macro first using the date button, but then changed the code to
use the format(date, mmmm dd, yyyy). I was just surprised that it would not
update, the same as a regular header that uses the date button option. Can I
assume that if I used your third method Range("A1").Formula = "=Now()" that
while it would update, you might not be able to get it to format as above? I
am ever so grateful for the explanations and it is very important to me that
I understand why.

Thanks again!

 

[Joel]

You can't format the date when it is in the header. =NOW() and any formula
doesn't work with a header. The 3rd method I have two statements. The
second statement Range("A1").NumberFormat = "mm/dd/yy" formats the cell as
if you manually formatted the cell using the menu Format - Cell - Number and
chose one of the date formats.

 

[Kay]

Yes, Joel, I understand that and therefore, why I was trying to do it via
code. Thanks for all of your help. I think what I have done will work fine.
Have a great day!