Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Go to Sheet
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Go to Sheet
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Go to Sheet
sheet2.select
or sheets("sheetname").select or application.goto sheets("sheetname").range("a3") -- Don Guillett SalesAid Software "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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Go to Sheet
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Go to Sheet
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Go to Sheet
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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Go to Sheet
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? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to Go to Sheet
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to find cell content in sheets and make sheet active | Excel Discussion (Misc queries) | |||
Macro that password protects a sheet | Excel Discussion (Misc queries) | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
MACRO - copy rows based on value in column to another sheet | Excel Discussion (Misc queries) | |||
2 questions, copying data from sheet to sheet and assigning macro | Excel Worksheet Functions |