Originally Posted by Ben McClave

Hi Jerry,

It sounds like you need a UserForm, since you're looking for the user to "choose" from a list. If so, step one would be to create a UserForm with a ComboBox (ComboBox1) and a Command Button (CommandButton1). After adding these items (and making the form look nice in general), add this code to the UserForm's module:

------------

Private Sub CommandButton1_Click()
sChosenWord = ComboBox1.Text
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim v As Variant

With CreateObject("Scripting.Dictionary")
.CompareMode = vbTextCompare
For Each v In Range("A1:A10") 'Change to suit
If Not IsEmpty(v.Value) And Not .exists(v.Value) Then
.Add v, Nothing
Me.ComboBox1.AddItem v
End If
Next v
End With

End Sub

-------------------

Next, in Module1, add this code:

--------------

Public sChosenWord As String

Sub OpenWorkbook()
Dim wbNew As Workbook

sChosenWord = vbNullString

UserForm1.Show

If sChosenWord = "" Then Exit Sub 'User canceled, exit

Set wbNew = Workbooks.Add

MsgBox "User chose: " & sChosenWord
End Sub


-----------------


To use this, just run the OpenWorkbook macro. It will call up the UserForm and display a unique list of values from the range you specify (in the example, A1:A10). Once the UserForm unloads, the macro checks for a null string and cancels if one is found. Otherwise, a new workbook is opened and a message box displays the value selected by the user.

Hope this helps get you started,

Ben