Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Oct 2002
    Location
    Dallas, Texas, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro for Book.xlt and Sheet.xlt (2000)

    (You guys were so helpful yesterday, I thought I'd push my luck and see if you could help me again <img src=/S/blush.gif border=0 alt=blush width=15 height=15> ...)

    1. If I put the following macro in my book.xlt and sheet.xlt and include a Workbook_Open or Worksheet_Open will it run the macro whenever a new Workbook or Worksheet is created?

    Sub UpdateFooter()
    '
    ' UpdateFooter Macro
    ' Macro recorded 9/6/2002 by Abbie Waters
    '

    '
    ActiveSheet.PageSetup.RightFooter = "&8" & ActiveWorkbook.FullName
    ActiveSheet.PageSetup.CenterFooter = "&8Page &P of &N"
    ActiveSheet.PageSetup.LeftFooter = "&8Run Date: &D &T"


    End Sub


    2. How do I make the macro run EVERYTIME sheet or workbook focus is changed?

    (I'm dealing with some folks who can't seem to remember to push the smiley face button on their toolbars whenever they create a new spreadsheet or change the path or "Save As".)

    I've got the Macro in everybody's PERSONAL.xls, and buttons on everybody's toolbar, already.

  2. #2
    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: Macro for Book.xlt and Sheet.xlt (2000)

    Why don't you just add the following to the before print event (under thisworkbook)
    You only need to update it before it prints, the rest of the time it doesn't matter.

    Steve

    <pre>Private Sub Workbook_BeforePrint(Cancel As Boolean)
    updateFooter
    End Sub
    </pre>


  3. #3
    New Lounger
    Join Date
    Oct 2002
    Location
    Dallas, Texas, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro for Book.xlt and Sheet.xlt (2000)

    Will that work globally if I have it in PERSONAL.xls? Or would I have to add it to ALL existing workbooks (the mind boggles...)

  4. #4
    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: Macro for Book.xlt and Sheet.xlt (2000)

    It has to be in each WORKBOOK that you want it in. It is called before anything in the workbook prints. You can add it to your default workbook templates if desired.

    Steve

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro for Book.xlt and Sheet.xlt (2000)

    You could also have it in personal.xls, but it would require you to create a class module that will handle application wide events.

    Check out this page for more information.

    Another remark.

    Don't use personal.xls for stuff like this. personal.xls is meant for personal use, e.g. recording macro's a user wants to have available always. If you create a new version and update everyones personal.xls, they loose their macros. I know I would be cross with you if you did that!

    Better: Create a separate workbook with all your macros and customisations. Attach the toolbars that belong to it to it, hide it and then save a copy into everyones XLSTART directory.

    About toolbars:

    You can attach a toolbar to a workbook. When this workbook is loaded, XL checks if the toolbar is on the system. If not, it copies the toolbar from the workbook to the system.

    After creating *or changing* the toolbar, you should attach the toolbar to your workbook:

    - activate the workbook to which you want to attach the toolbar
    - Rightclick the toolbar, select 'customize'
    - Click 'Attach' (Toolbars Tab)
    - If the workbook already contains a toolbar by that name, delete it first by clicking on it on the righthand side and choosing Delete.
    - Select your toolbar (on the left) and press 'copy'
    - Save the workbook (optionally: save_as an add-in).

    Also, You should include code that deletes the toolbar when your workbook or add-in is closed, so that when you deliver a new version of your workbook the new toolbar will be used i.s.o the old one. You can do that in the Thisworkbook module, using the Workbook_beforeClose event:

    Private Sub Workbook_beforeClose()
    On Error Resume Next 'In case Toolbar is absent
    Application.CommandBars("YourBarsName").Delete
    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    New Lounger
    Join Date
    Oct 2002
    Location
    Dallas, Texas, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro for Book.xlt and Sheet.xlt (2000)

    Thanks, Jan.

    I don't think I have worry about overwriting my users' personal.xls. They didn't even know they had one, and probably still don't. I'm going to try the ClassApp. I'll probably send the code before I actually implement it, just so I don't blow anything up. (I'm not a novice at VB, but I'm not always sure of my syntax.)

    Abbie <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

  7. #7
    New Lounger
    Join Date
    Oct 2002
    Location
    Dallas, Texas, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro for Book.xlt and Sheet.xlt (2000)

    Okay, will this run the macro "UpdateFooter" (see above) which is store in the personal.xls, everytime a workbook is opened, or added, or a new sheet is opened or added?

    In personal.xls, Class Module

    Public WithEvents App As Application

    Private Sub App_NewWorkbook(ByVal Wb As Workbook)

    End Sub

    Private Sub App_SheetActivate(ByVal Sh As Object)
    UpdateFooter
    End Sub

    Private Sub App_WorkbookActivate(ByVal Wb As Workbook)
    UpdateFooter
    End Sub

    We do a lot of importing from another application to excel, and it would really be nice to have the footer applied without having to think about it.

    And, yes, we want every single instance of every single spreadsheet created to have this footer.

    Also, would it be better to have this macro run every time we shift focus (as I think this is doing) or when we print. And if when we print, where to I put the BeforePrint statement? In the Class Module or in the ThisWorkbook Module?

    Thanks, in advance...

    Abbie

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro for Book.xlt and Sheet.xlt (2000)

    I guess a before_print event in the class module should do.
    I see no point in updating the footer each and every time one switches sheets/books.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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