Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Nov 2002
    Location
    Farnborough, Hampshire, England
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using One Macro for Several Worksheets (97 SR2)

    In Word, you can have a set of documents based on a single template. Shortcut keys and VBA can be saved in that template, and updates to these will be reflected in all documents based on the template.

    Is it possible to have something similar in Excel?

    In particular, I want to have a central macro (so it's easy to update), called up by a menu button or shortcut key which is only available for certain workbooks.

    Excel Help implied that updated macros in a template would not be updated in workbooks already created from it. Also, I can't work out how to assign a macro to a shortcut key or menu button just within a particular template. They seem to be available even when the templated workbook is closed.

    Can anyone help me, please?

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Using One Macro for Several Worksheets (97 SR2)

    No, you have to reopen the template and deliberately make changes to it.
    Also, Excel is very annoying when it comes to assigning shortcut keys - unlike Word it will not tell you if the keyboard shortcut has already been assigned, so it is easy to override existing shortcuts unintentionally. I used to have a list of the "available" keyboard shortcuts, but I've mislaid it. Perhaps one of the other loungers can help there.
    If you are creating your macro by recording, the dialogue box asks you where you want to store the macro. If you are creating a macro through the VBA editor, select the Excel file and choose to insert a new module (see attached image).

    Cheers
    Attached Images Attached Images
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using One Macro for Several Worksheets (97 SR2)

    Yes.

    If you create a workbook that is formatted the way you want it, and include the VBA code in a module in that workbook, then use File/SaveAs and change the file type to .xlt Template, and put the .xlt file in the XL Template directory (location varies with XL and Windows versions), then any new workbook created using the template will be formatted the way you want it and the code will be available in the new workbook. XL keeps the menues and toolbars in a file with a .xlb extension. Therefore, if you add a button to a menu or toolbar, that button is going to be there for any XL workbook. Therefore, if you want the button there only for workbooks created from the template, then there would have to be code in the template Workbook Activate routine to create the button where you want it, and code in the Workbook Deactivate routine to remove the button.
    Legare Coleman

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using One Macro for Several Worksheets (97 SR2)

    UMMMMM, I guess I better stop doing it then! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    See my reply to Mark.
    Legare Coleman

  5. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Using One Macro for Several Worksheets (97 SR2)

    I can't believe I disagreed with you!
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using One Macro for Several Worksheets (97 SR2)

    I wasn't there to disagree with when you posted.
    Legare Coleman

  7. #7
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Using One Macro for Several Worksheets (97 SR2)

    Is that a way of saying I'm in my own time zone <img src=/S/grin.gif border=0 alt=grin width=15 height=15>?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  8. #8
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Using One Macro for Several Worksheets (97 SR2)

    But seriously, I think I interpreted the question differently than you did.
    In Word for example - if you are working with a document based on a template and you want to update a shortcut key for that template you can actually chose (see attachment) which template you will store this in. Is there a way of doing this in Excel, aside from reopening the template?

    Curious
    Attached Images Attached Images
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using One Macro for Several Worksheets (97 SR2)

    The answer is obviously no since, as I said in my message, that information is not kept in the template in Excel. In Excel, once a new workbook is created from a template, there is no longer any connection between the template and the template. The new workbook becomes a complete copy of the template and there is no connection back to the template and no changes in the template affect the workbook.
    Legare Coleman

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

    Re: Using One Macro for Several Worksheets (97 SR2)

    In addition to what Andrew and Webgenii have written:

    You need to create a workbook that will contain all code and toolbars needed for your application. This code will have to determine somehow that the active workbook is one which is meant to be treated by the code. You might include a hidden worksheet in the templates which the code can detect so it knows it is to be available.

    Then you also need to make templates, which don't have to contain any code, but do contain (e.g.) a hidden worksheet or a hidden name so the code workbook knows it can do it's thing.

    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

    Separating the code from the templates is your task I guess.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    Lounger
    Join Date
    Nov 2002
    Location
    Farnborough, Hampshire, England
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using One Macro for Several Worksheets (97 SR2)

    Thanks to everyone for their help on this.

    I've come to the conclusion that the ends don't justify the means on this, so I'm just going to create an add-in and let the users add a link to the macro as they wish. However, I will keep the more complex solutions suggested in mind for the future!

    Thanks again!

Posting Permissions

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