Macro userform – a good visual way to organize macros

At a certain point it can become challenging to quickly find and run the macro we need. Below are some options to organize our macros. I will focus on using a pop-up form because it’s the method I prefer, but at the bottom of the article, you can see other options.

A pop-up menu looks like this:

How to make your own Macro menu form:

Please see this video tutorial (full-screen or go to youtube):

[youtube=https://www.youtube.com/watch?v=sgMEMNJjrKY]

…or follow these instructions:

Open Visual Basic Editor
In the Project explorer, Right click on the workbook in which you would like to create the macro menu form (not the current workbook, but personal.xls, or wherever else you store your macros), and select Insert, and then** UserForm.**
This should create an empty template. Let’s fill the template! From the Toolbox, select a CommandButton
On the Userform template, drag the shape of a box, and this will create a button called CommandButton1.
Obviously, we want to rename this, so right click on CommandButton1, and select Properties.
Change the Caption to the name you would like to see, in this case “Test button”, and press Enter
Let’s write some code inside this button. Double click on **CommandButton1. **This should create a macro for the Click event (what will happen when we click this button which is what we want).
Make sure to add “userform1.hide” within the code in order to hide the form again… you may not need this, but I recommend it.
Add some code! You can add code directly to the click event, or if you already have your macros written you can “RUN” the macro from wherever it is without having to move it. For now let’s keep it simple, and just add a message box… modify the text so it looks like this:
```
Private Sub CommandButton1_Click()
MsgBox (“Hello world”)
End Sub
```
That’s it! Rinse and repeat steps 3-6 as many times as you want! Now all we need is to way to show and hide the menu.
In VBA Editor, right click on your file, and select Insert->Module. This should bring up a white sheet. Copy paste the following:
```
Sub Show_menu()
UserForm1.Show
End Sub
```
Lastly, create a button in the quicklaunch bar or custom menu to run this macro. In Excel 2007, right click on the quicklaunch bar, and in the Choose commands from: list, select: Macros.
Select the macro Show_menu from the list, then click the button ADD > > and then click OK (you can change the image of the button by clicking Modify and selecting what you want from there.
If you prefer a keyboard shortcut, you can also click on Macros from the Developer tab in Excel, find the macro Show_menu, and then click Options on the right. This will bring up a shortcut screen, where you can for example, press Shift+T (or whatever you like). Click OK, and then cancel on the macro menu.
You’re done! You can access your menu by either pressing Ctrl+Shift+T, or by clicking the button on the quicklaunch bar.

Other options:

Buttons: Use buttons on the quicklaunch bar or a custom toolbar. This is cool for a few macros, but after 10 or so macros, it gets a bit heavy. The buttons look like this:
Drop-down menu: Ron DeBruin created a “Menu for favorite macros in Excel 2007-2010 (for one workbook)” but it seems to not be operational anymore) through which you can create a drop-down list with the macros you so desire, in any hierarchy that you specify. It’s pretty great, if it works for you.