Enable Disable Excel Command-Bar Options

Excel – Enable Disable Commands & Options

There are about 150+ options available with the code presented in this section.

The notable options are “Status Bar” and the Popup menu that appears when you right click on a cell or row etc.,

Using this code, those options can be switched on and off.

Sub List_Command_Bars()
    'Code by - author@officetricks.com
    
    Dim c As CommandBar, i As Double
    i = 1
    For Each c In CommandBars
        If VBA.Trim(c.Name) <> "" Then
            ThisWorkbook.Sheets(1).Cells(i, 1) = c.Name
            ThisWorkbook.Sheets(1).Cells(i, 2) = c.Enabled
            ThisWorkbook.Sheets(1).Cells(i, 3) = c.Visible
            ThisWorkbook.Sheets(1).Cells(i, 4) = c.Index
            i = i + 1
        End If
    Next
End Sub

But remember that not all the commands are available to change the value. There are certain values like default Excel Worksheet menu. This cannot be switch off. Otherwise Excel will not work properly.

Similarly there are few option that we can play around with this VBA code.