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.
