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?

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?

Just beat me to it! ;)

This was the way I did it:


UserForm called UserForm1, 2 Buttons (CommandButton1 and 2 and
ComboBox:

Private Sub CommandButton1_Click()

Dim xlWkBks, xlWkBkMessage

ComboBox1.AddItem (ThisWorkbook.Name)

For Each xlWkBks In Workbooks
If xlWkBks.Name < ThisWorkbook.Name Then
ComboBox1.AddItem (xlWkBks.Name)
End If
Next xlWkBks

End Sub


Private Sub CommandButton2_Click()

If ComboBox1.Value = "" Then MsgBox ("Please make a selection")

Windows(ComboBox1.Value).Activate

End Sub


*edit:* Ah, didn't notice the sheets bit, looks like charles has go
this one covered though.


Cheers
Jo

--
LFCFa
-----------------------------------------------------------------------
LFCFan's Profile: http://www.excelforum.com/member.php...fo&userid=3748
View this thread: http://www.excelforum.com/showthread.php?threadid=57220

maybe you can adapt something like this. just create a userform (userform1 in my
case)
put this code in the activate section

Private Sub UserForm_Activate()
Dim newButton As MSForms.Control
Dim z As Integer, i As Integer, cnt As Integer
Dim ws As Worksheet
cnt = ActiveWorkbook.Worksheets.Count
Set newButton = Me.Controls.Add("Forms.listbox.1")
For Each ws In ActiveWorkbook.Worksheets
With newButton
.AddItem ws.Name
.Left = 10
.Top = 5 + cnt
.Height = 15 * cnt
.Visible = True
.FontSize = 8
.Width = 85

End With

Next
End Sub

--


Gary


"drdavidge" wrote in message
oups.com...
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?

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?



interesting ideas guys, thanks. im wondering if there may be an easier
way to do this since i give the filename i open a variable name for
both the filename and the workbook. the book i am opening only contains
one sheet. any ideas how to do a vlookup on that?