Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filter multiple workbooks from drop down list selection | Excel Worksheet Functions | |||
History of Workbooks opened | Excel Worksheet Functions | |||
Troubleshoot links - both workbooks have to be opened? | Excel Discussion (Misc queries) | |||
always prompted to save opened workbooks | Excel Discussion (Misc queries) | |||
limit cell list selection based on the selection of another list | Excel Worksheet Functions |