Explorer/Navigator/Tree View Of Open Workbooks and Associated Worksheets

[fdegree]

I am trying to find a way to create a side panel (like what is seen in Windows Explorer) in Excel that will display all open workbooks and their associated worksheets in a tree view. I am hoping it can function in all versions of excel, and have the features to rename worksheets and manually drag and drop the worksheets for custom sorting.

[fdegree]

I realize all of this can be done by right clicking on the worksheet tab at the bottom of the screen and right clicking on the VCR controls at the bottom left of the screen. But, the workbooks that I create can range from 20 pages to over 700 pages. When I have a workbook with 100's of pages, I think it would so much easier to sift through them from a separate window pane that has them all listed.

[royUK]

I can't imagine anything helping with 700 worksheets

[fdegree]

You may be right...

Any ideas on how to create this to find out.

I'm thinking if there is a window pane listing the workbooks and worksheets, that would allow for about 30 pages to be viewed at the same time...making it easier to select, sort and rename them as necessary, instead of only having about 4 or 5 tabs at the bottom that can be viewed at any one time...30 vs. 5 seems easier and faster to me.

[abousetta]

There is another approach that might work for you. You will have a 'Master' sheet that will contain all the sheet names and their current order. Whenever you make a name change here then the name of that worksheet will be updated. If you change the tab order then that tab order will be changed also. I have began the coding to see if I can do it but it will take a little longer than I had originally anticipated. Would you be interested in this kind of approach?

[fdegree]

This sounds interesting...let me see if I understand this before you put too much time into it.

There will be a "Master" worksheet (maybe at the front of the workbook) that will list every sheet in the workbook. On this "Master" worksheet I can:
1. Change any name on this "Master" worksheet and the actual name of that worksheet automatically changes.
2. I can manually sort the worksheets, into any order I want, from this "Master" worksheet.

Couple of questions:
1. Will the "Master" worksheet automatically update as new worksheets are added to the workbook?
2. How will the sorting be accomplished? I'm assuming drag-and-drop is out.
2. Can this be done from a separate window pane? The reason I ask...when the workbook is complete, I generate a table of contents, and I also print the entire workbook. I don't want this "Master" worksheet included in either of them. I could just delete this "Master" sheet before creating a table of contents and printing, but I was hoping this would not be necessary...I am kinda forgetful sometimes.

[abousetta]

Couple of questions(answers):
1. yes, new sheet can trigger the update
2. Sorting will be by changing the order number in column B.
2. Let me see if I can get it going with the same workbook and then we can see if we can do it in a separate workbook.

Just for confirmation, you are using Office 2000. Is this correct?

abousetta

[fdegree]

Yes, I am using Office 2000. But, if there is a way, I would like it to work with any excel version.

Thanks for your help...I sincerely appreciate it.

[abousetta]

I'm using Excel 2010 and so I won't be able to test it on your version of Excel. I will try to make sure I don't use any of the newer functions, but you will have to let me know if I do.

[fdegree]

No problem...whatever you need

Thanks!!!!

[abousetta]

Here is what I have so far. On opening the file, activating the "Master" sheet or making any updates to the Master sheet, it will update the Sheet Names and Sheet Index. I have added error trapping whenever possible. But a few things to keep in mind, sorting or filtering will not trigger any events so advised not to do this as it is counterproductive.

Here's what the file can do:

1) If you add a new sheet to the workbook, it will update the Master sheet
2) If activate the Master sheet, if will update the Master sheet
3) If you change a sheet's name in the Master sheet, the macro will update the sheet name in the workbook and then update the Master sheet altogether.
4) If you change the order number (Index number) of a sheet, the macro will move the sheet to the new position in the workbook and then update the Master sheet altogether.
5) If you try to use a sheet number that is not allowed (#0, #1, or a # more than the number of sheets that you have in the workbook, then an error message will describe your problem and the reason you can't do this.
6) If you are hungry or thirsty, the macro will go and prepare dinner , etc.

Pitfalls...
* Keep the "Master" sheet as the first sheet, unless you have a really good reason why you want to move it.
* I have not put an error trapping for checking the length of the new worksheet name that is being changed (could do this if necessary)

Let me know if you have any problems or need any further tweaks.

Good luck.

abousetta

[OnErrorGoto0]

Can we ask why you have 700 sheets in a workbook?

[fdegree]

Here is what I have so far.
6) If you are hungry or thirsty, the macro will go and prepare dinner , etc.

This is the absolute best feature...no I can fire my maid and butler.

[fdegree]

Let me know if you have any problems or need any further tweaks.

Good luck.

abousetta

It works great if I open the .xls file that you have attached, and then build the workbook by adding the worksheets. But, if I open an existing workbook and then try to tun the macro I get the following:
Run-time error '9':
Subscript out of range

Then when I click on the Debug button, the following line of the code is highlighted:
With Worksheets("Master")

Any thoughts?

[abousetta]

It is looking for worksheet named "Master". This name can be changed to any name you want just make sure you make these changes in both ThisWorkbook and Module1. Whatever name you decide to replace the current "Master" worksheet name with just make sure to put it as the first sheet in the workbook.

Good luck and thanks for the feedback. I personally tested it out with a dummy workbook and 100 worksheets and it had no problem updating everything in a second. Let me know if you have any more problems.

[fdegree]

Can we ask why you have 700 sheets in a workbook?

The use I have for excel does not utilize it's full potential. I simply use it to create a report that shows how different pieces of equipment are performing for my clients. It is a great program for creating custom sheets that have blank cells to enter the necessary information. There is some use of formulas that are assigned to specific cells, but that is about the extent of excels potential that I use, with the exception of a few macros.

Hope that makes sense.

[fdegree]

It is looking for worksheet named "Master". This name can be changed to any name you want just make sure you make these changes in both ThisWorkbook and Module1. Whatever name you decide to replace the current "Master" worksheet name with just make sure to put it as the first sheet in the workbook.

Good luck and thanks for the feedback. I personally tested it out with a dummy workbook and 100 worksheets and it had no problem updating everything in a second. Let me know if you have any more problems.

Thanks...I have to cook dinner right now...grilling steak. I'll play with it more a little later.

[abousetta]

Good I was getting hungry and going to put a pizza in the oven, but a streak sound much better enjoy

[abousetta]

What Rory means is that you could probably make a template (one sheet) and put all your clients' data in a table (a second sheet) then have excel loop through each row (each containing a different client's data) and print the template. Now you will only need two sheets. I know it sounds simpler than it is, but it can usually be done with a lot less than 700 sheets.

[fdegree]

Perhaps it's me...I'm still kind of new to VBA and still learning.

I opened an existing workbook and inserted a new blank worksheet as the first page. I then renamed this new sheet as "Master". I ran the macro...
* It created a list of every sheet in the workbook in column "A" and the proper page number in column "B"...good so far.
* When I change the name of any sheet on the "Master" page, it does not update the actual name of that associated page. I also renamed the actual sheet, but the "Master" sheet did not update itself with the new name.
* I changed the page number on the "Master" sheet but it did not resort the actual pages.

Basically, it creates a table of content, but it doesn't respond to any changes.

It has to be something silly on my end, because when I originally opened your file it worked perfectly.

[fdegree]

What Rory means is that you could probably make a template (one sheet) and put all your clients' data in a table (a second sheet) then have excel loop through each row (each containing a different client's data) and print the template. Now you will only need two sheets. I know it sounds simpler than it is, but it can usually be done with a lot less than 700 sheets.

I'm not sure I'm following your description, but I think I got it.

Considering the nature of the report, I need one page for each individual piece of equipment, and no two pieces are the same. Therefore, the need for 100's of sheets for some projects.

[abousetta]

If it ain't broke... don't fix it

[fdegree]

Perhaps I'm inpatient...did you overlook my post above describing the problems I'm having? It's post #20

[abousetta]

Sorry about misreading your post. Did you copy and paste all the code in the right places. There are three set of code you need to add to your workbook:

1) Code is in Module1 (looks like you copied this over fine)
2) Code in ThisWorkbook (this triggers an update when changes are made in the master worksheet - don't think you copied this over)
3) Code in "Master" Worksheet (triggers an update when this sheet is activated - don't think you copied this over either)

Let me if this resolves your problems.

[fdegree]

Works perfectly now...sorry for not following your instructions the first time.

Hope the pizza was good...I know my steak was. Personally, I'd rather have pizza, but we made homemade pizza last night

[abousetta]

Glad it all worked out fine.

Good luck.