Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Before Print Macro insertion (Office 2000?)

    I review a large number of standard workbooks in my new position. I want to add to each one, in the ThisWorkbook Before Print module section, my footer text which shows the full and complete pathway to the file and other data. Currently, its a cut and paste op from Personal.xls where I keep my BeforePrint setup text. My Question: Is there a way to create a macro within Personal.xls that will setup my footer strings in the ThisWorkbook/BeforePrint "section" of any particular workbook I have open? Adding that useful pathway information using one macro will save me lots of time in the long run, and the resulting footer information will help others find where the file lives in Networkville. Thanks.

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Before Print Macro insertion (Office 2000?)

    I suppose you could create your own print procedure that ran the Before_Print routine then printed the workbook.

    This could live in your personal.xls and be assigned to a menu/toolbar etc.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Before Print Macro insertion (Office 2000?)

    You could create an application-level WorkbookBeforePrint event handler in your Personal.xls. It will apply to all workbooks, so there is no need to add code to each and every workbook.

    Steps to take:
    <UL><LI>Activate the Visual Basic Editor
    <LI>Select Personal.xls in the Project Explorer
    <LI>Select Insert | Class Module. Name this module clsBeforePrint.
    <LI>Add the following code to the class module:

    Public WithEvents App As Application

    Private Sub App_NewWorkbook(ByVal Wb As Workbook)
    ' Your code to set footer text goes here; use wb instead of ActiveWorkbook
    End Sub

    <LI>Select Insert | Module.
    <LI>Add the following code to the new module:

    Dim MyPrintHandler As New clsBeforePrint

    Sub InitializeApp()
    Set MyPrintHandler.App = Application
    End Sub

    <LI>Call InitializeApp from a macro called Auto_Exec in a standard module or from the Workbook_Open event handler in the ThisWorkbook module of Personal.xls.[/list]Type "using events with the application" (without quotes) in the Answer Wizard to learn more about application level event handlers.

  4. #4
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Before Print Macro insertion (Office 2000?)

    My Call is bombing out...is this correct?

    Private Sub Workbook_Open()
    Call IntializeApp
    End Sub

    Every time I put () after the App it disappears! I guess I should stick with my day job.

    My footer text refers to "ActiveSheet.PageSetup.CenterFooter = "&P of &N" " for example. Should I then say something different in the Private Sub App_NewWorkbook(By Val Wb as Workbook)? I suspect that might cause a problem as well.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Before Print Macro insertion (Office 2000?)

    You don't need to use Call explicitly, and it should be InitializeApp instead of IntializeApp.

    BTW, I should have used the WorkbookBeforePrint event (as I announced but failed to do). Here is code, avoiding ActiveSheet:

    Private Sub App_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)
    Dim sht As Worksheet
    For Each sht In Wb.Worksheets
    sht.PageSetup.CenterFooter = "&P of &N"
    Next sht
    Set sht = Nothing
    End Sub

    I have attached a zipped demo workbook with the code.

  6. #6
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Before Print Macro insertion (Office 2000?)

    Thanks. THis doesn't seem to work on my Excel2000 office laptop. Are there any amendments needed? I copied the Class Module into the Personal.xls file. Of course, this is the Office setup that won't let me retain customized toolbars, so nothing would surprise me. Here is what I amended the code to read:

    Private Sub App_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)
    Dim sht As Worksheet
    For Each sht In Wb.Worksheets
    sht.PageSetup.LeftFooter = ActiveWorkbook.FullName & "/" & " &A" & vbCr
    sht.PageSetup.RightFooter = "&D &T"
    sht.PageSetup.CenterFooter = "&P of &N"
    Next sht
    Set sht = Nothing
    End Sub

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Before Print Macro insertion (Office 2000?)

    If you make the modifications to Hans' file does it work (It works on my XL97 in Win XP)

    Did you copy the code from the Module?
    Did you copy the code from the thisWorkbook Object?

    Steve

  8. #8
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Before Print Macro insertion (Office 2000?)

    Option Explicit

    Dim MyPrintHandler As New clsBeforePrint

    Sub InitializeApp()
    Set MyPrintHandler.App = Application
    End Sub

    Help me understand this. I straightened myself out by examining the Modules and finding a basGeneral module in Hans' zipped file. What in the heck is that thing doing? All the code is in the Class Module, isn't it? So what is the other one doing and why does it have the unusual name?

    So I copy the basGeneral Module into my Personal.xls Module on the Excel2000 laptop, so clsBeforePrint has some company, and it STILL doesn't work, even if I shut everything down and open Excel from the Start menu. An "innocent" file I open up and do a print preview does not show the desired footer information. I was under the impression that being in a Class Module in Personal would enable the footer to be shown on any sheet in any workbook I needed to print. Sort of a universal footer.

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Before Print Macro insertion (Office 2000?)

    OK, You HAD the code from the CLASSMODULE,
    Now you have the code from the MODULE.

    Did you copy the code from thisWorkbook object? This is the code to run on workbook open?:
    <pre>Private Sub Workbook_Open()
    InitializeApp
    End Sub </pre>


    You need the code in ALL 3 places.

    If placed in personal.xls:
    when the workbook is open:
    WorkbookOpen routine calls the routine initializaeApp
    Initialize App is in a normal module (Hans renamed this module basGeneral in his workbook):
    This module
    dims an object in a NEWLY Created object type named clsBeforePrint
    Sets the object as an application level event

    The new class type is in the CLASS Module (clsBeforePrint)
    Which DEFINES an APPLICATION-LEVEL event (it is an application-level BEFORE Print event)

    Before print events are typicaly WORKBOOK level events: When you create the code, it runs Before you print IN a PARTICULAR workbook. This code runs BEFORE you PRINT in ANY WORKBOOK.

    Steve

  10. #10
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Before Print Macro insertion (Office 2000?)

    Thank you for the clairification. Will add the additional Workbook Open code to Personal.xls and study your comments.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •