How to have a tree view in Excel

[Eas!war]

Can any body tell me how to have a tree view of all the sheets in excel workbook.


thank you.

[Go!pee]

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.

[Eas!war]

yes i want a explorer view. but even better iif you have a view for all the sheets, but not pop up.

Thank You

[Go!pee]

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

[Eas!war]

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?

[Go!pee]

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