API Menu maker - for Userforms
Can Do
Goto Guest book sign in page [Home]
Thanks for visiting my site lucky visitor:
January 23, 2010
Copyright © 2002. XcelFiles. All Rights Reserved Ivan F Moala
Tell a friend about this page
Google
Search WWW Search My Site!
Creating Menus for userforms




When I 1st started using Excel one of the 1st routines I used was John Walkenbachs menumaker It is one I still use today and will continue to use this technique, so THANKS John. When John did this routine it was in response to a change in how Excel 97 handled menus via the new CommandBars. It also helped me to find a good pdf to excel converter, which can make life much easier.

Now with Microsoft Excel 2000 upwards, the introduction of the VB6 engine has given us the
Addressof Operator, which is crucial to getting the events for this type of menu, so this menumaker only applies to Xl2000 and above.

My good friend in Japan, Colo (MS Excel MVP) has a neat adaption of Johns original, have a look here, a must view.

Now I have adapted the technique to create a Menu for Userforms based along Johns work.
I have used other methods for making menus within a userform, BUT this method creates a true Windows Top level menu at the top via APIs ..........ones you come to expect to see on any
Windows Application i.e
File Edit Windows Help, usually accessible via Alt key and hovering over the items.
See image below..........................
If you have found this file useful then consider donating. Why Donate ?
How it works
This technique uses the same technique as John's i.e a table, which is stored in a worksheet. The image below shows the table. To create a custom userform menu, simply modify the data in the table.
The table above contains five columns:

  • Level: The "level" of the particular item. Valid values are 1 to 4.
    1 is for a Top Level menu (see below where File Paste options etc represent 1)




    2     is for a menu item i.e the drop down item of the Top Menu.
    3 is for a submenu item. Normally, you'll have one level 1 item, with level 2 items below it. A level 2 item may or may not have level 3 (submenus) items.
    4 Is a Sub menu of 3
    Note:
    0     indicates a Separator or Divider, DO NOT put any text in adjacent columns.


  • Menu name: The text that appears in the menu, menu item, or submenu. Use an ampersand (&) to specify a character that will be underlined and therefore activated via Alt key from the key board.


  • Sub routine to run: For level 1 items, There should be NO ROUTINE assigned. For level 2 or level 4 items, this will be the macro that is executed when the item is selected. If a level 2 item has one or more level 3 items, the level 2 item may not have a macro associated with it.


  • State: DO NOT Use or change (Automatically updates via formulas) just indicates end of Top level menu.


  • IDM Index: DO NOT Use or change. Used for Menu ID for running macros from the sub classed menu items within the userform.


So basically there is really only 3 columns you enter data in,
Level, Menu name and sub routine to run.
As there is limited error checking (I left that up to you) you will need to be careful how you assign....... experiment with continual SAVES


Using this technique

To use this technique in your own workbook or add-in, follow these general steps:

  • Download menumaker_UF_API.xls. This file contains the VBA code plus API's, and a worksheet named APIMNU.

  • Copy or drag & drop all 3 modules named;
    basAPIMNU
    basAPIMNU_Hook
    basAPIMNU_Loader


  • Copy the userform routines;
    UserForm_Initialize
    UserForm_QueryClose

  • Insert a new worksheet and name it APIMNU. Better yet, copy the APIMNU from the menumaker_UF_API.xls file.

  • Customize the API MenuSheet to correspond to your custom menu.


  • There is no error handling, so it's up to you to make sure that everything works.

Work around's

1) At this point in time the userform MUST BE non modal (I am looking @ a fix, as making the form vbModeless (Win Xp, XL2003, mouse - Genius net scroll) causes a loop in the mouse over event of the Menu area.

2) Adding controls etc - you need to to KEEP the form resize code IN other wise your userform will resize to accommodate the menu and distort the sizes of the controls. If you don't do this the repainting of the userform (done when the menu gets a message) causes it to repaint the userform to a reduced size. THIS IS DONE FOR YOU IN THE CODE. Just keep it in.

Updates:
Whilst I have done this menu using images as well, I have not made this available yet as there is a bit more work to do concerning the icons. You cannot use a standard size icons, unless you do userdrawn menus .... lets see :)
















Downloads

  • Download the template workbook. Use this as the template to build your useforms menus. Hopefully you will come to incorporate this into your routines for as long as I have enjoyed using Johns!





  • Download a fully worked Application (Userform) that has the controls etc. This particular userform simulates the Task manager (Ctrl + Alt + Del) BUT with the added benefit of being able to put the data into Excel.





menumaker_UF_API.zip
Testing
Testing done by

Ivan F Moala: - WinXP, XL2003, Xl2000

MS MVP Masuri Kaji (Colo): Office 2003 SP-1 on Windows 2000.
         Excel2000 on Windows XP and Excel2003

This page was created 12th march 2005