CODEWITHSUNDEEP
vbaexcel
Sam
Sam

Reputation: 185

ComboBox inputbox causes reselection of range

Currently I'm working on a userform prompting an inputbox for a range whenever the combobox dropdown button is clicked.

The problem is that whenever the range is selected (selecting a cell and then clicking ok), the userform is unselected (greys out), shows an empty dropdown list and forces me to reselect a range after I click anywhere on the workbook.

Is there any way to prevent a re selection of a range when clicking the dropdown button?

enter image description here enter image description here enter image description here

Code Below:

Private Sub ComboBox1_DropButtonClick()
    Dim InputCell As Range
    Set InputCell = Application.InputBox("Select Lookup Cell", "Obtain Object Range", Type:=8)
    ComboBox1.Text = InputCell.Address(0, 0, external:=True)
End Sub

Upvotes: 1

Views: 137

Answers (2)

Profex
Profex

Reputation: 1390

When I've used the RefEdit object in the past, I used the real thing (which I know has it's issues) and just used the Change event.

Perhaps you could just use the Enter event (you just have to ignore it the first time, if's it the first object that gets focus when initialized)

Resources:

Using RefEdit Controls in Excel Dialogs - Jon Peltier

RefEdits must be placed directly on the UserForm itself. If you put a RefEdit in a frame or on a multipage, strange things will happen, including bizarre Excel crashes.

RefEdits must not be used on modeless forms. RefEdits on modeless forms will result in bizarre Excel crashes.

RefEdit event procedures should be avoided. RefEdit events do not behave reliably, and they may result in VBA errors which are difficult to debug.

References to RefEdits must be removed. When a UserForm is added to a VB project, Excel adds a reference to the Microsoft Forms 2.0 Object Library. This reference is required for proper operation of the UserForms in your project. To see what references your project has, select the project in the Project Explorer, then select References from the Tools menu.

Alternative to Excel’s Flaky RefEdit Control - Jon Peltier

The new approach uses a TextBox in the dialog instead of the RefEdit.

The TextBox does not interact directly with a range as does the RefEdit. Instead, when this drop button is clicked, the dialog is temporarily hidden, and an InputBox appears to solicit the user’s input.

"It may be tempting to use type 8 to indicate a range object, but there is an obscure glitch that causes it to fail when the worksheet contains conditional formatting conditions that use formulas in their definitions. Fortunately using Type 0 to specify a formula works just fine."

Cannot use keyboard shortcuts to select ranges in RefEdit control in Excel

Upvotes: 1

Sam
Sam

Reputation: 185

From this source: Ozgrid

Though I'll be using custom text boxes, this could also work if anyone wants to use the combo boxes.

Private Sub ComboBox1_DropButtonClick()
    Static Abort As Boolean

    If Abort Then Exit Sub
    Abort = False

    Dim InputCell As Range
    Set InputCell = Application.InputBox("Select Lookup Cell", "Obtain Object Range", Type:=8)

    ComboBox1.Text = InputCell.Address(0, 0, external:=True)

    Abort = True
End Sub

Upvotes: 0

Related Questions