Application.Commandbars ("xxx").Enabled = False does not work?

Question

I have the following code in the Private Sub Workbook_Open ()

... (there's a bunch of code that preceeds this)

    Application.CommandBars("Worksheet Menu Bar").Enabled = False

    Application.CommandBars("Cell").Enabled = False

    Application.CommandBars("Sheet").Enabled = False

    Application.CommandBars("Ply").Enabled = False

    Application.CommandBars("Row").Enabled = False

    Application.CommandBars("Column").Enabled = False

    Application.CellDragAndDrop = False

but nothing happens - the right click still works, the ribbon is still visible, etc.

What's missing?

kala sha

Answer 1

Sorry, my mistake.  I thought you were refering to Toolbars.

Just a check, are there any other events in the sheet you added that could be conflicting with your code?

Cheers

Rich

Answer 2

the Application.CommandBars does work in Excel 2007.  I use the same coding in other workbooks and there are no issues.

in this workbook, I added a worksheet and the mouse right click still shows options.  In the other worksheets - the application.commandbars (false) does work properly.  Just the worksheet that i added to the workbook is giving me grief.

kala

Answer 3

Don't think you can do that with Office 2007 or 2010. Have a look at:

http://msdn.microsoft.com/en-us/library/aa338202.aspx  (Part1)

http://msdn.microsoft.com/en-us/library/aa338199(office.12).aspx  (Part2)

http://msdn.microsoft.com/en-us/library/aa722523.aspx  (Part3) 

Part 2 is espically useful.

Welcome to Ribon xml H€LL.

Cheers Rich