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.
