Ellipsis Textbox for VBA Userform File Select

I am trying to create a path selection user interface for an extensive VBA program I've been working on, but I can't seem to get the ellipsis textbox that I'd like. This is a very common feature, especially in option tables. This is an example of what I'd like to get, straight from the VBA Options panel:

I would LOVE to find a way to get the same functionality in a Userform. The only solution that I've found thus far is to use a combo box with the ellipsis arrow option enabled. However, there doesn't seem to be an apparent way to use the activation of the combo box arrow to run a dialog box, nor does there seem to be a way to make it look UNLIKE a combo box. Last resort I use a button below the text box, but I'd really prefer a less-bulky way of doing this.

Any solution would be greatly appreciated.

Answers


The only solution that I've found thus far is to use a combo box with the ellipsis arrow option enabled. However, there doesn't seem to be an apparent way to use the activation of the combo box arrow to run a dialog box, nor does there seem to be a way to make it look UNLIKE a combo box

Your suggestion does work, and it is surely less complex and more elegant than having two controls work together, Button + Textbox.

A Combo can achieve perfectly the desired feature, in the following way.

1) In design mode, set the button style to Ellipsis

         DropButtonStyle = fmDropButtonStyleEllipsis

And eventually, make the ellipsis show up only when the combo is entered, by setting the design-time property:

         ShowDropButtonWhen = ShowDropButtonWhenFocus

2) If needed, you can set other design-time properties to have some look and feel. The defaults look pretty good however.

3) Add the following handler to the parent userform. The snippet simulates the launching of a dialog and getting a new value or cancelling. It does not show any dropdown window. (but you still have control over that: if you want to show it according to some condition, you still can call the method ComboBox1.DropDown)

Private Sub ComboBox1_DropButtonClick()
    ' The following two lines avoid to call the routine twice, at entry and at exit
    Static i As Integer
    i = (i + 1) Mod 2: If i = 0 Then Exit Sub

    With ComboBox1
        s = InputBox("enter some text", , .Value) '<~~ simulates any dialog
        If s <> "" Then .Value = s
        SendKeys ("{Enter}") '<~~ to close immediately the dropdown window
    End With
End Sub

Try it ;)


Not only do ComboBoxes have Drop Buttons, so do TextBoxes (as do Excel's RefEdit controls). Even though you can't access the Textbox's Drop Button at design time, you can do so at runtime. Using a textbox avoids having to deal with the dropped down list of a combobox.

Given a textbox named TextBox1, the following code will provide the desired ellipsis drop button:

Private Sub UserForm_Initialize()
  With Me.TextBox1
    .DropButtonStyle = fmDropButtonStyleEllipsis
    .ShowDropButtonWhen = fmShowDropButtonWhenAlways
  End With
End Sub

Then use the DropButtonClick event of the textbox to perform whatever action you want:

Private Sub TextBox1_DropButtonClick()
  '' Code here to do what you need
End Sub

I have an extensive example at Alternative to Excel’s Flaky RefEdit Control that uses a textbox with a "Reduce" drop button to replicate the functionality of Excel's unreliable RefEdit controls.


Need Your Help

Create a div #map-canvas javascript

javascript jquery html google-maps

i have a page with a google map container, when click a link the map container change to slider with ajax, i need click another button to show again the map i try with this code:

Microsoft Edge easyXDM on("message") event not being called

javascript ajax cors microsoft-edge easyxdm

In Microsoft Edge, a GET request is not running. I have stepped through the code to the point of the AJAX request being run, and set a breakpoint in the callback(s). However, the code never reaches...