Drop Buttons on VBA UserForm Controls

What Are Drop Buttons

Drop Buttons are buttons which are part of another control. For example, in a ComboBox, the Drop Button is a small button with an arrow; when clicked this Drop Button drops down a list of options for the user to select from.

Drop Buttons are very useful with Textboxes, where they add functionality which otherwise would require an additional button.

VBA UserForm Controls with Drop Buttons

Let’s see which UserForm controls have Drop Buttons, and what we can do with them.

ComboBox

The ComboBox is probably the most common and most obvious control that has a Drop Button. There it is at the rightmost end of the control, with a downward pointing arrow.

When you click the Drop Button, a list drops down, and you can select from this list.

RefEdit

Another control with a Drop Button is the RefEdit control. This is used in a dialog (UserForm) to allow a user to select a range of cells in the underlying worksheet.

The RefEdit’s Drop Button has a horizontal line on it; this is called the “Reduce” style (we’ll get to that shortly). When you click on the Drop Button, the dialog is reduced in size so it only shows the title of the dialog and the RefEdit; the Drop Button changes appearance as well. When you select a range, the range is highlighted with the marching ants, and the range address appears in the RefEdit.

When you click the Drop Button again, the dialog is restored to its original size. As long as the RefEdit has focus (the cursor is in the RefEdit), the range corresponding to the address in the RefEdit has the marching ants highlight.

RefEdit controls are very useful, as I’ve described in my post Using RefEdit Controls in Excel Dialogs. RefEdits can also be frustrating because they aren’t as stable as most UserForm controls. I discussed this in Unspecified but Painfully Frustrating Error. In my commercial add-ins I use a less elegant but more reliable approach to range selection, and I describe this approach in Alternative to Excel’s Flaky RefEdit Control.

Textbox

What isn’t obvious is that Textboxes also have Drop Buttons. You don’t see it in the default Textbox.

In a bit I’ll show how to use the Textbox’s Drop Button.

Drop Button Options

General Options

Let’s select a ComboBox and look at its properties in the VB Editor’s Properties Pane. We see two interesting properties that sound relevant: DropButtonStyle, which is set by default to fmDropButtonStyleArrow, and ShowDropButtonWhen, which is set by default to fmShowDropButtonWhenAlways.

We can select DropButtonStyle, and click on its drop button to see the options. In addition to fmDropButtonArrow, which is highlighted, we also have fmDropButtonPlain, fmDropButtonfmDropButtonEllipsis, and fmDropButtonReduce.

We can likewise select ShowDropButtonWhen, and click on its drop button to see the options. In addition to fmShowDropButtonWhenAlways, which is highlighted, we also have fmShowDropButtonWhenNever and fmShowDropButtonWhenFocus.

If we click on the RefEdit and look at its properties, we do not see DropButtonStyle or ShowDropButtonWhen.

The Textbox also does not show DropButtonStyle or ShowDropButtonWhen properties.

Since the ComboBox shows the DropButtonStyle and ShowDropButtonWhen properties in the Properties Pane, we can set them when we design the UserForm. We can also set them at runtime, using code like this:

me.cmbComboBox.DropButtonStyle = fmDropButtonStyleArrow
me.cmbComboBox.ShowDropButtonWhen = fmShowDropButtonWhenAlways

Of course, the code above merely restates the default properties of the ComboBox.

When we open the UserForm’s code module and select the ComboBox in the top left dropdown, we can see an event called DropButtonClick. You can insert code into this event procedure which will run whenever the Drop Button is clicked, in addition to the drop down action built into the combobox. Because the ComboBox already has a specific action when the Drop Button is clicked, this event is not obviously useful, although I suppose you could use it to populate the list of options displayed in the ComboBox.

The RefEdit also has a DropButtonClick event, which fires in addition the the RefEdit’s usual action that minimizes the form and allows selection of a range. As with the ComboBox, this event is not obviously useful in a RefEdit.

The textbox also has a DropButtonClick event. The DropButtonClick event can be very useful for a Textbox, because otherwise you would have to build another button separate from the Textbox, if you wanted similar functionality.

If I search for DropButtonStyle in the Object Browser, I can see the fmDropButtonStyle enumeration, as well as the DropButtonStyle property for ComboBox, RefEdit, and TextBox. Note that this property is grayed out for the RefEdit and Textbox: it’s still available, but I had to right click in the Object Browser and choose Show Hidden Members in order to display them.

I don’t know why we have ComboBox2 and TextBox2 in addition to ComboBox and TextBox. Often such duplicate objects represent newer versions of an older object, with advanced features, but I couldn’t find any new or changed members, and Google wouldn’t tell me anything either.

I can also search for ShowDropButtonWhen in the Object Browser. I see the fmShowDropButtonWhen enumeration, and the ShowDropButtonWhen property for ComboBox, RefEdit, and TextBox.

When I search for DropButtonClick, I find it for all three controls, not grayed out.

Exploring Drop Button Properties

I’ve created a small workbook, DropButtons.xlsm, which will help show the properties of Drop Buttons.

When you open the workbook, you’ll see half a dozen buttons on the worksheet. Click these to see various dialogs.

ComboBox

I’ve included the following code in the UserForm_Initialize procedure, to show the four different Drop Button styles in the form.

Private Sub UserForm_Initialize()
  With Me.cmbArrow
    .List = Array("Arrow")
    .Value = "Arrow"
    .DropButtonStyle = fmDropButtonStyleArrow
    .ShowDropButtonWhen = fmShowDropButtonWhenAlways
  End With
  With Me.cmbReduce
    .List = Array("Reduce")
    .Value = "Reduce"
    .DropButtonStyle = fmDropButtonStyleReduce
    .ShowDropButtonWhen = fmShowDropButtonWhenAlways
  End With
  With Me.cmbEllipsis
    .List = Array("Ellipsis")
    .Value = "Ellipsis"
    .DropButtonStyle = fmDropButtonStyleEllipsis
    .ShowDropButtonWhen = fmShowDropButtonWhenAlways
  End With
  With Me.cmbPlain
    .List = Array("Plain")
    .Value = "Plain"
    .DropButtonStyle = fmDropButtonStylePlain
    .ShowDropButtonWhen = fmShowDropButtonWhenAlways
  End With
End Sub

I won’t talk about the code beyond the above, but I’ll let you explore the code in the downloaded workbook.

Click the “Show ComboBox Drop Button Styles” button on the worksheet to display a form with four ComboBoxes, one for each style:

Style has no effect on functionality: clicking any of these Drop Buttons drops down the ComboBox’s list. Clicking the “Reduce” Drop Button does not reduce the dialog’s size the way clicking the Drop Button on a RefEdit would.

Click the “Interactive ComboBox Drop Button Styles” button on the worksheet to display a different form, which shows the defaults for the Drop Button. Open the UserForm’s code window to see how the code changes the Drop Button appearance.

Click the Drop Button to see a list of styles, and select the one you want to change to (for example, Reduce).

Now Reduce is displayed in the ComboBox, and the Reduce style is applied.

Change the Drop Button Show to Focus, and the Drop Button will only appear when the ComboBox has focus. It’s not showing now, because the Focus option button has focus.

Select Never, and the Drop Button does not appear even if the ComboBox has focus.

Why would you use a setting of Never for a ComboBox? Perhaps you’ve designed your dialog so that the ComboBox option is selected early, and once selected you want it locked in. Set it to Never in code after the selection has been made, and the user can no longer access the options. The user can still type an entry in the ComboBox, but will probably not think of doing that.

RefEdit

Click the “Show RefEdit Drop Button Styles” button on the worksheet to display a form with four RefEdits. Code similar to that above applies the four styles.

Again, style does not affect functionality. Clicking any of these Drop Buttons will reduce the size of the dialog, the way RefEdits do.

Click the “Interactive RefEdit Drop Button Styles” button on the worksheet to display a different form, which lets you adjust the style and display of the RefEdit’s Drop Button.

TextBox

Click the “Show Textbox Drop Button Styles” button on the worksheet to display a textbox-loaded form with all four Drop Button styles

I have programmed simple DropButtonClick event procedures: Clicking a button shows a MessageBox that tells you which Drop Button was clicked. Here is how it looks after I click the Arrow Textbox’s Drop Button:

Click the “Interactive Textbox Drop Button Styles” button on the worksheet to display a new form. Note that the default Drop Button is never shown.

The default is Arrow, even though it is never shown. Selecting Arrow doesn’t change this.

But changing to Always makes the arrow appear.

Play around with these dialogs to see what the possibilities are.

Drop Button Settings in Practice

Your first intention will be to not confuse your users. Because of this you should probably never use a ComboBox with any style but fmDropDownStyleArrow or a RefEdit with any style but fmDropButtonStyleReduce.

You can certainly change the ShowWhen property of either to fmShowDropButtonWhenFocus, so the button is only visible when the control has focus. This may help reduce the cluttered appearance of your dialogs. You can also use fm ShowDropButtonWhenNever when you want to prevent the user from clicking the Drop Button.

The magic of Drop Buttons is most evident with Textboxes. Here are two ways I use Textbox Drop Buttons.

RefEdit Workaround

I mentioned that I use an Alternative to Excel’s Flaky RefEdit Control. This consists of a Textbox that contains the address, with a Drop Button using fmDropButtonStyleReduce, so the Textbox looks like a RefEdit. The code in the UserForm has various range and address validation code, which is normally built into the RefEdit.

I use this approach in all of the dialogs in Peltier Tech Charts for Excel that require the user to select a range. Here is the Waterfall Chart dialog. The first control, right below the descriptive text, is a regular Textbox with a “Reduce” style Drop Button.

File or Directory Path

If I show a file or directory path on a dialog, I use a textbox, and I put an “Ellipsis” Drop Button. Clicking this Ellipsis runs code to bring up a File Dialog for selecting a different path or filename.

The dialog below is the front end of an add-in I wrote to help me manage all of my other add-ins. It lets me select a project, then it displays all of the various VB components. I can select the ones I want to export. The bottom control, just above the Export and Done buttons, is a Textbox that displays with an Ellipsis Drop Button. I can click the Drop Button to select a different path for the exported files.

I’ll write about this VB Component Exporter in a future blog post, and maybe share it with my readers.