Disable Command bars and Controls in Excel 97-2003

Important message to visitors of this page

Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.

Luckily, Ron was kind enough to allow me to publish all of his Excel content here.

Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.

Kind regards

Jan Karel Pieterse

Important: All examples on this page use False to disable the commandbar/toolbar/control. Change it to True if you want to enable the control.

If you have no idea where to paste the code then check out this page.
Where do I paste the code that I find on the internet

How do I disable a Command bar

This code line will disable the Worksheet Menu Bar
Application.CommandBars("Worksheet Menu Bar").Enabled = False

This code line will disable the Standard toolbar
Application.CommandBars("Standard").Enabled = False

This code line will disable the Cell menu (menu what you see when you right click on a cell/range)
Application.CommandBars("Cell").Enabled = False

Important: You must always use the English name of the Command bar in the code. If you use the local name of the Command bar it is not working.

If you don't know the English name for a Command Bar you can run this macro. It will add a new worksheet to your workbook with a list of all names (English and local).

Sub Get_Commandbars_Names()
    Dim Cbar As CommandBar
    Dim NewWS As Worksheet
    Dim RNum As Long

    RNum = 1
    Set NewWS = Worksheets.Add
    On Error Resume Next
    ActiveSheet.Name = "CommandBarNames"
    On Error GoTo 0

    For Each Cbar In Application.CommandBars
        NewWS.Cells(RNum, "A").Value = Cbar.Name
        NewWS.Cells(RNum, "B").Value = Cbar.NameLocal
        RNum = RNum + 1
    Next Cbar

    NewWS.Columns.AutoFit
End Sub

How do I disable all or almost all Command bars

If you want them back use True instead of False in the code

Sub Disable_Command_Bars_1()
'This will disable all Command bars
    Dim Cbar As CommandBar
    For Each Cbar In Application.CommandBars
        Cbar.Enabled = False
    Next
End Sub

Sub Disable_Command_Bars_2()
'This will disable all BuiltIn Command bars
    Dim Cbar As CommandBar
    For Each Cbar In Application.CommandBars
        If Cbar.BuiltIn = True Then
            Cbar.Enabled = False
        End If
    Next
End Sub

Sub Disable_Command_Bars_3()
' This will disable all Command bars except
' the Worksheet Menu Bar
    Dim Cbar As CommandBar
    For Each Cbar In Application.CommandBars
        If Cbar.Name <> "Worksheet Menu Bar" Then
            Cbar.Enabled = False
        End If
    Next
End Sub

Another way is to use a array with all commandbars you want to disable I use the Cell menu and the Standard toolbar in this example.

Sub Disable_With_Array()
    Dim BarNames As Variant
    Dim N As Integer

    BarNames = Array("Standard", "Cell")
    For N = LBound(BarNames) To UBound(BarNames)
        On Error Resume Next
        Application.CommandBars(BarNames(N)).Enabled = False
        On Error GoTo 0
    Next N
End Sub

How do I disable a Menu or Control

After you read the information below you know that you must use the Id of the menu/control instead of the caption to avoid problems. But how do you find them?. You can use this Add-in (CommandBar Tools)
http://erlandsendata.no/?p=2694

See also the screenshots below this section of the webpage with all menus from the worksheet Menu Bar in Excel 2003.

Disable a menu on the Worksheet Menu Bar

Both Examples will disable the File menu in the Worksheet Menu Bar

1) Use the name of the command bar control

Application.CommandBars("Worksheet Menu Bar").Controls("File").Enabled = False

But this is not working if you run the code in a Excel version that is not English.
File is Bestand in the Dutch Excel version for Example.

2) Use the ID number of the command bar control ( working in every Excel language version)

Application.CommandBars("Worksheet Menu Bar").FindControl(ID:=30002).Enabled = False

because there is only one control with the ID 30002 you can also use this line because FindControl will find the first occurrence of the ID.

Application.CommandBars.FindControl(ID:=30002).Enabled = False

Disable a control

This code line disable View - Full Screen in the Worksheet Menu Bar
Application.CommandBars("Worksheet Menu Bar").FindControl _
(ID:=178, Recursive:=True).Enabled = False

I use Recursive:=True in this code example to look in all sub menus in the Worksheet Menu Bar

This example will only disable the "Copy" control on the Standard Toolbar.
Application.CommandBars("Standard").FindControl(ID:=19).Enabled = False

The examples below disable all the "Copy" controls in all command bars.
Note: Every "Copy" control in the Menu Bar, Toolbars and right click menus

If you use Excel 2000-2003 you can use the code below that use the Office.CommandBarControl

Sub MenuControl_Enabled_False_2000_2003()
' Excel 2000-2003
    Dim Ctrl As Office.CommandBarControl
    For Each Ctrl In Application.CommandBars.FindControls(ID:=19)
        Ctrl.Enabled = False
    Next Ctrl
End Sub

If you use Excel 97 then use this example (also working in 2000-2003)

Sub MenuControl_Enabled_False_97_2003()
' Excel 97-2003
    Dim Cbar As Long
    For Cbar = 1 To Application.CommandBars.Count
        On Error Resume Next
        Application.CommandBars(Cbar).FindControl(ID:=19, _
                      Recursive:=True).Enabled = False
        On Error GoTo 0
    Next Cbar
End Sub

Screenshots Worksheet Menu Bar Excel 2003 with ID numbers

Check out this Add-in (CommandBar Tools) to find all the ID numbers for every commandbar/control http://erlandsendata.no/?p=2694

Other useful examples

1) How do I run the code automatic when I activate or deactivate a workbook

You can use this events in the ThisWorkbook module.

Private Sub Workbook_Activate()
 'Your code to change Enabled to False
 'Or the name of your macro
End Sub

Private Sub Workbook_Deactivate()
 'Your code to change Enabled to True
 'Or the name of your macro
End Sub

Or use this two events

Private Sub Workbook_Open()
 'Your code to change Enabled to False
 'Or the name of your macro
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 'Your code to change Enabled to False
 'Or the name of your macro
End Sub

2) Hide the FormulaBar and StatusBar

Application.DisplayFormulaBar = False
Application.DisplayStatusBar = False

3) Disable the shortcuts

You can use Application.OnKey to do this. See the VBA help for more information or this page
Disable key or key combination or run a macro if you use it

Example to disable Ctrl c (the Copy shortcut)

Application.OnKey "^c", ""

to restore use

Application.OnKey "^c"

4) Other tips

Disable right clicking on the worksheet menu bar and other bars in 97 SR1 and above.

On Error Resume Next
Application.CommandBars("Toolbar List").Enabled = False
On Error GoTo 0

This prevents users double-clicking on the Toolbar area to open the (Customize Toolbars) dialog in Excel 2002 and above. Application.CommandBars.DisableCustomize = True

If you want to remove "Type a question for Help" on the Worksheet Menu Bar you can use this in Excel 2002 and above. True = hidden and False = visible
Application.CommandBars.DisableAskAQuestionDropdown = True

You can use this to avoid the error in Excel 97-2000

If Val(Application.Version) > 9 Then
     CallByName CommandBars, "DisableCustomize", VbLet, True
     CallByName CommandBars, "DisableAskAQuestionDropdown", VbLet, True
End If

5) Reset a commandbar

Application.CommandBars("Worksheet Menu Bar").Reset

Resetting a built-in control restores the actions originally intended for the control and resets each of the control's properties back to its original state. Resetting a built-in command bar removes custom controls and restores built-in controls.

6) Reset all menus in Excel

If you have problems to get your Excel menu's look normal you can always do this if resetting commandbars is not fixing it like I show you above.

Maybe you have a corrupt or bloated xlb file *normal* size is < 30 kb.
The .xlb file has all Toolbar customization in it.

1) Close Excel
2) Do a search for .xlb in Windows (Use: search hidden files and folders)
3) Rename or delete the .xlb file or files (In 2002 the name = Excel10.xlb)
4) Start Excel and see if it is fixed now

Deleting the file or renaming will do no harm to your system Excel will create a new file for you. (You lost your customization remember that) If you make your own toolbars or add buttons to other toolbars this file is important (backup it so you can restore it)

Ron de BruinExcel Automation