View Full Version : List of all open workbooks
r_mundhra
08-26-2007, 09:47 AM
Dear All
At times I work on Large Projects and have to keep 30 to 35 files open at a time as they contains Cross linkages.
It becomes diry to manage all open workbooks especially when U R working with 30 + open workbooks.
Can anyone help me with some better wat , say a tool or an add in.
For example, I M using an Add in (down loaded from a website) for managing the worksheets in the Active Workbook which I M attaching herewith. I found it quite useful.
Thanx/r_mundhra
lucas
08-26-2007, 04:08 PM
We're supposed to be here to help you learn so...Just to get you started. add a userform with a listbox named Listbox1 and add this code to the code for the userform. It will list the open workbooks for you:
Private Sub UserForm_Initialize()
On Error Resume Next
Dim w As Workbook
For Each w In Workbooks
ListBox1.AddItem w.Name
Next
End Sub
r_mundhra
08-26-2007, 10:29 PM
Thanx Lucas...I shall check it out/r_mundhra
r_mundhra
08-26-2007, 11:13 PM
Hi Lucas...its workign well/Thanx agn/r_mundhra
lior03
08-26-2007, 11:56 PM
hello
how can i activate a workbook that is on the listbox just by clicking on it?
thanks
Bob Phillips
08-27-2007, 02:21 AM
In the lisbox change event
Workbooks(ListBox1.Value).Activate
lior03
08-27-2007, 03:57 AM
hello
i enclose a file of a userform i built that will list all open wb.i notice some problems occur.the click event do not get me to activate a wb i choose.how can i clear the listbox so every time i open the userform only the preset open wb will appear .
thanks
lucas
08-27-2007, 06:39 AM
I don't think this will list all of the open workbooks on a network...only the workbooks you have open.....
mdmackillop
08-27-2007, 09:31 AM
You have a change event, not a Click event. Don't use the FullName to populate the listbox, just the Name
Private Sub ListBox1_Click()
Workbooks(ListBox1.Text).Activate
End Sub
Private Sub UserForm_Initialize()
On Error Resume Next
Dim w As Workbook
For Each w In Application.Workbooks
ListBox1.AddItem w.Name
Next
End Sub
Bob Phillips
08-27-2007, 10:13 AM
Change does it just as well as Click does.
mdmackillop
08-27-2007, 11:39 AM
hello
how can i activate a workbook that is on the listbox just by clicking on it?
thanks
But he did say "Click"!:*)
Bob Phillips
08-27-2007, 11:48 AM
Clicking on the item in the Listbox surely, still triggers the change event.
lior03
08-29-2007, 06:24 AM
hello
i have a usf which i want to add to a userform through a text box.
Function wbnumber()
wbnumber = Workbooks.Count
End Function
how can i get the result of this function o a text box or a label ?
thanks
Bob Phillips
08-29-2007, 06:30 AM
TextBox1.Text = wbNumber()
lucas
08-29-2007, 06:33 AM
Not sure why you want to use the function:
TextBox1.Value = Workbooks.Count
Bob Phillips
08-29-2007, 06:38 AM
I assumed it was just a simple example to support te question :-)
lior03
08-29-2007, 06:40 AM
like this?
Private Sub TextBox1_Change()
TextBox1.text = wbnumber()
End Sub
this do not work.
lucas
08-29-2007, 07:08 AM
Put it in the userform initialize statement
Private Sub UserForm_Initialize()
TextBox1.Text = wbnumber()
End Sub
and the function in a standard module
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.