Input Prompt With Options

15,850

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:

  1. You must create a userform named formComboInput with a combo box, label, and button etc.

  2. Create a public integer variable in the form's code called ReturnVal.

  3. Assign the value of -1 to ReturnVal in the formComboInput.InialiseForm sub, and populate the combobox in that sub too.

  4. 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.

Share:
15,850

Related videos on Youtube

VikkyB
Author by

VikkyB

Updated on September 14, 2022

Comments

  • VikkyB
    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
      Andy G over 10 years
      What 'input prompt' and 'input string'? What feature are you using: Data Validation, or some form of combobox? You need to clarify your question.