It has often been a requirement to disable various command bars or controls in order to control how a user interacts with the host application.  This FAQ covers some of the methods that enable us to remove functionality and then to add it back in as required.

COMMAND BARS : DISABLE OR HIDE?

In order to obtain the visible Command bar Names and Indexes (I know!!) the following can be run in either Excel or Word (possibly PPT & Access too)

Sub GetCommandBarNames()
Dim ctrl As CommandBar
For Each ctrl In Application.CommandBars
    If ctrl.Visible Then
        Debug.Print ctrl.Name, ctrl.Index
    End If
Next
End Sub

To get the full list just omit the ôIfàThenàEnd Ifö

There are two ways to make CommandBars disabled to users.  You can either hide the bar or disable it.  In essence there is no big difference - both will remove the command bar from the user interface (the visible bits of the program youÆre in!)

The difference lays in the fact that setting the command barÆs visible property to false does what it suggests.  The command bar can still be made visible, however, through the normal manual method, VIEW>TOOLBARS

This works in both Word & Excel
CommandBars("standard").Visible = False    æor True!

Using the following code, however, disables the command bar and it can only be reset with code (AFAIK)

CommandBars("standard").Enabled = False    æor True!

There is an exception to this.  The menu bar (ôMenu Barö, 36 in Word; ôWorksheet Menu Barö, 1 in Excel) cannot be set to Visible = False.  It can only be removed using the Enabled property.

MENUS & SUB MENUS

In order to disable the File menu in Excel the following could be used

Sub DisableMenu()
CommandBars("Worksheet Menu Bar").Controls("File").Enabled = False    æor True to enable
End Sub

By way of example, in order to disable a sub menu the following could be used.  In this case the option disabled is Properties.  I have used this in the past while using a custom property to hold data (num of used rows) so that it is always stored and updated within the book but cannot be accessed easily by the user.  WhoÆd think to look there anyway!!

Sub DisableSubMenu()
CommandBars("File").Controls("Properties").Enabled = False    æor True to enable
End Sub

Note how ôFileö becomes the Command Bar and ôPropertiesö the control.

Beware that controls like Open, New etc. need the ôàö as they have shown in the menu
i.e. Controls("Openà")

COMMAND BAR CONTROLS

In Excel or Word the following will disable all occurrences of the CUT control.  It does not, however, disable the CTRL+X key combination which is covered later.

'Use the control IDs identified
'to disable of enable individual controls
'throughout XL
Sub Find_Disable_Commands()
Dim myControls As CommandBarControls
Dim ctl As CommandBarControl
    Set myControls = CommandBars.FindControls _
        (Type:=msoControlButton, ID:=21)    '21 = cut
    For Each ctl In myControls
        ctl.Enabled = False
    Next ctl
End Sub

See my FAQ :
What are the CommandBar Control Names and IDs in Word & Excel   FAQ707-4727
to see how you can obtain all the control Names and IDs

In Excel to disable the CTRL+X key combo it is necessary to use the OnKey method (see help for full details) as follows

Sub Disable_Cut_Keyboard()
Application.OnKey "^x", ""
End Sub

To re-enable the keyboard shortcut use this code

Sub Enable_Cut_Keyboard()
Application.OnKey "^x"
End Sub

Note : You will need to consider applying the code to the workbookÆs Open, BeforeClose, Activate, Deactivate events as appropriate.

The key (no pun intended) here is the second argument of the OnKey method.  To disable the key combination an empty string is passed as the procedure.  To reset to the original, removing any custom assignments, this argument is omitted altogether.

In order to do this in Word it is necessary to use routines as follows

Sub DisableCut_CtrlX()
'Note the use of .Disable rather than .Clear
CustomizationContext = NormalTemplate
FindKey(BuildKeyCode(wdKeyControl, wdKeyX)).Disable
End Sub

Sub EnableCut_CtrlX()
CustomizationContext = NormalTemplate
KeyBindings.Add KeyCode:=BuildKeyCode(wdKeyControl, wdKeyX), _
    KeyCategory:=wdKeyCategoryCommand, Command:="editcut"
End Sub

Unfortunately with this code we donÆt have nice events like Workbook_Activate() & Workbook_Deactivate() in which to add it and we need to look into trapping document events which IÆm not covering in this FAQ but this link should be of considerable help
http://word.mvps.org/FAQs/MacrosVBA/AppClassEvents.htm

OTHER VAGUELY INTERESTING BIT!

This is an example of adding a command control to the standard toolbar of Word using the controlÆs ID rather than its name.
Taken directly from Words VBA help on CustomizationContext, the example adds the File Versions button to the Standard toolbar. The command bar customisation is saved in the template attached to the active document.

CustomizationContext = ActiveDocument.AttachedTemplate
Application.CommandBars("Standard").Controls.Add _
    Type:=msoControlButton, _
    ID:=2522, Before:=8

Thanks to Word Heretic (Steve Hudson) and Tom Ogilvy, both from MS Newsgroups, for helping me fill in a couple of gaps to complete this FAQ

(FAQ STILL UNDER CONSTRUCTION?  ANY COMMENTS FOR ADDITIONS OR POINTING OUT MISTAKES ARE APPRECIATED)

Back to VBA Visual Basic for Applications (Microsoft) FAQ Index
Back to VBA Visual Basic for Applications (Microsoft) Forum

My Archive

My FAQ Archive

Come Join Us!

Posting Guidelines

Jobs

VBA Visual Basic for Applications (Microsoft) FAQ

Office / VBA General

My Archive

Join Tek-Tips® Today!

Join Tek-Tips^® Today!