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

  • 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")

    ("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:

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

    HTH, Dzinja

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

  • 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 ?


  • 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
    On Error GoTo 0
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!