[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 921
  • Last Modified:

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
0
AndresHernando
Asked:
AndresHernando
  • 3
  • 3
2 Solutions
 
Rory ArchibaldCommented:
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
 
wobbledCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now