View Full Version : Remove the default Excel menubar

01-02-2012, 02:12 PM
Welcome Everyone,
I would like to replace the default excel menus with my homebrew own menus.
Here is the code for creating new menu:

Sub newmenu()
Dim cmdBar As CommandBar
Set cmdBar = Application.CommandBars("Worksheet Menu Bar")

Dim cmdBarControl As CommandBarControl
Set cmdBarControl = cmdBar.Controls.Add(Type:=msoControlPopup, temporary:=True)

With cmdBarControl
.Caption = "Cow"
With .Controls.Add(Type:=msoControlButton)
.Caption = "Add new Cow"
.OnAction = "form_start"
End With
End With
End Sub

And now I want to hide or remove the default menubar, but I haven't solved it yet.
I 've found a solution for enable menus, but it coundn't remove the menu, but it would be disabled status.

CommandBars("Tools").Enabled = False

But I would like to remove completely the default menu.
How can I reach this?

Any help would be greatly appreciated!
Very thanks.

01-02-2012, 03:32 PM
Taking away Excel's normal controls is a perilous path.
CommandBars is a property of the whole Application, not just your workbook.

How does the user get back their Excel functionality if
1) your code crashes?
2) Excel crashes?
3) Windows crashes?
4) their computer crashes?

How do they get their menu bar back when they switch to a different workbook without closing yours?

To answer your question, look through the properties of a CommandBar object. The obvious one will do what it says it does.

(If you cant figure out which property is "the obvious one", your code is going to hurt someone.)

01-02-2012, 07:10 PM
What version of Excel? 2007/2010 do things a little differently

What is the reason that you are trying to hide the normal Excel functionality?


01-03-2012, 06:16 AM
Dim cbar As CommandBar
For Each cbar In Application.CommandBars
cbar.Enabled = False

and reset with

Dim cbar As CommandBar
For Each cBar In Application.CommandBars

01-03-2012, 10:51 AM
Very thanks your idea. I'm newbie to VBA so you persuaded me not to twit these command bar:)

I accept your suggestion , and I change the menubar's status to disable as xld wrote his comment.

Thanks again.