Disable Commandbars

[merlynknight]

I have a VBA program that was written using Excel 2000 and recently we
have installed it on XP machines with Excel 2003.

Everything works fine except I cannot prevent the user from clicking
on the menu bar and getting the option to show built-in menu's

My program creates its own Menu and toolbar and I don't want the user
to be able to activate any other command bars.
In Excel 2000 I used the code

" Application.CommandBars("toolbar list").Enabled = False"

and that worked great for Excel 2000 and previous versions but it does
not work for Excel 2003.
I searched Microsoft and Excel help and all I can find is how to
prevent a toolbar from being changed.
I believe that the above code is undocumented and I found it in this
forum 3 or 4 years ago.

Any help will be appreciated.
Merlyn

 

[Rob Bovey]

Hi Merlyn,

There was a new property added to the CommandBars object in Excel 2002
that allows you to prevent this. To disable customization of command bars
you would use:

CommandBars.DisableCustomize = True

and to enable customization you would use:

CommandBars.DisableCustomize = False

If you application still needs to run under Excel 2000, you'll have to use
late binding and a version check to run this or Excel 2000 will give you an
error. The code to do this would look like the following:

Dim objCBars As Object
Set objCBars = Application.CommandBars
If Val(Application.Version) >= 10 Then
objCBars.DisableCustomize = True
End If

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *