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.
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
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
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
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)
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
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
Check out this Add-in (CommandBar Tools) to find all the ID numbers for every commandbar/control http://erlandsendata.no/?p=2694
1) How do I run the code automatic when I activate or deactivate
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
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)