Excel Menu builder

Here is my (free) menu builder. Its for classic Excel only, but maybe one of you ribbon fanbois would modify it to work with 2007 too? Ribbon XML is so easy right?
[edit link corrected 24/09/08 to free products page]

I haven’t made much effort to tidy it up for release, its just as I use it (ie tested and working on my machine, last time I needed it, nothing more).

There are plenty of other menu builders around, but often they are worksheet driven. Thats great for .xls and .xla stuff, but as I do a lot of COM stuff its not ideal. My one uses a worksheet initially, but actually outputs a .bas module. That can then be imported to an excel based project or a VB6 one.

Watch out though as the code appends, so if you run it a few times with the same output file you end up with tons of repeated code. I am sure there was a reason why its like that, I can’t remember it though.

Here are the base settings:

menu builder settings

And here is the table where you fill in the menu text, tooltips etc.

Menu builder table

It only does basic buttons, you can just go in and adjust the VBA if you need any fancy stuff. It does a matching toolbar and menu. Just delete the code for the bits you dont want.

The workbook also has an embedded toolbar with the first 1,000 toolbar buttons on it, with their IDs. When you shut the workbook that stays around. You can hide it or delete it from Tools>>Customise>>Toolbars. I have never found these embedded toolbars very manageable, so I always create and destroy my menus on open and close. Doing that with 1,000 buttons is painfuly slow though.

The code is open and unprotected, but all the work is done concatenating strings in the worksheets (the hidden one called workings). It also has basic instructions.

Let us know what you think

cheers

Simon

Advertisements

9 Responses to “Excel Menu builder”

  1. Rob Bruce Says:

    “I am sure there was a reason why its like that, I can’t remember it though.”

    Just look it up in your documentation, right? ;-)

  2. Simon Says:

    Rob
    I would do, but the dog ate it! ;-)

  3. Marcus Says:

    Hi Simon,
    I’ve done similar but prefer to have the process table driven 9from a central database). For some add-ins I’ve developed in a corporate environment I’ve done the following:
    1) grab the user’s logon id and retrieve their user group (privileges) from the database.
    2) retrieve a list of menu options for the given user group
    3) loop through the menu options and build the menu.

    The process isn’t over complex as you’re working within a known environment. It allows the menu creating process to be dynamic, allows new or amended menu options to be managed centrally and makes it easy for individual users to move between user groups.

    Cheers – Marcus

  4. Dennis Wallentin Says:

    Simon,

    The table driven approach for RibbonX is well documented in the book:
    RibbonX: Customizing the Office 2007 Ribbon

    Kind regards,
    Dennis

  5. jonpeltier Says:

    I suspect Simon’s dog was tasked to document the utility, but never did so.

  6. Ross Says:

    Looks good Simon, might pop back and “hit this up” If i need to make a big meun for a com addin, nice one!

  7. Rod Says:

    hey there- I ‘ve been looking for just this sort of thing – but the link to your menu builder is broken…. could you fix he link or email the menu builder to me ?

    thanks
    Rod

  8. Simon Says:

    Thanks for the heads up Rod
    You can get it from here:
    http://www.codematic.net/excel-tools/free-excel-tools.htm

  9. titski Says:

    Option Explicit

    Function genm()
    Dim x As Range
    Dim c As Range
    Set x = Cells.SpecialCells(xlCellTypeConstants)
    Dim b As Object
    Set b = CommandBars.Add
    Dim h As New Scripting.Dictionary
    For Each c In x.Cells
    Select Case Trim(c.Value)
    Case “btn”
    Set h(h.Count) = b
    Set b = b.Controls.Add
    Case “end btn”, “endbtn”
    Set b = h(h.Count – 1)
    h.Remove (h.Count – 1)
    Case “subm”
    Set h(h.Count) = b
    Set b = b.Controls.Add(msoControlPopup)
    Case “end subm”, “endsubm”
    Set b = h(h.Count – 1)
    h.Remove (h.Count – 1)
    Case Else
    Dim vbs As New MSScriptControl.ScriptControl
    vbs.Language = “VBScript”
    vbs.Reset
    vbs.AddObject “b”, b
    vbs.ExecuteStatement Replace(c.Value, “`”, Chr(34))
    End Select
    Next c
    End Function
    ===========
    put description on the sheet
    subm
    btn
    b.caption = `test1`
    b.faceid = 1
    endbtn
    btn
    b.caption = `test1`
    b.faceid = 1
    endbtn
    btn
    b.caption = `test1`
    b.faceid = 1
    endbtn
    btn
    b.caption = `test1`
    b.faceid = 1
    endbtn
    subm
    btn
    b.caption = `test1`
    b.faceid = 1
    endbtn
    btn
    b.caption = `test1`
    b.faceid = 1
    endbtn
    endsubm
    endsubm

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: