Buttons for Excel worksheets
 

Hi

Hopefully someone can help me on this.

I have in one Excel file, 12 worksheets each containing a lot of
data. What I would like is a set of buttons or a toolbar which would
have buttons for each worksheet that I can click on to open the
relevant worksheet.

Is this possible?

Clicking on the sheet tabs on the bottom gets frustrating as each
sheet name is long so scrolling is necessary and annoying.


Any help would be greatly appreciated.

Thanks

SP

Buttons for Excel worksheets
 

On a Contents sheet you could have a series of hyperlinks so that if
you click on one you will be taken to that sheet. On each of the other
sheets you could have one hyperlink to take you back to the Contents
sheet. For example, enter this formula in H1 of one of the sheets:

=HYPERLINK("#Contents!A1","Back to Contents")

This will display the message Back to Contents and if you click on
this cell it will send you to A1 of the Contents sheet. Copy this
formula to H1 of all the other sheets.

Then in the Contents sheet itself, you would have a series of formulae
like:

=HYPERLINK("#Sheet1!A1","Sheet1")
=HYPERLINK("#Sheet2!A1","Sheet2")

and so on. You need to change the Sheet1, Sheet2 etc in the first part
of the formula to match your sheet names, but you can have whatever
message you want in the second part of the formula. You can have this
list starting in, say, A3.

Hope this helps.

Pete



On May 20, 1:16 am, Shreekant Patel wrote:
Hi

Hopefully someone can help me on this.

I have in one Excel file, 12 worksheets each containing a lot of
data. What I would like is a set of buttons or a toolbar which would
have buttons for each worksheet that I can click on to open the
relevant worksheet.

Is this possible?

Clicking on the sheet tabs on the bottom gets frustrating as each
sheet name is long so scrolling is necessary and annoying.

Any help would be greatly appreciated.

Thanks

SP

Buttons for Excel worksheets
 

If you rightclick on the VCR like controls (to the left of the worksheet tabs),
you'll see a list of worksheets in that workbook.

Other options:

Maybe you could use David McRitchie's Build Table of Contents to get the list
and the hyperlinks all at once:
http://www.mvps.org/dmcritchie/excel/buildtoc.htm

Another option if you want a more generic solution:
http://contextures.com/xlToolbar01.html
(From Debra Dalgleish's site.)

It builds a toolbar that you can use with any workbook to navigate to any
worksheet.

Shreekant Patel wrote:

Hi

Hopefully someone can help me on this.

I have in one Excel file, 12 worksheets each containing a lot of
data. What I would like is a set of buttons or a toolbar which would
have buttons for each worksheet that I can click on to open the
relevant worksheet.

Is this possible?

Clicking on the sheet tabs on the bottom gets frustrating as each
sheet name is long so scrolling is necessary and annoying.

Any help would be greatly appreciated.

Thanks

SP

--

Dave Peterson

Buttons for Excel worksheets
 

If you right-click on the navigation arrows at bottom left of the sheet tabs you
will get a 15 sheet pop-up selection and "more"

If that's not enough................

If you want to see all names and select from a list use Bob Phillips'
browsesheets macro.

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

NOTE: Gary Brown revised this code to cover hidden sheets and chart sheets and
other potential problems.

See this thread for that revised code.

http://snipurl.com/1l8o4

I would make one more change to Gary's code.

Const nWidth As Long = 8 'width of each letter
Change to 10 or sheetnames < 4 chars will be incomplete


Gord Dibben MS Excel MVP

On 19 May 2007 17:16:25 -0700, Shreekant Patel wrote:

Hi

Hopefully someone can help me on this.

I have in one Excel file, 12 worksheets each containing a lot of
data. What I would like is a set of buttons or a toolbar which would
have buttons for each worksheet that I can click on to open the
relevant worksheet.

Is this possible?

Clicking on the sheet tabs on the bottom gets frustrating as each
sheet name is long so scrolling is necessary and annoying.


Any help would be greatly appreciated.

Thanks

SP