Excel VBA use Inputbox to select options

This Excel tutorial explains how to use VBA Inputbox to select options using SELECT CASE.

Excel VBA use Inputbox to select options

In my previous post, I demonstrated how to use VBA Inputbox to input a value and create Msgbox to return the value. In this post I will create an Inputbox for users to select options with the help of Select Case.

In the below example, I create three options in the Inputbox for users to select:

1. Mary

2. May

3. Susan

If users select something else, an error message box will pop up.

Press ALT+F11, copy and paste the below Procedure in a new Module.

Public Sub inputbox_selection()
    sinput = Application.InputBox("Please select 1 to 3" & vbLf & vbLf & "1. Mary" & vbLf & "2. May" & vbLf & "3. Susan", "Enter a value", 1)
    If sinput = False Then
        Exit Sub
    Else
        Select Case sinput
            Case 1
                MsgBox ("you have selected 1")   'change to your desired action for each case
            Case 2
                MsgBox ("you have selected 2")
            Case 3
                MsgBox ("you have selected 3")
            Case Else
                dummy = MsgBox("wrong input", vbCritical)
        End Select
    End If
End Sub

To explain briefly, vbLF represents line feed, the purpose is to show only one option in each line. Case Else is for exception handling. Please read my previous post if you want to know the argument of Inputbox.

Run the above Procedure, an Inputbox is popped up, default value is set to 1. Click OK.

 

A MsgBox returns your selection.

 

If you type a value other than 1 to 3, an error Msgbox will pop up.

 

 

Wyman W
Wyman is a Human Resources professional based in Hong Kong, specialized in business analysis, project management, data transformation with Access and Excel.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *