Selection list within a user prompt

[Jeff-Q]

I'm writing a fairly basic macro to prompt a user for input. Several fields are straight forward and simply require a title to be entered. In another cell, however, I'd like to prompt the user to select from a list (which is currently set up as a validation list). Does anyone know if this is possible from an InputBox or perhaps some other VBA dialog prompt?

i.e., the simple prompt that I've specified is:

Title = Application.InputBox("Enter Song Title")
ActiveCell.FormulaR1C1 = Title

I want another prompt to have a user select a predefined list of themes.

Thanks for any help.

Jeff

 

[Joe Was]

I have done this two ways:

Build a UserForm containing a ListBox that is populated from a sheet containing the Titles available. The user just selects from the list.

Or

Use the InPutBox Function and format the massage like:


Enter the Number of your selection below:

1 xyz
2 abc
3 123

4 Exit


Then make the default option 4.

Use the Select Case structure to work with the selection.

 

[Jeff-Q]

Thanks for the prompt reply Joe.

Some selections will be from an extensive list, so your first option appears to be the one to use. Would you mind providing a sample of your suggested code? I'm hoping to provide a selectable drop down list on the actual prompt dialog. I've currently got the cell formatted with an optional, selectable list (by means of Data Validation List). Rather than have a user click to the actual cell and then make a selection from a list box, I wish to have a macro prompt the user to make a selection.

Thanks again for your help.

 

[Joe Was]

Insert a UserForm from the VBE Editor: Insert - UserForm
Add a ComboBox from the ToolBox

Right-Click the UserForm and copy this code:

Private Sub UserForm_Initialize()
Dim RngTags As Range, RngNames As Range, i As Long

Set Rng1 = Sheets("Sheet1").Range("myNames")

With ComboBox1
.ColumnCount = 1
.Style = fmStyleDropDownList
.TextAlign = fmTextAlignCenter
.BoundColumn = 1

For i = 1 To Rng1.Count
.AddItem Rng1(i).Value
.List(.ListCount - 1, 1) = Rng1(i).Value
Next i

End With
End Sub


Private Sub ComboBox1_Change()
Sheets("Sheet1").Range("D1").Value = ComboBox1.Value
End Sub


The UserForm_Initialize Sub works with a named range (myNames) that is the list of Titles that you have loaded into a table on a sheet.

The ComboBox1_Change event code takes the selected value and loads it into cell D1, you can change this to load a varaible with the selection to use anywhere in other code if you define it outside the event or just work with it in that event.

To run the userform use:

Sub mySForm()
UserForm1.Show
End Sub

With a button or whatever.

 

[Jeff-Q]

Thanks. That works well. This is a little beyond the more basic VBA code Writing that I'm familiar with. The only remaining issue is that the selection dialog stays open when I make my selection. Can you please advise on how to have it close upon making a selection or perhaps have it include an "OK" selection? I imagine this involves adding a command button to the "UserForm1," but I'm not too familiar with generating forms in VBE.

You've been a great help. Thanks & God Bless.

 

[Joe Was]

Replace my last Sub with these two:

Private Sub ComboBox1_Change()
Sheets("Sheet1").Range("D1").Value = ComboBox1.Value

myUnLoad
End Sub


Sub myUnLoad()
UserForm1.Hide
End Sub