Hide menu items
Upcoming Power Excel Seminars
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Hide menu items

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    John G
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    How can I write vba code to hide different menu items? I want to make the "Send to" selection under "File" hidden or grayed out. I also want to gray out "Macro" under "Tools".

    John

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Brampton
    Posts
    328
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The menu items which are part of Excel application cannot be controlled programatically for permanent modification. The only option available, which is relatively limited in extent is the Customize menu item, which allows a reduced level of customization to respond to personal preferences and working habits.
    The possibility of disabling by code menu items exist only for This Workbook macros.

    [ This Message was edited by: corni on 2002-03-05 19:05 ]

    [ This Message was edited by: corni on 2002-03-06 19:44 ]

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Jefferson City, Missouri
    Posts
    382
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    This is what I did from the help here as well. Open VB editor and under General Declarations for This Workbook type the following:
    Dim CB As CommandBar
    Dim C As CommandBarControl
    Dim ID

    Then insert a module in This Workbook and type the following:

    Sub Disable_RightClick_Toolbar()
    ID = 30017
    For Each CB In Application.CommandBars
    Set C = CB.FindControl(ID:=ID, recursive:=True)
    If Not C Is Nothing Then C.Enabled = False
    Next
    Application.CommandBars("Toolbar List").Enabled = False
    Application.CommandBars("Visual Basic").Enabled = False

    End Sub

    Then insert a second one and type the following:

    Sub Enable_RightClick_ToolBar()

    ID = 30017
    For Each CB In Application.CommandBars
    Set C = CB.FindControl(ID:=ID, recursive:=True)
    If Not C Is Nothing Then C.Enabled = True
    Next
    Application.CommandBars("ToolBar List").Enabled = True
    Application.CommandBars("Visual Basic").Enabled = True

    End Sub

    Then in This Workbook Open event type:
    Disable_RightClick_Toolbar

    and in This Workbook Deactivate() type:
    Enable_RightClick_Toolbar.

    This worked great for me but their is one flaw and I hope whoever else reads this can help me fix this. But after you do the above and then save and re-open the file, the macro will be greyed under the tools menu, but if you leave that file open and then open a second file and then close it the macro will be enabled again. I haven't figured out how to stop that from happening but as long as the user doesn't know about the second file then your macro will remain greyed. As far as the other menu item I don't know but maybe this will give you a head start in modifying the code for the other item.

    HTH

    I appreciate the help from everyone at Mr. Excel.

    viper

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Jefferson City, Missouri
    Posts
    382
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Forgot to mention. For both of the module inserts you will need to put the following before the code as well as in the General Declarations for This Workbook:
    Dim CB As CommandBar
    Dim C As CommndBarControl
    Dim ID

  5. #5
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    For graying out the File|Send To, use this code (you don't need to set the commandbar or the first popup, but I've done it as an example)



    Dim CmdBar As CommandBar
    Dim CmdBarControl As CommandBarPopup
    Dim SendToControl As CommandBarPopup

    Set CmdBar = Application.CommandBars("Worksheet Menu Bar")
    Set CmdBarControl = CmdBar.Controls("&File")
    Set SendToControl = CmdBarControl.Controls("Sen&d To")

    'Remember to set this to True before exiting Excel
    SendToControl.Enabled = False


    For the Tools|Macro button do the same except put in "&Tools" instead of "&File"
    and "&Macro" instead of "Sen&d To".

    ***Remember to reset these values to True before exiting Excel.

    Cheers


    _________________
    [b] Mark O'Brien

    [ This Message was edited by: Mark O'Brien on 2002-03-04 22:40 ]

    [ This Message was edited by: Mark O'Brien on 2002-03-04 22:41 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com