Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Build Macro Toolbar depending on File (2003)

    Good morning,

    I have a number of workbooks that I distribute to people across my organization. I prefer to distribute them without any macros for various reasons.

    On the other hand, I've developed several macros per workbook that help me revise the workbook in whatever way needs to be done. For example, one workbook has dates in a cell that may get revised but I need to keep the old dates available. The old dates are formatted with strikethrough and the new date is entered with ALT+enter. Since getting to the strikethrough format involves many clicks, I created a macro that does the strikethrough and prompts me for the new date. This macro is NOT used in any of the other workbooks I maintain.

    Naturally, these macros are in my Personal.xls. And I can add a button for each macro to my toolbar. So far, so good.

    But I have maybe 2 or 3 macros per workbook. So adding a button to my Excel toolbar (Excel11.xlb for example) for all my macros clutters up my toolbar so I have to check which button to click on.

    What I'd like to be able to do is when the workbook is opened to build a toolbar with buttons linked to just the macros for that workbook. I know how to build a toolbar in code, create buttons, and link the buttons to macros. What I'm missing is the ability to choose which set of macros to link to the toolbar buttons based on which workbook is being opened.

    I should also say that a given workbook may not have a fixed name. What I mean by that is that the name of a set of workbooks that I want the same macros for may have a name like "Routers 20071116.xls". The first part of the name is fixed but there's a date included in the file name that is used to indicate the date the file was created. So the files "Routers *.xls" with a date substituted for the * have a set of macros in my Personal.xls associated with them. Also, the files may be stored in different folders.

    If I were using the Workbook Open routine in the file to be distributed, this would be easy. But as said at the beginning, I don't want any macros in the files that I distribute.

    So any ideas on how to do this?

    TIA

    Fred

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Build Macro Toolbar depending on File (2003)

    You would need:
    1. A host workbook - probably an add-in.
    2. All the relevant documents to be tagged in some way - a Custom Document Property may be the best bet.
    The host file would need to contain a class module with an application object declared WithEvents. This will enable you to trap the Application's WorkbookOpen event, check the property of the workbook, and respond accordingly.

    If your users may have more than one workbook open that requires its own toolbar, you will need to trap the activate/deactivate events of the workbooks too. Might be easiest to have another class (a WorkbookMonitor) with a WithEvents workbook variable to do this.

    Hope that gives you some ideas.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Build Macro Toolbar depending on File (2003)

    Hi Rory,

    > Hope that gives you some ideas.

    Yes. This is beyond my capabilities. I've never done anything with class modules.

    >1. A host workbook - probably an add-in.

    I know what an add-in is about. But not sure how this would fit into the overall scheme of things. I kind of see that this host workbook is where the code would reside that sets up the toolbar depending on the file being opened.

    >2. All the relevant documents to be tagged in some way - a Custom Document Property may be the best bet.

    Why is this needed? Why couldn't the code depend on the file name? As mentioned, there may be a set of files that have a common name such as "Router....xls" where the ... is a date, so a typical file name would look like "Router 20071117.xls" and another of this set would be "Router 20071116.xls". For the "Router...xls" set of files, I'd want the same toolbar. (PS: I forgot to mention that sometimes the name would be "Copy of Router 20071116.xls" because of the way I do things. So any file with "Router" anywhere in its file name would be part of the same set of files to get the same toolbar.)

    That said, setting up a Custom Doc Property would not be a problem. Just not clear on why it's needed (other than, perhaps, to save parsing the file name, especially given the PS I just threw in). This part seems the least of my problems.

    Thanks.

    Fred

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Build Macro Toolbar depending on File (2003)

    The add-in would contain the class module with the Application variable to monitor workbooks being opened. (Using an add-in rather than Personal.xls makes it easy to turn on/off and to distribute to others)
    I would be easier to have tags because I assume you can have different naming conventions and different toolbars depending on the name. It seems pointless having the code run for all opened workbooks when you presumably only need it to monitor certain workbooks. It can also be used instead of a name parsing routine as you say. It might be easier to have two tags, one to say that the file needs a toolbar, and one to say which toolbar. Then you don't have to alter the code unless you need a whole new toolbar creation routine.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Build Macro Toolbar depending on File (2003)

    Hi Rory,

    I think I'm getting the hang of most of it.

    I recall that Jan Karel Pieterse posted something about creating add-ins so I'll check that out.

    Since I haven't done any class modules before, I'm not sure how to monitor for workbooks being opened. But I think I've seen some tutorials on class modules that might help. If not, I'll post back.

    I think I only need 1 custom property. Each workbook "type" only has a few buttons so I only need 1 toolbar per "type." I have enough types to make using 1 toolbar for all of them a little unwieldy in terms of having too many buttons. As you also suggested, there may also be many types of workbooks that I don't need any toolbar for, like ones I get from other people and even some of my own.

    But one thing you mentioned confused me:
    >It seems pointless having the code run for all opened workbooks when you presumably only need it to monitor certain workbooks.

    Wouldn't I have to run the code for all workbooks to check if they're one of my special ones? Of course, if not, then the code exits immediately, so only a few lines of checking code runs. If it is one of my specials, then I check which one and build the toolbar. I'm envisioning the first few lines of code would be something like:
    - check for existence of custom property; if it doesn't exist, exit.
    - Select case based on value of custom property for known workbook types (which can grow). If no match (which should not happen since I wouldn't add the custom property unless giving it a value for building a toolbar), exit. If there is a match, call a subroutine for just creating a toolbar, which all my special workbooks would need. Then within the code for that match after returning from creating the toolbar, create the buttons and link them to the macros within the add-in.

    I'd probably also monitor for the close to delete the toolbar.

    I think that would do it.

    Thanks for the guidance.

    Fred

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

    Re: Build Macro Toolbar depending on File (2003)

    Attached is a demo of an application event listener in a class module.
    Attached Files Attached Files
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Build Macro Toolbar depending on File (2003)

    Jan Karel,

    Thanks for the demo.

    It seems there are a lot of little pieces that go into doing this. I guess I can copy most of your code and just use WorkbookOpen and WorkbookClose instead of the events you have (Activate and Deactivate).

    I'll give it a whirl.

    Fred

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Build Macro Toolbar depending on File (2003)

    Here's another rough demo. In the zip file there are:
    1. WorkbookMonitor.xls - this is the one with the code in.
    2. Monitortest1, monitortest2 and monitortest2b workbooks. These all have a custom property called "monitorme" added, which has been set to the name of the toolbar required.
    If you open WorkbookMonitor then open any or all of the others, you should see different menus appear. As you switch between the workbooks, the relevant menus are hidden or made visible as required.
    Hopefully the code is relatively clear (it's very rough and has crude error handling!) and is enough to give you some ideas.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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