> If you select I20 and freeze panes, A1:H19 will remain in view.
>
[quoted text clipped - 79 lines]
>>>>
>>>>Thanks
OK, try it this way.
Copy the code.
Right-click on the Excel Icon left of "File" on the main menu in Excel or on the
Excel Icon at left side of Title bar if not maximized.
Select "View Code" from the dropdown.
A module will open.
Paste the code into that module. Save and close then re-open.
I still don't understand why a simple "Freeze Panes" won't work.
Gord
>Yes the sheet is named menu
>
[quoted text clipped - 94 lines]
>>>>>
>>>>>Thanks
Gord Dibben - 12 Jul 2007 15:08 GMT
Personally I would not bother with all those buttons and preventing scrolling.
I prefer Bob Phillips' browsesheet code to go to any sheet in the workbook so
you don't have to keep going back to the TOC 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
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
>OK, try it this way.
>
[quoted text clipped - 12 lines]
>
>Gord
politesse - 12 Jul 2007 15:38 GMT
Thanks, but my buttons open different workbooks not different sheets
> Personally I would not bother with all those buttons and preventing
> scrolling.
[quoted text clipped - 118 lines]
>>
>>Gord
Gord Dibben - 12 Jul 2007 16:43 GMT
"21 of my workbooks"
Missed that part......sheepish grin
Gord
>Thanks, but my buttons open different workbooks not different sheets
>
[quoted text clipped - 120 lines]
>>>
>>>Gord
politesse - 12 Jul 2007 15:15 GMT
Wow!, That did it, A thousand thanks
> OK, try it this way.
>
[quoted text clipped - 116 lines]
>>>>>>
>>>>>>Thanks