Input Prompt With Options
If you mean using the InputBox() function:
Answer: no you can't. InputBox is built into VBA and can't be modified.
However you CAN write your own InputBox. Do to this:
You must create a userform named formComboInput with a combo box, label, and button etc.
Create a public integer variable in the form's code called ReturnVal.
Assign the value of -1 to ReturnVal in the formComboInput.InialiseForm sub, and populate the combobox in that sub too.
In the userform button click code, assign value of formComboInput.comboInput.ListIndex to ReturnVal, and hide the form.
When the form loads, populate the combobox with a subroutine such as InitialiseForm(). You can store combo box ranges in a separate sheet or in static arrays.
Then insert code similar to below (untested sorry):
' User form code:
Option Explicit
public ReturnVal as integer
sub InitialiseForm()
dim i as integer
ReturnVal = -1
comboInput.Clear
for i = 1 to ThisWorkbook.Worksheets.Count ' Populates combobox
comboInput.AddItem ThisWorkbook.Worksheets(i).Name
next
end sub
btnOK_Click()
ReturnVal = comboInput.ListIndex ' Change ReturnVal from -1 to the listbox index
Me.Hide
End Sub
' Module/sheet code:
Option Explicit
function ShowComboInput(InputBoxCaption as String, DefaultResult as String) as String
with formComboInput
.InitialiseForm() ' Make the combo box populate etc
.labelCaption = InputBoxCaption
.Show vbModal ' CODE EXECUTION PAUSES HERE UNTIL FORM IS CLOSED
if .ReturnVal > -1 then
ShowComboInput = .comboInput.Value ' Returned if user clicks OK button
else
ShowComboInput = DefaultResult ' Returned if user closes form
end if
end with
end function
sub InputBoxExample() ' Call this sub to test the above code
MsgBox ShowComboInput("Testing", "User didn't click OK button!")
end sub
This code is untested so may need some tweaking but in general this is how I would implement a custom input box.
Related videos on Youtube
VikkyB
Updated on September 14, 2022Comments
-
VikkyB over 1 year
Is there a way to produce input prompt having multiple options in the prompt itself. User can choose any one of the option provided. As of now I am doing it using 'Input string' and then setting the value of variable. Example: Which sheet to choose? Sheet1? Sheet2? Sheet3?
-
Andy G over 10 yearsWhat 'input prompt' and 'input string'? What feature are you using: Data Validation, or some form of combobox? You need to clarify your question.
-