Prior to now I have not tried it with that many sheets. I think the most was
about 60-70 sheets and worked fine at that level.

You are correct.

I just tried with 143 sheets and it crashed.

Works fine on 125 sheets.

Don't know where the limit is or why the crash occurs.

Keep dropping sheets until you find a max limit at which the code works<g

Would assume it is a "memory" thing but not sure.


Gord Dibben Excel MVP

On Tue, 22 Nov 2005 02:21:03 -0800, "Dmunday"
wrote:

Gord

Is there a limit to the number of sheets that can be used when running the
BrowseSheets macro, as every time i run the macro and select the sheet to
goto, Excel crashes.

Am running Excel 2002 SP2, using a workbook with approx. 140 worksheets.

Thanks and Regards

Derek

"Gord Dibben" wrote:

John

You might wish to use Bob Phillips' BrowseSheets macro.

Will pop up a form with all sheets listed and an option button to take you to
each sheet.

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

On Sun, 20 Nov 2005 08:03:51 -0800, "John"
wrote:

When i have written Macro's to take you to another sheet, I always write

ActiveSheet.Next.Select

However I am getting too many sheets. Is there an easier way to write a
macro to take you directly to another sheet without having to write the above
statement 15 times?