SelectBox User Interface

A SelectBox is an User Interface that prompts the user of your spreadsheet to select one or more items from a list, triggering automation based on the user’s selection(s).

Use Cases:

SelectBox Macros allow the end-user of your spreadsheet to perform certain automations.

• Easily “Jump” to different ranges or sheets in your workbook.
• Quickly unhide (or hide) certain worksheets
• Create a tool to easily save, save as PDF, and/or email your worksheets.
• Create user interface to allow users to select any options and integrate into your VBA code.

SelectBox Builder – How to

SelectBoxes are not included with Excel, but with AutoMacro you can easily create these professional user interfaces.

The SelectBox Builder is found under the UI menu:

To create the form, first enter some basic information:

1. Enter the title
2. Enter the text prompt
3. (Optional) Enter the VBA UserForm name (this is only used in the VBA Code)
4. Select if the user is allowed to select multiple items.

Next, select what items the user will see:

5. Specify the type of list to display to the user. Notice that after selecting Worksheets, the SelectBox auto-populates with all the worksheets in the workbook. (We will discuss the other options later in this tutorial)
6. Specify any items to exclude from the list.
   Here you can add worksheets to the Exclude List and they will be removed from the SelectBox.

7. Select which (if any) worksheet should be the default option.
8. Define the Action to perform on the worksheets:

Click Insert Macro and you’ll see the Insert Macro menu.

Adjust the button name if desired.

Click Insert Macro and a button will be added to your worksheet to trigger the SelectBox.

Named Ranges

The Named Range option looks almost identical to worksheets.  However, you’ll notice that the only available action is to Go To the named range.

This is a great way to quickly jump to different sections of your workbook.

Comma Separated List

With the Comma Separated List, you must manually create a list, by using commas to separate values:

However, no action can be selected. In order to apply actions, you must manually add this to your code.

From Range

You can populate the SelectBox from a Range:

However, just like with the Comma Separate List, no action can be selected. In order to apply actions, you must manually edit the VBA Code.

Start Automating Excel

Get AutoMacro