Home

MacOffice

This site will look better if you upgrade to a browser that supports web standards.

In XL2001 and XLv.X the Standard and Formula toolbars can easily be accidentally undocked, causing the workbook windows to slide up underneath.

Here's a way to lock them in place so that you can't inadvertently move them:

Put this macro in your Personal Macro Workbook, or another workbook in your Microsoft Office N:Office:Startup:Excel folder, in a regular code module (in the Visual Basic Editor, choose Insert/Module):

    Public Sub LockToolbars()
        With Application.CommandBars("Standard")
            .Protection = msoBarNoProtection
            .Position = msoBarTop
            .RowIndex = 1
            .Protection = msoBarNoMove
        End With
        With Application.CommandBars("Formula Bar")
            .Protection = msoBarNoProtection
            .Position = msoBarTop
            .RowIndex = 2
            .Protection = msoBarNoMove
        End With
    End Sub

Then call the macro from the Workbook_Open() event code in the ThisWorkbook code module of your Personal Macro Workbook:

    Private Sub Workbook_Open()
        LockToolBars
    End Sub

Save the Personal Macro Workbook, then type OPT-F11 to return to XL. Quit XL. For best results, trash the Microsoft Excel Toolbars (9/10) file in the Preferences:Microsoft folder, and empty the trash, before opening XL for the first time (but if you've modified them, you'll have to do it again afterward).

This page last updated Tuesday, 3 February 2004

© Copyright 2001 - 2004 McGimpsey and Associates. Except where noted, all code on this site may be distributed under the Gnu GPL. Acknowledgement is appreciated.

Valid XHTML 1.1 Valid CSS Made on a Macintosh

Quick Links

Unfamiliar with macros? See David McRitchie's Getting Started With Macros and User Defined Functions