Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

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
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

VBA Visual Basic for Applications (Microsoft) FAQ

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)

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

Close Box

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close