Solved

Automated Footer with VB

Posted on 2004-10-15
9
525 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
SuperAntiSpyware Licenses Discounted by 25% !

Exclusive offer to Experts Exchange Members!
Buy SuperAntiSpyware License(s) from us and save 25% on the regular purchase price.
- Includes Full SuperAntiSpyware Vendor Support Entitlements
- Your Subscription does not begin until you activate your license
- Buy for your friends

 
LVL 1

Author Comment

by:studeggle
ID: 12322113
That will work :D
0
 
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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: …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

752 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