VBA Express : Excel - Disable Command Bars or Command Bar Controls

Excel

Disable Command Bars or Command Bar Controls

Ease of Use

Easy

Version tested with

2002

Submitted by:

Jacob Hilderbrand

Description:

An example of how to disable specific Command Bars or a single Control on the Command Bar.

Discussion:

You have an Excel program and you want to disable certain Command Bars, or maybe just a few Controls on the Command Bar.

Code:

instructions for use

			Option Explicit 
 
Sub DeactivateIt() 
     
    With Application.CommandBars("Worksheet Menu Bar") 
        .Controls("&Edit").Enabled = False 
        .Controls("&Window").Visible = False 
        With .Controls("&File") 
            .Controls("&Print...").Enabled = False 
            .Controls("&Print Preview").Enabled = False 
        End With 
    End With 
    Application.CommandBars("Drawing").Enabled = False 
    Application.CommandBars("Standard").Controls("&Save").Enabled = False 
     
End Sub 
 
Sub ActivateIt() 
     
    With Application.CommandBars("Worksheet Menu Bar") 
        .Controls("&Edit").Enabled = True 
        .Controls("&Window").Visible = True 
        With .Controls("&File") 
            .Controls("&Print...").Enabled = True 
            .Controls("&Print Preview").Enabled = True 
        End With 
    End With 
    Application.CommandBars("Drawing").Enabled = True 
    Application.CommandBars("Standard").Controls("&Save").Enabled = True 
     
End Sub

How to use:

Open Excel.
Alt + F11 to open VBE.
Insert | Module.
Paste the code there.
Close VBE (Alt + Q or press the X in the top right hand corner).
Tools | Macro | Macros (Select ActivateIt or DeactivateIt the press Run).

Test the code:

Refer to the "How To Use" section.
This code will active/deactivate the Drawing Toolbar, Print, Print Preview, Edit and Window menus and the Save Icon.
Download the attachment for a working example.

Sample File:

Disable Command Bars.ZIP 7.69KB

Approved by mdmackillop

This entry has been viewed 173 times.