+ Reply to Thread
Results 1 to 4 of 4

Excel/VBA 2003 Tool Bar Replacement in 2010

  1. #1
    Registered User
    Join Date
    02-18-2009
    Location
    Apex, NC, USA
    MS-Off Ver
    Office 365
    Posts
    64

    Lightbulb Excel/VBA 2003 Tool Bar Replacement in 2010

    I have recently converted to Office/Excel 2010. I am extending and maintaining an application I created in Excel/VBA 2003. I need some high level design advice for dealing with a tool bar that I dynamically created via VBA 2003. This tool bar is part of a multi workbook application. The VBA macro that creates the tool bar runs out of the auto-open macro in a Personal workbook. If any of the participating workbooks in the application are found to be open (were clicked causing the macro to run) , the macro creates the desired tool bar with buttons that are linked to necessary macros that support the application. If none of the participating workbooks are the one clicked, the start-up macro checks to see if the tool bar is already there and deletes it and if not, it just exits. The button face numbers and the to-be-linked macro names are stored in a table within the Personal workbook.

    All that said, it works mostly fine as I installed Excel 2010 ,except that the buttons are hidden on the Add-Ins ribbon as a custom tool bar.

    I would like the buttons to be more prominent in their 2010 reincarnation! If possible, I would also like to maintain their table driven definition and create whatever replacement via VBA.

    To properly exploit Excel, 2010, what is the proper 2010 resource to use? It appears that tool bars in 2010 have been relegated to being second class citizens, offered as (mere) add-ins and given second class real estate

    Can I create ribbons under VBA? On the ribbon, can I create a group with buttons ? Can the buttons be linked to macros? If this is possible, is this the proper resource to use?

    Across the application there are other worksheet specific macros linked by objects embedded in specific respective worksheets. However, the ones I am questioning in this post are buttons (and their linked macros) that need to be available across workbooks and worksheets.

    Any advice would be appreciated.
    Last edited by DanBlum; 04-13-2012 at 10:11 AM.

  2. #2
    Forum Contributor
    Join Date
    05-05-2009
    Location
    Wisconsin
    MS-Off Ver
    Excel 2010 and 365
    Posts
    113

    Re: Excel/VBA 2003 Tool Bar Replacement in 2010

    Lots of good information at

    http://www.rondebruin.nl/ribbon.htm


    It's kind of a pain and, from what I can tell, very difficult to do it programatically. There is an editor you need to download that you use to create the xml code required. It doesn't have built in syntax or anything so it can be a little tricky debugging. Any minor syntax errors will result in the menues not showing up.


    Also, they only exist if the file you created them in is active. Unless, of course, you save the file as an .xlam add-in and then go into add-in manager and select the add in. Then it will show up all the time.


    They do have a gallery item that can be added in which you can (probably?) add items programatically but I'm still learning. I have macro lists with macro name and descriptions stored in a workbook that I used in 2003 to create buttons to launch macros. Easy to add new macros just by listing them in the worksheet. No need to change code. Not sure how to do that in 2010 but I think the gallery might work.


    The thing that complicates it for me is that the main files used are still in 2003 format because most of those here haven't upgraded to 2010 yet.


    More good info regarding gallery controls

    http://msdn.microsoft.com/en-us/libr...ffice.12).aspx


    It looks like you can load a list from a worksheet. The example has notes about loading button names. It would take some modifications to make it work for macros but using the same method that could be used to set the names of the buttons could also be used to get the name of the macro to run. That way, the list in the worksheet could be updated to add new macros. No recoding required.
    Last edited by Cyclops; 04-13-2012 at 09:29 AM.

  3. #3
    Registered User
    Join Date
    02-18-2009
    Location
    Apex, NC, USA
    MS-Off Ver
    Office 365
    Posts
    64

    Re: Excel/VBA 2003 Tool Bar Replacement in 2010

    Thanks for your extensive reply. As you said, it looks like a pain. I have not programmed yet in XML and don't even know how to get it compiled, etc, etc. If I have interpreted the linked article (and the example within) correctly, the resulting set of butttons will occupy no improved prominence to the user beyond what I have now. Namely, I now have an Add-in tab, below that a custom tool bar group, and finally, nearby, the individual buttons. Just the name on the tab would have changed... I would have my own chosen tab name, my own chosen group name instead of "Custom Tool Bars" and then the same individual buttons. And maybe I'd be more restricted on the button faces. So, you have helped me realize that what I already have is probably the right trade-off of effort v. result.

    Thanks again. You answered my question, although it wasn't exactly what I wanted to hear!

  4. #4
    Forum Contributor
    Join Date
    05-05-2009
    Location
    Wisconsin
    MS-Off Ver
    Excel 2010 and 365
    Posts
    113

    Re: Excel/VBA 2003 Tool Bar Replacement in 2010

    I think I'm starting to come to the same realization.


    One thing you can do is add custom toolbars to the quick access toolbar. It will add an icon that opens a drop down. Still an extra click but at least you don't need to change your active menu to launch a custom macro.


    I just converted an old 2003 toolbar to a new ribbon / menu with buttons but I find myself still going through the quick access toolbar.


    I'm disappointed at the lack of information regarding the syntax (schema?) for the xml code.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1