VBA code to select a value from a pre-defined Range Named List

[Roncondor]

I have a range name called "LoanOptions" which lists various loan options (surprise, surprise) e.g. Fixed, LIBOR, ARM, ..

Within a macro I want the user to be able to select which option they want. i.e. similar to a dropdown in regular Excel

I googled it but none of the answers relates to my problem.

Thanks!

 

[cerfani]

You can make a form with a combobox or insert a userform combobox on your spreadsheet

See if this helps... https://support.microsoft.com/en-us...obox-control-or-a-listbox-control-in-excel-or

 

[JoeMo]

If you want a VBA solution and don't want to invest the time in creating a userform, you could try something like this:

Sub PickAnOption()
Dim Options As Variant, Choice As String
Options = Array("FIXED", "LIBOR", "ARM") 'add options to suit using quote marks and a comma delimiter
startOver:
Choice = Application.InputBox("Ehter an option from the following list:" & vbNewLine & Join(Options, vbNewLine), Type:=2)
If Choice = "False" Then Exit Sub
For i = LBound(Options) To UBound(Options)
    If UCase(Choice) = UCase(Options(i)) Then  'Remove the Ucase from both sides of the "=" if you want the choice to be case sensitive
        Exit For
    ElseIf i = UBound(Options) Then
        MsgBox "Your entry must be one of these options:" & vbNewLine & Join(Options, vbNewLine)
        GoTo startOver
    End If
Next i
'do something with the Choice
End Sub

 

[JoeMo]

Here's a variation that uses the list of options from a vertical named range named "Options".

Sub PickAnOption()
'assumes options are in a vertical range named "Options"
Dim Options As Variant, Choice As String
Options = Range("Options").Value
startOver:
Choice = Application.InputBox("Ehter an option from the following list:" & vbNewLine & Join(Application.Transpose(Options), vbNewLine), Type:=2)
If Choice = "False" Then Exit Sub
For i = LBound(Options) To UBound(Options)
    If UCase(Choice) = UCase(Options(i, 1)) Then
        Exit For
    ElseIf i = UBound(Options) Then
        MsgBox "Your entry must be one of these options:" & vbNewLine & Join(Application.Transpose(Options), vbNewLine)
        GoTo startOver
    End If
Next i
'do something with the Choice
End Sub