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)
Code:
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
Code:
CommandBars("standard").Visible = False æor True!
Using the following code, however, disables the command bar and it can only be reset with code (AFAIK)
Code:
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
Code:
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!!
Code:
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
Code:
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
Code:
Sub Disable_Cut_Keyboard()
Application.OnKey "^x", ""
End Sub
To re-enable the keyboard shortcut use this code
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
Code:
Sub DisableCut_CtrlX()
'Note the use of .Disable rather than .Clear
CustomizationContext = NormalTemplate
FindKey(BuildKeyCode(wdKeyControl, wdKeyX)).Disable
End Sub
Code:
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.
Code:
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)
;-)
