Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatted Date in header applied with a macro
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatted Date in header applied with a macro
Maybe run the macro using one of the events like Workbook Open or Before Print.
"Kay" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatted Date in header applied with a macro
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" wrote: Maybe run the macro using one of the events like Workbook Open or Before Print. "Kay" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatted Date in header applied with a macro
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" wrote: 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" wrote: Maybe run the macro using one of the events like Workbook Open or Before Print. "Kay" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatted Date in header applied with a macro
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! "Kay" wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatted Date in header applied with a macro
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" wrote: 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! "Kay" wrote: 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatted Date in header applied with a macro
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! "Kay" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting date from Date and Time formatted cell | Excel Discussion (Misc queries) | |||
changing date formatted as general number into date format | Excel Programming | |||
Can a header be applied once to a workbook & print on each page? | Excel Worksheet Functions | |||
how to write macro after autofilter is applied | Excel Worksheet Functions | |||
In Excel, why sort function fails when applied to a list of date? | Excel Discussion (Misc queries) |