INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips now!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- One-Click Access To Your
- Automated Signatures
On Your Posts
- Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
"...It's fun to see others going through the same stuff I did and be able to help. It's also a way for me to stay sharp and not lose the stuff I've learned..."
Where in the world do Tek-Tips members come from?
Office / VBA General
How can I Disable/Enable CommandBars and/or Controls
Posted: 11 Feb 04 (Edited 12 Feb 04)
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)
Dim ctrl As CommandBar
For Each ctrl In Application.CommandBars
If ctrl.Visible Then
Debug.Print ctrl.Name, ctrl.Index
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
CommandBars("Worksheet Menu Bar").Controls("File").Enabled = False ‘or True to enable
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!!
CommandBars("File").Controls("Properties").Enabled = False ‘or True to enable
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
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
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
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
Application.OnKey "^x", ""
To re-enable the keyboard shortcut use this code
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
'Note the use of .Disable rather than .Clear
CustomizationContext = NormalTemplate
CustomizationContext = NormalTemplate
KeyBindings.Add KeyCode:=BuildKeyCode(wdKeyControl, wdKeyX), _
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
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
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
Join Tek-Tips® Today!
Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.
Here's Why Members Love Tek-Tips Forums:
- Talk To Other Members
- Notification Of Responses To Questions
- Favorite Forums One Click Access
- Keyword Search Of All Posts, And More...
Register now while it's still free!
Already a member? Close this window and log in.
Join Us Close