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.
Talk To Other Members