If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Tree View for excel sheets
Can any body tell me how to have a tree view of all the sheets in excel
workbook, thank you. |
#2
|
|||
|
|||
ck
What is your definition of a "tree view"? Mine is a central trunk with branches leading to branches to leaves or needles. Like what you see in Windows Explorer. Excel worksheets are in a linear mode. You can see more sheets by right-clicking on one of the arrow buttons down at left side of sheet tab bar. There are VBA methods of popping up a list of sheets on a UserForm or in a listbox. Post back if any of this helps or if I have missed it completely. Gord Dibben Excel MVP On Wed, 22 Dec 2004 15:09:03 -0800, "c2k2000" wrote: Can any body tell me how to have a tree view of all the sheets in excel workbook, thank you. |
#3
|
|||
|
|||
yes i want a explorer view. but even better iif you have a view for all the
sheets, but not pop up. htank you. "Gord Dibben" wrote: ck What is your definition of a "tree view"? Mine is a central trunk with branches leading to branches to leaves or needles. Like what you see in Windows Explorer. Excel worksheets are in a linear mode. You can see more sheets by right-clicking on one of the arrow buttons down at left side of sheet tab bar. There are VBA methods of popping up a list of sheets on a UserForm or in a listbox. Post back if any of this helps or if I have missed it completely. Gord Dibben Excel MVP On Wed, 22 Dec 2004 15:09:03 -0800, "c2k2000" wrote: Can any body tell me how to have a tree view of all the sheets in excel workbook, thank you. |
#4
|
|||
|
|||
No pop up.......
See David McRitchie's site for code to build a Table of Contents on a separate sheet with hyperlinks to each sheet. http://www.mvps.org/dmcritchie/excel/buildtoc.htm Gord On Thu, 23 Dec 2004 10:09:07 -0800, "c2k2000" wrote: yes i want a explorer view. but even better iif you have a view for all the sheets, but not pop up. htank you. "Gord Dibben" wrote: ck What is your definition of a "tree view"? Mine is a central trunk with branches leading to branches to leaves or needles. Like what you see in Windows Explorer. Excel worksheets are in a linear mode. You can see more sheets by right-clicking on one of the arrow buttons down at left side of sheet tab bar. There are VBA methods of popping up a list of sheets on a UserForm or in a listbox. Post back if any of this helps or if I have missed it completely. Gord Dibben Excel MVP On Wed, 22 Dec 2004 15:09:03 -0800, "c2k2000" wrote: Can any body tell me how to have a tree view of all the sheets in excel workbook, thank you. |
#5
|
|||
|
|||
this works half of it, but i still need to have the table of content on each
sheet, i notice there is a prog call XL Navigator, is that any good? "Gord Dibben" wrote: No pop up....... See David McRitchie's site for code to build a Table of Contents on a separate sheet with hyperlinks to each sheet. http://www.mvps.org/dmcritchie/excel/buildtoc.htm Gord On Thu, 23 Dec 2004 10:09:07 -0800, "c2k2000" wrote: yes i want a explorer view. but even better iif you have a view for all the sheets, but not pop up. htank you. "Gord Dibben" wrote: ck What is your definition of a "tree view"? Mine is a central trunk with branches leading to branches to leaves or needles. Like what you see in Windows Explorer. Excel worksheets are in a linear mode. You can see more sheets by right-clicking on one of the arrow buttons down at left side of sheet tab bar. There are VBA methods of popping up a list of sheets on a UserForm or in a listbox. Post back if any of this helps or if I have missed it completely. Gord Dibben Excel MVP On Wed, 22 Dec 2004 15:09:03 -0800, "c2k2000" wrote: Can any body tell me how to have a tree view of all the sheets in excel workbook, thank you. |
#6
|
|||
|
|||
Have not tried XL Navigator so cannot comment on its efficacy or stability.
Give it a try and let us know how it works out. I know you said "no pop up" but I like this code for sheet navigation from Bob Phillips. Sub BrowseSheets() Const nPerColumn As Long = 38 'number of items per column Const nWidth As Long = 13 'width of each letter Const nHeight As Long = 18 'height of each row Const sID As String = "___SheetGoto" 'name of dialog sheet Const kCaption As String = " Select sheet to goto" 'dialog caption 'all the above Const's are adjustable to your taste. Dim i As Long Dim TopPos As Long Dim iBooks As Long Dim cCols As Long Dim cLetters As Long Dim cMaxLetters As Long Dim cLeft As Long Dim thisDlg As DialogSheet Dim CurrentSheet As Worksheet Dim cb As OptionButton Application.ScreenUpdating = False If ActiveWorkbook.ProtectStructure Then MsgBox "Workbook is protected.", vbCritical Exit Sub End If On Error Resume Next Application.DisplayAlerts = False ActiveWorkbook.DialogSheets(sID).Delete Application.DisplayAlerts = True On Error GoTo 0 Set CurrentSheet = ActiveSheet Set thisDlg = ActiveWorkbook.DialogSheets.Add With thisDlg .Name = sID .Visible = xlSheetHidden 'sets variables for positioning on dialog iBooks = 0 cCols = 0 cMaxLetters = 0 cLeft = 78 TopPos = 40 For i = 1 To ActiveWorkbook.Worksheets.Count If i Mod nPerColumn = 1 Then cCols = cCols + 1 TopPos = 40 cLeft = cLeft + (cMaxLetters * nWidth) cMaxLetters = 0 End If Set CurrentSheet = ActiveWorkbook.Worksheets(i) cLetters = Len(CurrentSheet.Name) If cLetters cMaxLetters Then cMaxLetters = cLetters End If iBooks = iBooks + 1 .OptionButtons.Add cLeft, TopPos, cLetters * nWidth, 16.5 .OptionButtons(iBooks).text = _ ActiveWorkbook.Worksheets(iBooks).Name TopPos = TopPos + 13 Next i .Buttons.Left = cLeft + (cMaxLetters * nWidth) + 24 CurrentSheet.Activate With .DialogFrame .Height = Application.Max(68, _ Application.Min(iBooks, nPerColumn) * nHeight + 10) .Width = cLeft + (cMaxLetters * nWidth) + 24 .Caption = kCaption End With .Buttons("Button 2").BringToFront .Buttons("Button 3").BringToFront Application.ScreenUpdating = True If .Show Then For Each cb In thisDlg.OptionButtons If cb.Value = xlOn Then ActiveWorkbook.Worksheets(cb.Caption).Select Exit For End If Next cb Else MsgBox "Nothing selected" End If Application.DisplayAlerts = False .Delete End With End Sub Gord On Thu, 23 Dec 2004 12:39:10 -0800, "c2k2000" wrote: this works half of it, but i still need to have the table of content on each sheet, i notice there is a prog call XL Navigator, is that any good? "Gord Dibben" wrote: No pop up....... See David McRitchie's site for code to build a Table of Contents on a separate sheet with hyperlinks to each sheet. http://www.mvps.org/dmcritchie/excel/buildtoc.htm Gord On Thu, 23 Dec 2004 10:09:07 -0800, "c2k2000" wrote: yes i want a explorer view. but even better iif you have a view for all the sheets, but not pop up. htank you. "Gord Dibben" wrote: ck What is your definition of a "tree view"? Mine is a central trunk with branches leading to branches to leaves or needles. Like what you see in Windows Explorer. Excel worksheets are in a linear mode. You can see more sheets by right-clicking on one of the arrow buttons down at left side of sheet tab bar. There are VBA methods of popping up a list of sheets on a UserForm or in a listbox. Post back if any of this helps or if I have missed it completely. Gord Dibben Excel MVP On Wed, 22 Dec 2004 15:09:03 -0800, "c2k2000" wrote: Can any body tell me how to have a tree view of all the sheets in excel workbook, thank you. |
#7
|
|||
|
|||
How about a floating toolbar that you could use with any workbook?
http://groups.google.com/groups?thre...1C74%40msn.com c2k2000 wrote: Can any body tell me how to have a tree view of all the sheets in excel workbook, thank you. -- Dave Peterson |
#8
|
|||
|
|||
Dave
That's the second time you have posted this URL and both times I get Not Found Sorry, the document you requested is not available. You can visit the main page. Gord On Thu, 23 Dec 2004 17:45:30 -0600, Dave Peterson wrote: How about a floating toolbar that you could use with any workbook? http://groups.google.com/groups?thre...1C74%40msn.com c2k2000 wrote: Can any body tell me how to have a tree view of all the sheets in excel workbook, thank you. |
#9
|
|||
|
|||
Hmmmm.
I blame google and that beta stuff! This seems to work ok: http://groups.google.co.uk/groups?th...6CBA%40msn.com And for comparison purposes only: http://groups.google.com/groups?thre...1C74%40msn.com It looks almost the same vbg. Gord Dibben wrote: Dave That's the second time you have posted this URL and both times I get Not Found Sorry, the document you requested is not available. You can visit the main page. Gord On Thu, 23 Dec 2004 17:45:30 -0600, Dave Peterson wrote: How about a floating toolbar that you could use with any workbook? http://groups.google.com/groups?thre...1C74%40msn.com c2k2000 wrote: Can any body tell me how to have a tree view of all the sheets in excel workbook, thank you. -- Dave Peterson |
#10
|
|||
|
|||
In fact, if I just changed the high level domain, it worked ok:
http://groups.google.co.uk/groups?th...1C74%40msn.com Dave Peterson wrote: Hmmmm. I blame google and that beta stuff! This seems to work ok: http://groups.google.co.uk/groups?th...6CBA%40msn.com And for comparison purposes only: http://groups.google.com/groups?thre...1C74%40msn.com It looks almost the same vbg. Gord Dibben wrote: Dave That's the second time you have posted this URL and both times I get Not Found Sorry, the document you requested is not available. You can visit the main page. Gord On Thu, 23 Dec 2004 17:45:30 -0600, Dave Peterson wrote: How about a floating toolbar that you could use with any workbook? http://groups.google.com/groups?thre...1C74%40msn.com c2k2000 wrote: Can any body tell me how to have a tree view of all the sheets in excel workbook, thank you. -- Dave Peterson -- Dave Peterson |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Presenter View is selected but not displayed on primary monitor | mikejanj | Powerpoint | 6 | March 16th, 2005 08:24 PM |
Presenter View - nothing's ever simple | Fran McConville | Powerpoint | 1 | October 19th, 2004 05:33 PM |
Outlook 2003 View Settings MADNESS | laura | General Discussion | 7 | August 20th, 2004 05:17 PM |
disable datasheet view in form | John Milbury-Steen | Using Forms | 3 | June 10th, 2004 03:45 PM |
sql view | charles osborne | Using Forms | 1 | May 29th, 2004 05:42 AM |