Workbook Assistance
 

I have a workbook that has over 80 worksheets in it and I am creating a
summary page. Currently I use the scroll bar to slide between the various
sheets to cut and paste the information and then create the hyperlinks. It
would be more efficient at this stage if I had a dropdown box similar to the
named field dropdown box. Is there a way to create a dropdown option so you
can navigate quickly to the individual worksheets that are created within a
workbook?
--
SGE

Workbook Assistance
 

Well you can create hyperlinks that go to each tab. Insert--Hyperlink, then
select "Place in this Document" and select the tab (or named range) you want
to go to.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"SGE" wrote:

I have a workbook that has over 80 worksheets in it and I am creating a
summary page. Currently I use the scroll bar to slide between the various
sheets to cut and paste the information and then create the hyperlinks. It
would be more efficient at this stage if I had a dropdown box similar to the
named field dropdown box. Is there a way to create a dropdown option so you
can navigate quickly to the individual worksheets that are created within a
workbook?
--
SGE

Workbook Assistance
 

Another option to explore ..

See Dave Peterson's technique at Debra's:
http://www.contextures.com/xlToolbar01.html
Excel -- Navigation Toolbar for Workbook Sheets
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"SGE" wrote:
I have a workbook that has over 80 worksheets in it and I am creating a
summary page. Currently I use the scroll bar to slide between the various
sheets to cut and paste the information and then create the hyperlinks. It
would be more efficient at this stage if I had a dropdown box similar to the
named field dropdown box. Is there a way to create a dropdown option so you
can navigate quickly to the individual worksheets that are created within a
workbook?
--
SGE

Workbook Assistance
 

Here is macro from Bob Phillips that pops up a list of sheets to choose from.

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


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 Dibben MS Excel MVP

On Fri, 6 Apr 2007 11:02:04 -0700, SGE wrote:

I have a workbook that has over 80 worksheets in it and I am creating a
summary page. Currently I use the scroll bar to slide between the various
sheets to cut and paste the information and then create the hyperlinks. It
would be more efficient at this stage if I had a dropdown box similar to the
named field dropdown box. Is there a way to create a dropdown option so you
can navigate quickly to the individual worksheets that are created within a
workbook?
--
SGE

Workbook Assistance
 

Hi,

Check this link for assistance.

Excel -- Data Validation -- Create Dependent Lists.

Use this URL: http://www.contextures.com/xlDataVal02.html

Challa Prabhu


"SGE" wrote:

I have a workbook that has over 80 worksheets in it and I am creating a
summary page. Currently I use the scroll bar to slide between the various
sheets to cut and paste the information and then create the hyperlinks. It
would be more efficient at this stage if I had a dropdown box similar to the
named field dropdown box. Is there a way to create a dropdown option so you
can navigate quickly to the individual worksheets that are created within a
workbook?
--
SGE