Input Box Question

[andremayer]

I have created this input box which works great, exept, the person using would type in what the input box is asking as information. Which is fine, but what I would rather have is the input box to pop up, and then instead of typing in the information, I want to be able to choose from a drop down list, of what information is required in that field, also, it would help with spelling errors and so on..

I don't know if I explained myself correctly, but I hope I did and I hope someone can help!

Thank you so much!

 

[GlennUK]

And what are you going to be doing with choice that has been made from the drop-down list?

 

[andremayer]

I want them to choose a time slot per 15minutes for one, and a store name for another.

 

[GlennUK]

I want them to choose a time slot per 15minutes for one, and a store name for another.

Yeah, that's still just saying what you want them to choose. What are you going to be doing with the choices after they have been made?

If you are doing this just to allow the users to choose values that you are just going to put into cells, then you could use Data Validation ... but I don't know what you are going to be doing.

 

[mikerickson]

Are you open to invoking a userform rather than an Input Box?
I have a "ListForm" userform on my home computer, if that will suit your needs.

Alternatly, you could create a named range ListOfOptions (with the options listed) and use code like this

Sub test()
    Dim startFrom As Range
    Dim userInput As Variant
    
    Set startFrom = Selection
    
    Application.Goto Range("ListOfOptions"), True
    
    Do
        userInput = Application.InputBox("Select from the List", Type:=7)
        If userInput = False Then Exit Sub: Rem cancel
    Loop While IsError(Application.Match(userInput, Range("ListOfOptions"), 0))
    
    Application.Goto startFrom, True
    
    MsgBox userInput & " was selected."
End Sub

Note that, while the InputBox is visible, the user can click on the appropriate cell to enter that value.

 

[mikerickson]

Here is that listform.
Create a userform, name it Choose.
It should have two buttons, named butCancel and butOK.
It should have a label named Label1 and a list box named ListBox1.

Put this code in its code module.

Option Explicit

Private Sub butClose_Click()
    Unload Me
End Sub

Private Sub butOK_Click()
    Me.Tag = "x"
    Me.Hide
End Sub

Function FromList(chooseFrom As Variant, _
                            Optional Prompt As String, _
                            Optional Title As String = "Choose from list", _
                            Optional Default As Variant = "", _
                            Optional MultiSelect As Boolean = False, _
                            Optional Delimiter As String = ",") As String
    Dim oneItem As Variant
    Dim returnString As String
    With Me
        .Caption = Title
        .lblPrompt.Caption = Prompt
        With .Label1
            With .Font
                .Size = Me.ListBox1.Font.Size
                .Name = Me.ListBox1.Font.Name
            End With
        End With
    End With
    If TypeName(chooseFrom) = "Range " Then
        With chooseFrom
            chooseFrom = IIf(.Cells.Count = 1, Array(.Value), .Value)
        End With
    End If
    
    With Me.ListBox1
        For Each oneItem In chooseFrom
            Rem put in listbox
            .AddItem CStr(oneItem)
            Rem add line to dummy label
            Label1.Caption = Label1.Caption & vbCr & CStr(oneItem)
        Next oneItem
        
        Label1.Caption = Mid(Label1.Caption, 2)
        .MultiSelect = IIf(MultiSelect, fmMultiSelectMulti, fmMultiSelectSingle)
        
        If MultiSelect Then
            If Not TypeName(Default) Like "*()" Then
                If Not Default = vbNullString Then
                Default = Array(Default)
                End If
            End If
            For oneItem = 0 To .ListCount - 1
                If IsNumeric(Application.Match(.List(oneItem), Default, 0)) Then
                    .Selected(oneItem) = True
                End If
            Next oneItem
        Else
            On Error Resume Next
            .Value = Default
            On Error GoTo 0
        End If
    End With
    
    Me.Show
    
        With Choose
            With .ListBox1
                If MultiSelect Then
                    Rem multiselect
                    If .Parent.Tag = vbNullString Then
                        Rem canceled
                    Else
                        returnString = "x"
                        For oneItem = 1 To .ListCount
                            If .Selected(oneItem - 1) Then
                                returnString = returnString & Delimiter & .List(oneItem - 1)
                            End If
                        Next oneItem
                        returnString = Mid(returnString, 2)
                        If returnString <> vbNullString Then
                            returnString = Mid(returnString, Len(Delimiter) + 1)
                        End If
                    End If
                    'FromList = returnString
                Else
                    Rem single select
                    If .ListIndex > -1 Then
                        returnString = ListBox1.Value
                    End If
                    'FromList = returnString
                End If
                FromList = returnString
            End With
        End With
    
    Unload Choose
End Function

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Me.Hide
End Sub

Private Sub UserForm_Activate()
    Rem size and positioning
    
    With lblPrompt
        .WordWrap = False
        .AutoSize = True
        .AutoSize = False
        If .Width > 300 Then
            .Width = 370
            .WordWrap = True
            .AutoSize = True
            .AutoSize = False
        End If
        .BackColor = Me.BackColor
        If .Width < 202 Then .Width = 202
    End With
    
    With ListBox1
        Rem size matches auto-size dummy label
        ListBox1.Height = Label1.Height + 3
        ListBox1.Width = Label1.Width + 80
        ListBox1.Height = Application.Min(ListBox1.Height, 200)
        Rem postion matches prompt
        .Left = lblPrompt.Left
        .Top = lblPrompt.Top + lblPrompt.Height + 12
    End With
    
    With butOK
        .Left = lblPrompt.Left + lblPrompt.Width - .Width
        .Top = ListBox1.Top + ListBox1.Height + 20
    End With
    With butClose
        .Left = butOK.Left - 80
        .Top = butOK.Top
    End With
    
    With Me
        .Width = butOK.Left + butOK.Width + 20
        .Height = butOK.Top + butOK.Height + 23 + 20
        .Repaint
    End With
End Sub

Private Sub UserForm_Initialize()
    With Me.Label1
        .WordWrap = False
        .AutoSize = True
        .Visible = False: Rem alter
        .Caption = vbNullString
    End With
End Sub

It is used like this

Sub test()
    Dim retString As String
    
    retString = Choose.FromList(Range("A1:A8"), "Choose something from the list", _
        MultiSelect:=False)
        
    'retString = Choose.FromList(Array("alpha", "beta", "gamma"), "choose")
    
    If StrPtr(retString) = 0 Then
        MsgBox "Cancel pressed"
    Else
        If retString = "" Then
            MsgBox "vbNullString was entered."
        Else
            MsgBox retString
        End If
    End If
End Sub

Note that it can accept either a range or an explicit array as its ChooseFrom argument.