Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Footer
Hello,
Is it possible to place in the footer of a spreadsheet the date that the report was last modified, rather than the date the report is printed or the current date. Thanks, |
#2
|
|||
|
|||
Footer
One way:
Put something like this in the ThisWorkbook code module: Private Sub Workbook_SheetChange( _ ByVal Sh As Object, ByVal Target As Excel.Range) Sh.PageSetup.LeftFooter = Format(Date, "dd mmm yyyy") End Sub Note that this will only indicate when a cell is changed due to user input (or external data link). Also, if there are links between multiple worksheets, you'd need to update each worksheet, not just the worksheet that was changed. If you're not familiar with macros, take a look he http://www.mvps.org/dmcritchie/excel/getstarted.htm In article , tjh wrote: Hello, Is it possible to place in the footer of a spreadsheet the date that the report was last modified, rather than the date the report is printed or the current date. Thanks, |
#3
|
|||
|
|||
Footer
Thanks for your respons,
I changed the code slightly to include the hour and minute, but it did not seem to work correctly. It shows 12:00 AM as the current time. Private Sub Workbook_SheetChange( _ ByVal Sh As Object, ByVal Target As Excel.Range) Sh.PageSetup.RightFooter = Format(Date, "dd mmm yyyy" & " " & "hh:mm AMPM") End Sub Thank You, "JE McGimpsey" wrote: One way: Put something like this in the ThisWorkbook code module: Private Sub Workbook_SheetChange( _ ByVal Sh As Object, ByVal Target As Excel.Range) Sh.PageSetup.LeftFooter = Format(Date, "dd mmm yyyy") End Sub Note that this will only indicate when a cell is changed due to user input (or external data link). Also, if there are links between multiple worksheets, you'd need to update each worksheet, not just the worksheet that was changed. If you're not familiar with macros, take a look he http://www.mvps.org/dmcritchie/excel/getstarted.htm In article , tjh wrote: Hello, Is it possible to place in the footer of a spreadsheet the date that the report was last modified, rather than the date the report is printed or the current date. Thanks, |
#4
|
|||
|
|||
Footer
And in addition to the below, how can I change the format. Such as times new
roman and 8 font (within this code) Thank You, "tjh" wrote: Thanks for your respons, I changed the code slightly to include the hour and minute, but it did not seem to work correctly. It shows 12:00 AM as the current time. Private Sub Workbook_SheetChange( _ ByVal Sh As Object, ByVal Target As Excel.Range) Sh.PageSetup.RightFooter = Format(Date, "dd mmm yyyy" & " " & "hh:mm AMPM") End Sub Thank You, "JE McGimpsey" wrote: One way: Put something like this in the ThisWorkbook code module: Private Sub Workbook_SheetChange( _ ByVal Sh As Object, ByVal Target As Excel.Range) Sh.PageSetup.LeftFooter = Format(Date, "dd mmm yyyy") End Sub Note that this will only indicate when a cell is changed due to user input (or external data link). Also, if there are links between multiple worksheets, you'd need to update each worksheet, not just the worksheet that was changed. If you're not familiar with macros, take a look he http://www.mvps.org/dmcritchie/excel/getstarted.htm In article , tjh wrote: Hello, Is it possible to place in the footer of a spreadsheet the date that the report was last modified, rather than the date the report is printed or the current date. Thanks, |
#5
|
|||
|
|||
Footer
Change this line:
Sh.PageSetup.RightFooter = Format(Date, "dd mmm yyyy" & " " & "hh:mm AMPM") to Sh.PageSetup.RightFooter = Format(Now, "dd mmm yyyy" & " " & "hh:mm AMPM") And if you record a macro when you change the formatting of a footer, you'll see something like: With ActiveSheet.PageSetup .RightFooter = "&""Times New Roman,Bold""&8asdfasdf" So you might want something like: Sh.PageSetup.RightFooter = "&""Times New Roman,Bold""&8" & _ Format(Now, "dd mmm yyyy" & " " & "hh:mm AMPM") But you'll have to worry about that the first character of your string is numeric. It could confuse excel into making that font size humongous. Just add an extra space: Sh.PageSetup.RightFooter = "&""Times New Roman,Bold""&8 " & _ Format(Now, "dd mmm yyyy" & " " & "hh:mm AMPM") (there's a space right after the 8.) tjh wrote: And in addition to the below, how can I change the format. Such as times new roman and 8 font (within this code) Thank You, "tjh" wrote: Thanks for your respons, I changed the code slightly to include the hour and minute, but it did not seem to work correctly. It shows 12:00 AM as the current time. Private Sub Workbook_SheetChange( _ ByVal Sh As Object, ByVal Target As Excel.Range) Sh.PageSetup.RightFooter = Format(Date, "dd mmm yyyy" & " " & "hh:mm AMPM") End Sub Thank You, "JE McGimpsey" wrote: One way: Put something like this in the ThisWorkbook code module: Private Sub Workbook_SheetChange( _ ByVal Sh As Object, ByVal Target As Excel.Range) Sh.PageSetup.LeftFooter = Format(Date, "dd mmm yyyy") End Sub Note that this will only indicate when a cell is changed due to user input (or external data link). Also, if there are links between multiple worksheets, you'd need to update each worksheet, not just the worksheet that was changed. If you're not familiar with macros, take a look he http://www.mvps.org/dmcritchie/excel/getstarted.htm In article , tjh wrote: Hello, Is it possible to place in the footer of a spreadsheet the date that the report was last modified, rather than the date the report is printed or the current date. Thanks, -- Dave Peterson |
#6
|
|||
|
|||
Footer
Thanks that helps.
One more, back to JE's comment on linked worksheets. If linked worksheets (within the file) are updated, how can the date on these sheets be updated to show that they were modified also. (without having to touch/change each sheet) Thank You, "Dave Peterson" wrote: Change this line: Sh.PageSetup.RightFooter = Format(Date, "dd mmm yyyy" & " " & "hh:mm AMPM") to Sh.PageSetup.RightFooter = Format(Now, "dd mmm yyyy" & " " & "hh:mm AMPM") And if you record a macro when you change the formatting of a footer, you'll see something like: With ActiveSheet.PageSetup .RightFooter = "&""Times New Roman,Bold""&8asdfasdf" So you might want something like: Sh.PageSetup.RightFooter = "&""Times New Roman,Bold""&8" & _ Format(Now, "dd mmm yyyy" & " " & "hh:mm AMPM") But you'll have to worry about that the first character of your string is numeric. It could confuse excel into making that font size humongous. Just add an extra space: Sh.PageSetup.RightFooter = "&""Times New Roman,Bold""&8 " & _ Format(Now, "dd mmm yyyy" & " " & "hh:mm AMPM") (there's a space right after the 8.) tjh wrote: And in addition to the below, how can I change the format. Such as times new roman and 8 font (within this code) Thank You, "tjh" wrote: Thanks for your respons, I changed the code slightly to include the hour and minute, but it did not seem to work correctly. It shows 12:00 AM as the current time. Private Sub Workbook_SheetChange( _ ByVal Sh As Object, ByVal Target As Excel.Range) Sh.PageSetup.RightFooter = Format(Date, "dd mmm yyyy" & " " & "hh:mm AMPM") End Sub Thank You, "JE McGimpsey" wrote: One way: Put something like this in the ThisWorkbook code module: Private Sub Workbook_SheetChange( _ ByVal Sh As Object, ByVal Target As Excel.Range) Sh.PageSetup.LeftFooter = Format(Date, "dd mmm yyyy") End Sub Note that this will only indicate when a cell is changed due to user input (or external data link). Also, if there are links between multiple worksheets, you'd need to update each worksheet, not just the worksheet that was changed. If you're not familiar with macros, take a look he http://www.mvps.org/dmcritchie/excel/getstarted.htm In article , tjh wrote: Hello, Is it possible to place in the footer of a spreadsheet the date that the report was last modified, rather than the date the report is printed or the current date. Thanks, -- Dave Peterson |
#7
|
|||
|
|||
Footer
J.E.'s code looked for changes that you made by typing--and excel knows what
cell you changed (and what worksheet that cell was on). Changes caused by calculation aren't as nice. Excel doesn't keep track of the cells that changed--or the values before the recalculation. I think the closest thing you could do is to keep track of each value in each cell in each worksheet. Then you could compare those "before" values with the "after recalc" values. It doesn't sound like much fun to me. tjh wrote: Thanks that helps. One more, back to JE's comment on linked worksheets. If linked worksheets (within the file) are updated, how can the date on these sheets be updated to show that they were modified also. (without having to touch/change each sheet) Thank You, "Dave Peterson" wrote: Change this line: Sh.PageSetup.RightFooter = Format(Date, "dd mmm yyyy" & " " & "hh:mm AMPM") to Sh.PageSetup.RightFooter = Format(Now, "dd mmm yyyy" & " " & "hh:mm AMPM") And if you record a macro when you change the formatting of a footer, you'll see something like: With ActiveSheet.PageSetup .RightFooter = "&""Times New Roman,Bold""&8asdfasdf" So you might want something like: Sh.PageSetup.RightFooter = "&""Times New Roman,Bold""&8" & _ Format(Now, "dd mmm yyyy" & " " & "hh:mm AMPM") But you'll have to worry about that the first character of your string is numeric. It could confuse excel into making that font size humongous. Just add an extra space: Sh.PageSetup.RightFooter = "&""Times New Roman,Bold""&8 " & _ Format(Now, "dd mmm yyyy" & " " & "hh:mm AMPM") (there's a space right after the 8.) tjh wrote: And in addition to the below, how can I change the format. Such as times new roman and 8 font (within this code) Thank You, "tjh" wrote: Thanks for your respons, I changed the code slightly to include the hour and minute, but it did not seem to work correctly. It shows 12:00 AM as the current time. Private Sub Workbook_SheetChange( _ ByVal Sh As Object, ByVal Target As Excel.Range) Sh.PageSetup.RightFooter = Format(Date, "dd mmm yyyy" & " " & "hh:mm AMPM") End Sub Thank You, "JE McGimpsey" wrote: One way: Put something like this in the ThisWorkbook code module: Private Sub Workbook_SheetChange( _ ByVal Sh As Object, ByVal Target As Excel.Range) Sh.PageSetup.LeftFooter = Format(Date, "dd mmm yyyy") End Sub Note that this will only indicate when a cell is changed due to user input (or external data link). Also, if there are links between multiple worksheets, you'd need to update each worksheet, not just the worksheet that was changed. If you're not familiar with macros, take a look he http://www.mvps.org/dmcritchie/excel/getstarted.htm In article , tjh wrote: Hello, Is it possible to place in the footer of a spreadsheet the date that the report was last modified, rather than the date the report is printed or the current date. Thanks, -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Footer Macro? | Excel Discussion (Misc queries) | |||
How to insert a picture in the Footer (not the Header) in Excel 20 | Excel Worksheet Functions | |||
Inserting Footer - Ajit | Excel Discussion (Misc queries) | |||
How to automate custom footer on all tabs | Excel Discussion (Misc queries) | |||
Formatting a footer with a top border-line | Excel Discussion (Misc queries) |