A menu on a userform

Posted on by jkpieterse

Hi everyone,

I’ve been fooling around trying to get a tabledriven menu for a
userform to work.

It uses a table on a worksheet to build the menu, like in this screenshot:

Obviously I used a couple of things from Stephen Bullen, Rob Bovey and John Green’s famous book “Professional Excel Development” to build this.

I’m almost there I believe (download here).

I’d like to ask you all:

– comments (I might have a hole or two in the logic and in the
termination cleaning up of the class instances)
– A tip on how to get shortcut keys working.

Regards,

Jan Karel Pieterse

Posted in Uncategorized

24 thoughts on “A menu on a userform”

  1. I imagine you have your reasons for doing what you are doing, but an easier approach that would work if you can show the userform modeless would be to use a commandbar. Create one, show it so that it appears adjacent to the userform, make it immobile, and you are all set. Obviously, if the userform is moved, move the commandbar.

    [From a quick test, it looks like XL/Office uses different measurement systems for userforms and commandbars. Since the userform coordinates and the commandbar coordinates are not the same scale you will need some kind of conversion, probably point-to-from-pixel.]

  2. “Create one, show it so that it appears adjacent to the userform, make it immobile, and you are all set. Obviously, if the userform is moved, move the commandbar.”

    I wonder what would happen if a commandbar’s window was parented to the form’s window with SetParent…

  3. Tushar:

    Always good to have remarks like these. Good idea too.

    Reasons:

    1. I want some sort of menu with the form.
    2. I wanted to do the excercise to create the menu on the fly, by adding controls at runtime, including event handlers. Seems to work just fine.

    Stephen:

    If you could get that to work, it would be most interesting.

  4. Hi all,

    On my website http://rtsoftwaredevelopment.de you will find a way to add a ‘real menu’ to a userform (see ‘Beispiele->Userform mit Min/MaxButton’). Description is only in german, but code should be clear. In the example, the menu is hardcoded, but it should be no problem to create the menu dynamical.

    Regards,

    Thomas Risi

  5. Hi Jan,

    it is very useful solution, I guess. I can imagine lot of possibilities, how make my system of userforms more userfriendly. If I can, I would just beg for adding columns in your source worksheet, where I can fill in the name of procedure, that will be triggered by pressing specific menu item.

    By the way, if anybody interested in, I uploaded my new tool for “rapid menu development”
    based on famous MenuMaker of John Walkenbach to my web

    http://www.dataspectrum.cz/pages/eng_files/engfilesmain.htm

    I have added some other features like facing buttons with own images,
    adding comboboxes and/or manipulating default main menu items.

    Jiri Cihar,Dataspectrum

  9. Sorry I should have added this to my above post:

    Jan your methods really nice, it had a sort of Java feel about it.

    As for the commard bar option, I think Dick posted here about possible ways to do this, I did have an example file of my site, but it seems to have stoped working now! I dont quite get why you would want to so tightly control the command bar?

  12. # jkpieterse Says:
    March 6th, 2006 at 12:09 pm

    Tushar: Curious: what “event” would you use to discern whether the userform has been moved?

    Hi JKP:

    In the Excel Calculator (http://www.tushar-mehta.com/excel/software/utilities/xlcalculator.html) I show a userform without the title bar and then use code to move the userform in response to user mouse movements. The code is at: http://tmc.newsgrouphosting.com/readmessage?id=%3C4ccd21e$10b5de63$bc91@mps%3E&group=tmc.blogposts

  13. Aside from the ‘Everest’ argument (“Because it’s there”), why put a menu on a userform?

    Maybe there is a good reason design why there is no menu component available for userforms: only the primary window should have a menu and in an Excel application the primary window is the Excel application (note it already has a menu :)). You can hide the Excel application of course and make your userform behave as the primary window but that would beg the question, ‘Why build this application in Excel?’

    Jamie.

    –

  14. Just another wild idea, but how about using a simple label control for the top-level ‘menu’ and when clicking on it show a popup commandbar?

  15. “Just another wild idea, but how about using a simple label control for the top-level ‘menu’ and when clicking on it show a popup commandbar?”

    I think that is what Dick suggested, when he blog about it here, it’s also what i used in my example (this did work, but is broken now ?!?!, I have also improved the “tool bar” idea)
    http://www.methodsinexcel.co.uk/Downloads/MIE_Toolbars_And_MenuBars.zip
    for new example toolbar see this post:
    http://www.blog.methodsinexcel.co.uk/2006/03/06/spell-checking-in-user-forms/

    I would go a fix it, but with Ivan’s work it hardly seems woth it? No?

    John, I was thinking, when I first saw office 12, that someone (or a workgroup) could use the API method to completely replace the ribbions bar – if you could hide the ribbions that is. Not sure what you would do about toolbars anyway? – well it might not be worth it, but it would be possible.

  16. Hi all,

    With all the respect to the “can do”-approach I fully agree with Jamie. Despite the issues with some events-driven procedures in classic VB I would simple set up a COM add-in instead of trying to do it with Excel.

    Of course, I fully understand that there is a point to challenge it with Excel ;)

    BTW, those of You who run Excel 12 have You experienced any issues with COM add-ins developed with classic VB?

    Kind regards,
    Dennis

  17. Hi Dennis,

    I guess that depends what you mean by ‘issues’. In general, they work fine. There are some issues with commandbars disappearing, but I think they’re redraw/rebuild bugs rather than big issues. FWIW, I’ve written a COM Addin in VB6 to exercise both the new RibbonX and Custom Task Panes and they both work well. I haven’t looked at the nuances of Commandbar support (e.g. demand-loaded commandbars). What issues were you thinking of?

  18. Stephen,

    “FWIW, I’ve written a COM Addin in VB6 to exercise both the new RibbonX and Custom Task Panes and they both work well”

    Sounds good to me.

    What I have in mind is:

    – Implement VB6 forms as native Excel userforms.

    – Present solutions to handle events like Combobox_Change etc.

    Kind regards,
    Dennis

  19. Why a menu?

    Sometimes it is just a handy interface. The background is the Name Manager. I got feedback in Dennis’ forum about the buttons with icons being hard to memorize. I can agree with that.

    Since there are so many commands on that thing, I thought to create a menu on it.

    I haven’t implemented this on NM yet, I was first trying to get a generic way to set one up, so I can throw in a couple of class modules and some init subs in the form along with a table on a worksheet to get it working on any userform.

    It seems to be working (one level deep though, not two or more).

    I have the “Onaction” thing lined up, I just need to implement it.

    I thought about the popup commandbar, but liked this challenge better. It was a good puzzle to solve and at the same time I got the opportunity to excercise with class modules, collections and central event handling. All in one small project. So there you go. It IS sort of a “do because I can” project, but I suspect it might come in handy too, because it is a good excercise in how to add controls on the fly, *including* their event code.

  20. One advantage of the userform menu might be that it allows for easier updating, along the same lines/reason for using a none com based addin in the first place. What about if you dont have a compilable programming platform on you pc?

  21. Hi Tushar and Dennis. I haven’t spoken with you guys in several years! I hope all is well. You may remember me as tstom or right_click on mrexcel.com. Anyway, I ran across this looking for my file as I did not remember what forum I posted it in. I actually found it on my computer. Anyway, rather it’s relevant at this point or not, here is an example of anchoring a built in and custom commandbar within a userform…

    http://cid-ea73b3a00e16f94f.skydrive.live.com/self.aspx/Mr%20Excel%20Example/AnchorCommandBarInUserFormv2.zip

  22. Hi Friends,

    I need to place custom button at titlebar of userform. At this time when I click on custom button it show me only “hello world”

    Your help will be higherly appericiated.

    Thanks in advance…

    Xtream..

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.