Create a UserForm, UserForm1, 2 Listboxes, ListBox1 and ListBox2, and a
Command Button, CommandButton1

Use this macro to launch the userform

Sub ShowWorkbooks()
Dim wb As Workbook
With UserForm1
.ListBox1.Clear
.ListBox2.Clear
For Each wb In Workbooks
.ListBox1.AddItem wb.Name
Next
.ListBox1.ListIndex = 0
End With
UserForm1.Show False
End Sub

and paste this code into the code section for UserForm1:

Option Explicit

Private Sub CommandButton1_Click()
MsgBox ListBox1.Value & " " & ListBox2.Value
Unload Me
End Sub

Private Sub ListBox1_Click()
Dim ws As Worksheet
ListBox2.Clear
If Not ListBox1.Value = "" Then
For Each ws In Workbooks(ListBox1.Value).Sheets
ListBox2.AddItem ws.Name
Next
ListBox2.ListIndex = 0
End If
End Sub

HTH

Charles Chickering
drdavidge wrote:
hey guys, is there a way i can program a macro to display a box with
all the currently opened excel files and sheets?

basically i have a macro that will prompt you for a file, open it, and
then do some formatting on this file. i have a nother macro for a
separate sheet that will do vlookup's on this new (formatted) sheet
that is already open, but i dont know how to specify which open sheet
to do the vlookups in. i open up the sheet by doing this:

MsgBox "Please select the MRP file"
mrpFilename = Application.GetOpenFilename("Excel files (*.xls), *.xls")
Set mrpBook = Workbooks.Open(mrpFilename)

then it does the formatting.. etc.


is there an easy way to do this?