[Solved] Menu Bars: Is there any easier way to do this?

elopez · Jul 14th 2003

I am trying to make a menu bar (named CTSMenuBar) that has two menus. One menu is a custom menu and the other is the file menu with everything invisible but the Print, Print Preview and Print Area menu items. The following is the code I found that eliminates everything off the file menu except for what I want but it seems long. If possible I would like to know if it can be done in a more expedient manner since I may have to do it in several areas of my project. Also if anybody knows how, I would like to eliminate the recent documents area of the File Menu as well.

CommandBars("Worksheet Menu Bar").Controls(1).Copy Bar:=CommandBars("CTSMenuBar")
CommandBars

("CTSMenuBar").FindControl(ID:=30002).Controls("&Save").Visible = False

CommandBars ("CTSMenuBar").FindControl(ID:=30002).Controls("&Save As...").Visible = False

CommandBars("CTSMenuBar").FindControl(ID:=30002).Controls("&Save As Web Page...").Visible = False

CommandBars("CTSMenuBar").FindControl(ID:=30002).Controls("&Save Workspace...").Visible = False

CommandBars("CTSMenuBar").FindControl(ID:=30002).Controls("&Search...").Visible = False

CommandBars("CTSMenuBar").FindControl(ID:=30002).Controls("&Open...").Visible = False

CommandBars("CTSMenuBar").FindControl(ID:=30002).Controls("&Web Page Preview").Visible = False

CommandBars("CTSMenuBar").FindControl(ID:=30002).Controls("&New...").Visible = False

CommandBars("CTSMenuBar").FindControl(ID:=30002).Controls("&Page Setup...").Visible = False

CommandBars("CTSMenuBar").FindControl(ID:=30002).Controls("&Send To").Visible = False

CommandBars("CTSMenuBar").FindControl(ID:=30002).Controls("&Properties").Visible = False

CommandBars("CTSMenuBar").FindControl(ID:=30002).Controls("&Close").Visible = False

CommandBars("CTSMenuBar").FindControl(ID:=30002).Controls("&Exit").Visible = False:biggrin:

dkabambe · Jul 15th 2003

You could just try adding the controls you need rather than adding everything and hiding the ones you don't...

Code

Set myBar = Application.CommandBars("CTSMenuBar")
    Set myControl1 = myBar.Controls.Add(msoControlPopup)
    myControl1.Caption = "&File"
    
        Set myControl2 = myControl1.Controls.Add(ID:=109)
        Set myControl2 = myControl1.Controls.Add(ID:=4)
        
        Set myControl2 = myControl1.Controls.Add(msoControlPopup)
        myControl2.Caption = "Prin&t Area"
        
            Set myControl3 = myControl2.Controls.Add(ID:=364)
            Set myControl3 = myControl2.Controls.Add(ID:=1584)

Display More

HTH, Dzinja

elopez · Jul 15th 2003

dkabambe,
I actually did end up doing that; I just had to find what the ID numbers were. Thanks for responding. . .:biggrin:

learn · Jul 16th 2003

Hello

I am quite interested to find out what are the code to turn off CTSMenubar (in dkabambe's example) upon closed of the excel file.

How to identify the control ID of the specify toolbar ?

elopez · Jul 16th 2003

Learn
This is the code I used to delete the CTSMenubar. The code didn't delete Excel's default menubar it just created another one and made it visible. Excel will only show one menubar at a time so when you delete the CTS Menubar it automatically replaces it with the default.

Sub DeleteMenuBar()
On Error Resume Next
CommandBars("CTSMenuBar").Delete
On Error GoTo 0
End Sub

[Solved] Menu Bars: Is there any easier way to do this?

Participate now!

Share