Results 1 to 4 of 4
  1. #1
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Centralised Macros (Excel XP)

    I have a number of spreadsheets that all utilize the same bunch of macros. Originally, I had the macros in each spreadsheet which caused lots of <img src=/w3timages/censored.gif alt=censored border=0> when I needed to update / upgrade / change the macros. I now use a separate spreadsheet that has the macros stored in them and each spreadsheet that needs these macros has an auto exec subroutine to make sure that that spreadsheet is loaded (loading it if it isn't).

    Is this the best way of doing this (prob not)? Is there a better way? What do *.xla spreadsheets do for me in this context?
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

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

    Re: Centralised Macros (Excel XP)

    1. If you have a custom toolbar button or custom toolbar item that refers to a macro in a central workbook, that workbook will be opened automatically (in the background) the first time the user clicks the button or menu item. So there is no need to ensure that the macro workbook is loaded.

    2. If you save the central workbook with the macros as an Excel add-in (*.xla), the users can install this add-in (Tools | Add-Ins...); it will then be loaded each time Excel is started, so that macros etc. are available all the time. An added advantage is that an add-in is invisible and not easily modified by the end-user.

  3. #3
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Centralised Macros (Excel XP)

    Really? I didn't know that option 1) worked. I had a button on the sheet that pointed to the all powerful 'run' macro in this external spreadsheet and it wasn't happy unless the workbook holder the macro was open. I have hidden the macro workbook and also marked it as read only so it is as invisible as I could get it without going down the password route.

    Thanks for the feedback - I'll pass that onto the team.
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

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

    Re: Centralised Macros (Excel XP)

    If you create a command button on a worksheet using the Forms toolbar, you can easily assign a macro to it residing in another open workbook (it'll be displayed in the list when you right-click the button and select Assign Macro...). If you click the button later, when the macro workbook is not open, it will be opened automatically.

    If you create a command button on a worksheet using the Control Toolbox, it is a bit more work to make it run a macro residing in another workbook, but it is still possible: create an On Click event procedure and call the macro using Application.Run:

    Private Sub CommandButton1_Click()
    Application.Run "Book2.xls!Test"
    End Sub

Posting Permissions

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