Macro to Go to Sheet

John · External Since: Jul 15, 2004 Posts: 500

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?

bpeltzer · External Since: Nov 08, 2005 Posts: 380

Worksheets("Sheet3").Select.
You could also replace the string literal with a variable set to the name of
the desired sheet.

"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?
>

John · External Since: Jul 15, 2004 Posts: 500

Thank you. Worked Perfectly and will save me a great deal of time.

Thanks again

"bpeltzer" wrote:

> Worksheets("Sheet3").Select.
> You could also replace the string literal with a variable set to the name of
> the desired sheet.
>
> "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?
> >

Gord Dibben · External Since: Feb 23, 2004 Posts: 9431

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?

Don Guillett · External Since: Sep 01, 2003 Posts: 1150

sheet2.select
or
sheets("sheetname").select
or
application.goto sheets("sheetname").range("a3")

--
Don Guillett
SalesAid Software
donaldb.RemoveThis@281.com
"John" wrote in message

> 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?
>

Dmunday · External Since: Nov 22, 2005 Posts: 1

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?
>
>

Gord Dibben · External Since: Feb 23, 2004 Posts: 9431

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?
>>
>>

Tony Borg · External Since: Sep 01, 2006 Posts: 1

Hi Gord,
This macro that you posted was something I really needed. Thanks for that. I
wonder if it can be modified so that it works as follows. I have a workbook
with six worksheets some of which are password protected. This macro goes to
the last sheet first (which is a password protected sheet) and asks for the
password. If a correct password is provided it goes to that sheet and then
displays the menu. If incorrect password provided it goes to the next
protected sheet and asks for the password etc. as above. What I would like is
for the menu to display immediately and then if a sheet without password
protection is selected it goes directly to that sheet but if a sheet with
password protection is selected from the menu it asks for the password and
then takes you to the requested sheet otherwise it either brings up the menu
again or takes you back to the Main sheet. Thanks for your help.
Regards Tony.
--
TonyB

"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?
>
>

mplsmutt · Joined: Jan 27, 2010 Posts: 1

Found this from an old thread and is close to what I need...except that the pop up box orders the worksheet names as they appear...would anyone know what additional code would order the worksheet names in the pop up box alphabetically by the worksheet name? Thanks in advance.
____________________
[quote="Gord Dibben"]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.