list of opened workbooks and selection
 

Dear experts,
I have written a macro that works on the active workbook.
I would like instead to get a list of the opened workbooks
in Excel, and be able to select one (maybe through an
inputbox?) on which the macro has to work.
Is this possible?
Many thanks!
Kind regards,
Valeria

list of opened workbooks and selection
 

Hi Valeria, :-)

The code with using Input box would be...

Sub Test()
Dim wkb As Workbook
Dim strWkbName
Dim strActivate As String
For Each wkb In Workbooks
strWkbName = strWkbName & wkb.Name & vbLf
Next
strActivate = Application.InputBox _
(strWkbName & "Which workbook do you want to activate?")
On Error GoTo Terminate
With Windows(strActivate)
.Visible = True
.Activate
End With
Exit Sub
Terminate:
MsgBox Err.Number & ":" & Err.Description
End Sub

But I think you don't have to ACTIVATE workbook, just specify the workbook
that you want, like this.

Just my opinion, ListBox or Combobox is better than inputBox. (You don't
have to type, just can select names)

Sub Test2()
Dim wkb As Workbook
Dim strWkbName
Dim strActivate As String
For Each wkb In Workbooks
strWkbName = strWkbName & wkb.Name & vbLf
Next
strActivate = Application.InputBox _
(strWkbName & "Which workbook do you want to activate?")
On Error GoTo Terminate
YourMacro Workbooks(strActivate)
Exit Sub
Terminate:
MsgBox Err.Number & ":" & Err.Description
End Sub

Sub YourMacro(ByVal wkb As Workbook)
wkb.Sheets(1).Cells(1, 1).Value = "something"
End Sub


--
Kind Regards
Colo
/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
Colo of 'The Road of The Cell Masters' :)

URL:http://www.interq.or.jp/sun/puremis/...astersLink.htm


/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

"Valeria" wrote in message
...
Dear experts,
I have written a macro that works on the active workbook.
I would like instead to get a list of the opened workbooks
in Excel, and be able to select one (maybe through an
inputbox?) on which the macro has to work.
Is this possible?
Many thanks!
Kind regards,
Valeria

list of opened workbooks and selection
 

if you put a combobox onto a userform, you can populate
it like this:

Option ExplicitPrivate Sub UserForm_Initialize()
Dim wb As Workbook
ComboBox1.AddItem "Select Workbook..."
For Each wb In Workbooks
ComboBox1.AddItem wb.Name
Next
ComboBox1.ListIndex = 0
End Sub
Private Sub cmdOK_Click()
If ComboBox1.ListIndex = 0 Then Exit Sub
'process workbook
End Sub

Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
Dear experts,
I have written a macro that works on the active
workbook.
I would like instead to get a list of the opened
workbooks
in Excel, and be able to select one (maybe through an
inputbox?) on which the macro has to work.
Is this possible?
Many thanks!
Kind regards,
Valeria
.

list of opened workbooks and selection
 

Valeria,

You may want to modify Patrick's code so as to avoid
hidden workbooks and the workbook containing the code, viz.

For Each wb In Workbooks
If wb.Windows(1).Visible And wb.Name <
ThisWorkbook.Name Then
ComboBox1.AddItem wb.Name
End If
Next wb

Kevin Beckham

-----Original Message-----
if you put a combobox onto a userform, you can populate
it like this:

Option ExplicitPrivate Sub UserForm_Initialize()
Dim wb As Workbook
ComboBox1.AddItem "Select Workbook..."
For Each wb In Workbooks
ComboBox1.AddItem wb.Name
Next
ComboBox1.ListIndex = 0
End Sub
Private Sub cmdOK_Click()
If ComboBox1.ListIndex = 0 Then Exit Sub
'process workbook
End Sub

Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
Dear experts,
I have written a macro that works on the active
workbook.
I would like instead to get a list of the opened
workbooks
in Excel, and be able to select one (maybe through an
inputbox?) on which the macro has to work.
Is this possible?
Many thanks!
Kind regards,
Valeria
.

.