Solved

Automated Footer with VB

Posted on 2004-10-15
9
520 Views
Last Modified: 2007-11-27
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.

VB script:

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
Next ws
End Sub


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.

Thanks,
David Eggleston
0
Comment
Question by:studeggle
  • 6
  • 3
9 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 12321718
Hi David,
Here is a modification of Ture's sub that adds the filename, date and version number to the footer. The version number is stored in a named formula associated with the file. This sub goes in the ThisWorkbook code pane.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
Dim str As String
Dim VersionNumber As Variant
On Error Resume Next
VersionNumber = Mid(ThisWorkbook.Names("VersionNumber").Value, 2) + 1
If Err <> 0 Then
    ThisWorkbook.Names.Add Name:="VersionNumber", RefersTo:="=1", Visible:=False
    VersionNumber = 1
    Err.Clear
End If
On Error GoTo 0

ThisWorkbook.Names("VersionNumber").RefersTo = "=" & VersionNumber
str = ThisWorkbook.Name & "   " & Format(Date, "mm/dd/yyyy") & "    Vers #" & VersionNumber
For Each ws In ThisWorkbook.Worksheets
    ws.PageSetup.RightFooter = str
Next ws
End Sub


Cheers!

Brad
0
 
LVL 1

Author Comment

by:studeggle
ID: 12321985
That works wonderfully  :D Thank you for the quick responce.  I don't supose you know how to tackle the other part of the question  "And as if that wasn't enough I would like to also do this with Word and PowerPoint if at all possible."?
0
 
LVL 81

Expert Comment

by:byundt
ID: 12322046
David,
I'd like to tackle the problem of making the code work in Word & PowerPoint. I'll have time either tonight or tomorrow.
Brad
0
 
LVL 1

Author Comment

by:studeggle
ID: 12322113
That will work :D
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 81

Expert Comment

by:byundt
ID: 12322243
David,
Which version(s) of Office are people using?
Brad
0
 
LVL 1

Author Comment

by:studeggle
ID: 12322556
Office XP
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 12330418
David,
For Word, you will need to create a class module to create the BeforeSave event. To do this, ALT + F11 to open the VBA Editor, then CTRL + R to open the Project Explorer. In the Project Explorer pane on the left, click on the name of your document (or template), then use the Insert...Class Module menu item to create a blank class module sheet.

I changed the name of the class module from the default value Class1 to EventClassModule. To do this, click on Class1 in the Project Explorer, then open the View...Properties menu item. Click on Class1 on the right side of the resulting dialog window--to the right of (Name). Type in the new name.

'Code for class module:
Public WithEvents appWord As Word.Application      'This statement must go before any subs or functions

Private Sub appWord_DocumentBeforeSave(ByVal Doc As Document, SaveAsUI As Boolean, Cancel As Boolean)
    Dim str As String
    Dim VersionNumber As Variant
    VersionNumber = ActiveDocument.BuiltInDocumentProperties("Revision number")
    str = ThisDocument.Name & "   " & Format(Date, "mm/dd/yyyy") & "    Vers #" & VersionNumber
    ActiveDocument.Sections(1).Footers(wdHeaderFooterPrimary).Range.Text = str
End Sub


Next, you must initialize the class module when you open the document or create a new document. To do this, enter the following code in the ThisDocument code pane:

'Code for ThisDocument code pane
Dim X As New EventClassModule   'This statement must go above any subs or functions

Private Sub Document_New()
    RegisterEventClass
End Sub

Private Sub Document_Open()
    RegisterEventClass
End Sub

Sub RegisterEventClass()
    Set X.appWord = Word.Application
End Sub

To test the code, run the RegisterEventClass sub to register the class module, then click the Save button.
Brad
0
 
LVL 81

Expert Comment

by:byundt
ID: 12330527
David,
For PowerPoint, you will also need to create a class module, using the same approach as for Word. I named it EventClassModule once again. One big difference is that I didn't find a Presentation_Open event that would run when the presentation was opened. Instead, it only ran when you opened a second presentation. As a result, the event class must be registered manually by running the RegisterEventClass macro after the presentation has opened.

Another difference is the fact that PowerPoint produces both slides and notes. I didn't know whether you wanted the footer in the slides or notes, so the code includes both.

'Code for class module
Public WithEvents appPP As PowerPoint.Application   'This statement must go above any subs or functions

Private Sub appPP_PresentationSave(ByVal Pres As Presentation)

    Dim str As String
    Dim VersionNumber As Variant
   
    VersionNumber = ActivePresentation.BuiltInDocumentProperties("Revision number")
    str = ActivePresentation.Name & "   " & Format(Date, "mm/dd/yyyy") & "    Vers #" & VersionNumber
    If ActivePresentation.HasTitleMaster Then
        With ActivePresentation.TitleMaster.HeadersFooters
            With .Footer
                .Text = str
                .Visible = msoTrue
            End With
        '.SlideNumber.Visible = msoFalse
        End With
    End If
    With ActivePresentation.SlideMaster.HeadersFooters
        With .Footer
            .Text = str
            .Visible = msoTrue
        End With
        '.SlideNumber.Visible = msoFalse
    End With
    With ActivePresentation.Slides.Range.HeadersFooters
        With .Footer
            .Text = str
            .Visible = msoTrue
        End With
        '.SlideNumber.Visible = msoFalse
    End With
    With ActivePresentation.NotesMaster.HeadersFooters
        .Footer.Text = str
    End With
End Sub

'Code for regular module sheet:
Dim X As New EventClassModule   'This statement must go above any subs or functions

Sub RegisterEventClass()
    Set X.appPP = PowerPoint.Application
End Sub

To test the code, run the RegisterEventClass sub to register the class module (only need to do this once), then click the Save button.
Brad
0
 
LVL 81

Expert Comment

by:byundt
ID: 12330769
David,
It turns out that PowerPoint will support automatic events when you open a presentation file provided you install an add-in that registers the class module. Here's a link to the required code, developed by PowerPoint MVP Shyam Pillai http://www.mvps.org/skp/autoevents.htm
Brad
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now