Macro Button Menu

[politesse]

I have created a macro button menu to access 21 of my workbooks.

I did this so my wife could access the workbooks easily.

I would like to lock the view that shows on the screen so it will not scroll
if someone uses the scroll button on the mouse.

Can this be done?
Excel 2002

Thanks

 

[Gord Dibben]

Are these buttons on the worksheet?

You could place them all above a certain row and left of a certain column then
Window>Freeze Panes so even if someome scrolled the buttons would remain in
view.

e.g. place the buttons in the range of A1:F10

Select G11 and freeze at that point.

With VBA code..........................

You can set the scrollarea to a fixed range.

Since the scrollarea method does not stick between sessions you will have to
reset it each time you open the workbook.

You may wish to place the code into a WorkBook_Open Sub in ThisWorkbook module
and specify which worksheet if only one sheet required.

Adjust the sheetname and range to suit.

Private Sub WorkBook_Open()
Sheets("YourSheet").ScrollArea = "A1:M30"
End Sub



Gord Dibben MS Excel MVP

 

[politesse]

Yes the buttons are on a worksheet named menu.xls from A1 to H19

If I freeze the panes then it freezes where the selected box is which puts
the menu out of line.

I used the visual basic editor and pasted

Private Sub WorkBook_Open()
Sheets("menu").ScrollArea = "A1:H30"
End Sub

Nothing happened. Don't know why

 

[Gord Dibben]

If you select I20 and freeze panes, A1:H19 will remain in view.

On the other issue.........................

Workbook_Open code goes into ThisWorkbook module, not a general module.

Alt + F11 to open VBE.

Expand your workbook/project and expane Microsoft Excel Objects.

Double-click on ThisWorkbook to open that module.

Paste the code in there.

Save and close your workbook.

Re-open with the scrollarea set to A1 H30

By the way..........menu.xls is a workbook, not a worksheet.

Is your sheet name "menu" also?


Gord Dibben MS Excel MVP

 

[politesse]

Yes the sheet is named menu

I ended up with something I can live with but VBA did nothing, maybe I am
not doing it right, didn't understand"Expand your workbook/project and
expand Microsoft Excel Objects. Double-click on ThisWorkbook to open that
module."

May have something to do with the background picture that is included in the
range

My thought there was some easy way to freeze the screen and lock it from
moving

Thanks anyhow

 

[Gord Dibben]

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

 

[Gord Dibben]

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

 

[politesse]

Wow!, That did it, A thousand 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

 

[politesse]

Thanks, but my buttons open different workbooks not different sheets

 

[Gord Dibben]

"21 of my workbooks"

Missed that part......sheepish grin

Gord