VBA project

[GYR]

Hello,

We are using Excel 2007.

I wanted to add the date of the last modification of the file to the printed pages, so I googled how to do it and found a useful answer on this from from 10 years ago. It said to press ALT+F11, then on the left side go under VBAProject, there go to ThisWorkbook and there paste the next code:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
For Each wk In Worksheets
With ActiveSheet.PageSetup
.LeftHeader = "Last Modified on " & ActiveWorkbook.BuiltinDocumentProperties.Item("Last Save Time")
.CenterHeader = ""
.RightHeader = "Printed on &D &T"
.LeftFooter = "&F &A"
.CenterFooter = ""
.RightFooter = "Page &P of &N"
End With
Next
End Sub

Then I saved it and closed. Like I said, it worked perfectly.

The problem is that when I open the file after closing it, it does not appear, and I have to paste the code again every time I open it. Not only is this not comfortable, it also counts as a modification, so even if I haven't changed the work sheets themselves, just wanted to open and print it, it shows today's date.

My question is, who to I save it permanently so it will remain always after the first time I created it?

While we're at it, if there's a way for that code to appear on every NEW Excel file I create automatically that would be great.

Thanks so much.

Gil

 

[shg]

You need to save the file in macro-enabled (xlsm) fomat. You should be getting a warning that you can't save the VBA project with an xlsx file.

 

[wrightyrx7]

Oh yea. Do what SHG said

 

[Firefly2012]

Hi Gil

Do you save the file down after you paste the code in (you need to)? And you must save the file as something other than a .xlsx (as these do not hold VBA code).

To include this in any new workbooks created, you need to create a new workbook and post the code into the ThisWorkbook module of this workbook, and then save this workbook as Book.xltm in your XLSTART folder (the location of which will vary according to your OS - do a Google search to identify where it will be).

 

[GYR]

I saved it as xlsm and it seems to be working.
This is so efficient! Thank you all!
Gil.

 

[shg]

You're welcome, good luck.