Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / July 2007

Tip: Looking for answers? Try searching our database.

Macro Button Menu

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
politesse - 11 Jul 2007 23:24 GMT
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 - 11 Jul 2007 23:45 GMT
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

>I have created a macro button menu to access 21 of my workbooks.
>
[quoted text clipped - 7 lines]
>
>Thanks
politesse - 12 Jul 2007 00:54 GMT
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

> Are these buttons on the worksheet?
>
[quoted text clipped - 40 lines]
>>
>>Thanks
Gord Dibben - 12 Jul 2007 01:14 GMT
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

>Yes the buttons are on a worksheet named menu.xls from A1 to H19
>
[quoted text clipped - 53 lines]
>>>
>>>Thanks
politesse - 12 Jul 2007 13:46 GMT
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

> If you select I20 and freeze panes, A1:H19 will remain in view.
>
[quoted text clipped - 79 lines]
>>>>
>>>>Thanks
Gord Dibben - 12 Jul 2007 15:01 GMT
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.