Macro to choose data and export in another workbook
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
|