vba disable customize

Question

In Excel for Windows apparently you can disable customzation of toolbars by using this line of code: 

CommandBars("Toolbar List").Enabled = False

Could you please tell me now to disable customization of toolbars in Excel for Mac 2011?

Thanks

briano216

Answer 1

First - that doesn't work in WinXL 2007/2010, and wasn't reliable in XL2003 (it was deprecated and replaced by the CommandBars.DisableCustomize property in XL02). 

Second - it doesn't actually disable customization of toolbars - it simply makes it more difficult, by disabling the UI toolbar that lists custom toolbars. There are still at least a couple of ways of easily circumventing it.

Third - disabling customization of toolbars is an incredibly aggressive/intrusive act, unless you're only doing it on your personal machine. I use an almost completely customized environment - my startup add-in customizes the Worksheet and Chart menu bars, completely replaces the Standard menu bar, modifies the pop-up menus that you get when right-clicking, and creates a dozen or so custom menu bars, of which most are context-sensitive and show up only when needed. It also customizes the Visual Basic Editor menus.

I have created dozens of Excel application workbooks that create custom toolbars that are enabled when the workbook is active, and disabled and hidden when the workbook is not.

I appreciate when someone sends me an Excel application with custom toolbars that show up when I'm using that workbook and that make it easier to use. However, if anyone ever interfered with my customizations, or ability to customize, I'd have their hides (or at least, I'd never open a workbook of theirs again).

Answer 2

Yes, I tried it. It gave me error "Run-time error '5'. Invalid procedure call.

Answer 3

I take your point. What I'm wanting to do is disable all cut tools and paste tools, but retain copy and provide a paste that pastes values only, not formatting. I have found code that disables all the cut and paste tools/options but the one I have not been able to disable is the cut and paste within the customize window. Is there a way I can disable just those tools within the customize window? 

I like your suggestion of creating my own toolbar. I will put the paste (values only) tool on that.

Answer 4

Have you tried doing it exactly the same way? What does not work? Do you get errors? Which one?