asked on

Excel VBA - Input box with dropdown of integers 1 to 150

How can I generate a user input box that has a dropdown for the user to select his choice (the dropdown choices will be integers 1 to 150 that could be in a separate table if need be).

Thanks, --Andres

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

wobbled

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wobbled

sorry - fast asleep today. I posted the above with a combo box rather than a text box.

Ignore the code in the last post, what I meant to say was use a Text box on the form to allow users to add their number and then do a check on that.

Private Sub CommandButton1_Click()

    If IsNumeric(Me.TextBox1.Value) Then
        If Me.TextBox1.Value >= 0 And Me.TextBox1 <= 150 Then
            'do whatever it is you want to do
        Else
            MsgBox "The value of " & Me.TextBox1.Value & " is outside the range of 0 to 150", vbCritical, "Error found"
        End If
    Else
        MsgBox "The value entered in the box must be a numberic number between 0 and 150", vbInformation, "Error found"
    End If

End Sub

Open in new window

Rory Archibald

Given that a combobox automatically matches as you type I can't honestly see any benefit whatsoever in using a textbox rather than a combo?

wobbled

Fair point on the combobox though most users still tend to use the mouse to scroll down value they want still. Hopefully this trend is changing with dynamic search boxes becoming the norm on webpages, where people are used to just entering a few characters and it jumps to the matching values etc. That said some of my users still appear to be struggling to work a mouse :)

Rory Archibald

Well, if you want you can simply hide the dropdown? That way you still get the auto match.