Automated Footer with VB
Posted on 2004-10-15
We have a problem with lots of different people working the same file, printing of hard copies and then they end up getting mixed up and no-one knows which one is the latest without careful and painstaking studing and collaboration. So I want to add a VB script to the document that will automatically update a footer each time the document is saved, hence no more confusion. I did a search and picked up the following VB script for Excel from ture (Ture Magnusson) in a post to another person's question and it helps with what I want but has some shortcomings that I would like to try and change.
It only does the Date in the footer. I want my footer to read "FileName Date Vers #" FileName would be whatever the file is called, Date would be the file was last saved, and Vers # I would like to count upwards each time the file is saved.
And as if that wasn't enough I would like to also do this with Word and PowerPoint if at all possible.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.PageSetup.RightFooter = Date
Can anyone help me rewrite this script to do what I want, or at least get closer. I do understand that I will probably need different scripts for each type of document--that is not a problem.