userform to display dropdown list of open XL files

[davegb]

I want to create a userform to display 2 dropdown lists, both with all
currently opened XL files in the list, so the user can select 2 files
to compare.

I've tried reverse engineering Chip Pearson's code in his "Compare"
macro, but it's just beyond my current skill level. And I looked for
information in this NG, but couldn't find a solution, probably I just
don't know how to phrase the search criteria correctly.

I have almost no experience with forms in XL, other than creating a
simple form to ask the user for input and put the data in a cell. But I
do know how to create a basic form.

Thanks.

 

[Guest]

Create a userform and add two combo boxs to it. Add this code to the form and
you will get your two combo boxes...

Private Sub UserForm_Initialize()
Dim wbk As Workbook

For Each wbk In Workbooks
ComboBox1.AddItem wbk.Name
ComboBox2.AddItem wbk.Name
Next wbk
End Sub

 

[Tom Ogilvy]

You might want to add:

Private Sub UserForm_Initialize()
Dim wbk As Workbook

For Each wbk In Workbooks
if wbk.windows(1).Visible = True then
ComboBox1.AddItem wbk.Name
ComboBox2.AddItem wbk.Name
end if
Next wbk
End Sub

This will avoid including workbooks that are hidden.

 

[davegb]

You might want to add:

Private Sub UserForm_Initialize()
Dim wbk As Workbook

For Each wbk In Workbooks
if wbk.windows(1).Visible = True then
ComboBox1.AddItem wbk.Name
ComboBox2.AddItem wbk.Name
end if
Next wbk
End Sub

This will avoid including workbooks that are hidden.

Thanks, both of you. I probably should have asked earlier, but how do I
capture the spreadsheet names once they've been selected and the OK
button clicked?

 

[Guest]

My preference is to not unload the form, but just to hide it. Then you can
just referdirectly to the combo boxes somethin like this...

workbooks(combobox1.text).select

 

[davegb]

My preference is to not unload the form, but just to hide it. Then you can
just referdirectly to the combo boxes somethin like this...

workbooks(combobox1.text).select

Thanks to you both!