Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
c2k2000
 
Posts: n/a
Default 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   Report Post  
Gord Dibben
 
Posts: n/a
Default

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   Report Post  
c2k2000
 
Posts: n/a
Default

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   Report Post  
Gord Dibben
 
Posts: n/a
Default

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   Report Post  
c2k2000
 
Posts: n/a
Default

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   Report Post  
Gord Dibben
 
Posts: n/a
Default

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   Report Post  
Dave Peterson
 
Posts: n/a
Default

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   Report Post  
Gord Dibben
 
Posts: n/a
Default

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   Report Post  
Dave Peterson
 
Posts: n/a
Default

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   Report Post  
Dave Peterson
 
Posts: n/a
Default

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


  #11   Report Post  
Gord Dibben
 
Posts: n/a
Default

Got this one to work.

Thanks Dave.

On Fri, 24 Dec 2004 09:14:31 -0600, Dave Peterson
wrote:

This seems to work ok:

http://groups.google.co.uk/groups?th...6CBA%40msn.com


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
PROTECTING/UNPROTECTING SHEETS Maureen Excel Discussion (Misc queries) 1 January 6th 05 07:46 PM
View and Cell Sum Range Dewayne Excel Discussion (Misc queries) 4 December 29th 04 04:23 PM
Multiple sheets selected twa14 Excel Discussion (Misc queries) 2 December 21st 04 12:15 PM
There is no way to view multiple sheets from one workbook kstub Excel Discussion (Misc queries) 3 December 17th 04 09:55 PM
Linking sheets to a summary sheet in workbook gambinijr Excel Discussion (Misc queries) 4 December 16th 04 09:13 PM


All times are GMT +1. The time now is 09:40 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"