Excel 2007 Code

[Dee Bee 19]

Hi - I have been running Excel 2003 and using the following code to inset the File Name & Path and most importantly the Date and Time last saved into a footer which works fine.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveSheet.PageSetup.LeftFooter = FullName & Space(40) & "Last saved: " & Format(Date, "dd-mm-yy") & " " & Time

End Sub

Now I have moved up to Excel 2007 inserting the same code doesn't seem to work - nothing is displayed in the footer. If I use 2003 to create the file using the code above and then open it using 2007 all is well. What am I doing wrong?

Dee Bee

[jolivanes]

Worked for me in 2007.
Where is the code? In the "ThisWorkbook" pane?

[Bob Phillips]

My system doesn't allow colons in the filename, soi maybe


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveSheet.PageSetup.LeftFooter = FullName & Space(40) & "Last saved: " & Format(Now(), "dd-mm-yy hh.mm.ss")
End Sub

[Dee Bee 19]

Worked for me in 2007.
Where is the code? In the "ThisWorkbook" pane?

Thanks for quick response.

Good Point! 2003 opens directly to the "ThisWorkbook" pane when clicking "View Code" - 2007 opens to the "Sheet1" pane.

Is there any easy way to get to the "ThisWorkbook" pane in 2007?

[Bob Phillips]

Select it in the explorer.

[Dee Bee 19]

Select it in the explorer.

Sorry - not sure how to do that - bit of a newbie with 2007. Can you give me instructions on how to reach the explorer?

[Bob Phillips]

In the VBIDE, Ctrl-R.

[Dee Bee 19]

In the VBIDE, Ctrl-R.

Yea!! Ctrl+R brings up VBAProject(Book 1) and there is ThisWorkbook!! Inserted my code and all is fine.

Code works - I work!

Thanks guys for all your help.

[Bob Phillips]

So how did it ever work in Excel 2003 if it was in the wrong module?

[Dee Bee 19]

So how did it ever work in Excel 2003 if it was in the wrong module?

As I said earlier, 2003 automatically comes up with the "ThisWorkbook" module by default unlike 2007.

Kind regards to all.