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
AndresHernandoAsked:
Who is Participating?
 
Rory ArchibaldConnect With a Mentor Commented:
You cannot have a dropdown on an inputbox per se - you would have to create a userform, add a combobox to it and then use code like:
Private Sub UserForm_Initialize()
   Dim n As Long
   For n = 1 To 150
      Me.ComboBox1.AddItem n
   Next n
End Sub

Open in new window

0
 
wobbledConnect With a Mentor Commented:
I would recommend that you don't put 150 options in a drop down box as it will be difficult to scroll to the value you want.  Instead just put in the label the range that you want people to use and then just put some error handling in to check if the value is outside of that.

If you put a command button on your userform and using the names that rorya suggested here is some code to do that
Private Sub CommandButton1_Click()

    If IsNumeric(Me.ComboBox1.Value) Then
        If Me.ComboBox1.Value >= 0 And Me.ComboBox1 <= 150 Then
            'do whatever it is you want to do
        Else
            MsgBox "The value of " & Me.ComboBox1.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

0
 
wobbledCommented:
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

0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Rory ArchibaldCommented:
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?
0
 
wobbledCommented:
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 :)

0
 
Rory ArchibaldCommented:
Well, if you want you can simply hide the dropdown? That way you still get the auto match.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.