Consulting

Results 1 to 12 of 12

Thread: Disable Menu Bar ("HOME" AND "DATA") Menu in EXCEL 2007

  1. #1

    Post Disable Menu Bar ("HOME" AND "DATA") Menu in EXCEL 2007

    I am using Microsoft Excel 2007.
    I want to disable "HOME" and "DATA" Menu bar I Microsoft Excel 2007 using VBA.
    I'm using below code but not work.

    Private Sub Workbook_Activate()
    Application.CommandBars("Worksheet Menu Bar").Controls("Data").Enabled = False
    End Sub
    Last edited by Aussiebear; 04-17-2023 at 11:46 AM. Reason: Added code tags to supplied code

  2. #2
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    You should hide the ribbon tabs in the XML.
    Open your file in Custom UI Editor for Microsoft Office, select Insert / Office 2007 Custom UI Part from the menu, paste the code in the window on the right:
    <customUI  xmlns="http://schemas.microsoft.com/office/2006/01/customui" >
        <ribbon >
            <tabs >
                <tab 
                    idMso="TabHome"
                    visible="false"/>
                <tab 
                    idMso="TabData"
                    visible="false"/>
            </tabs >
        </ribbon >
    </customUI >
    Artik

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,702
    Location
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,439
    Location
    Quote Originally Posted by Paul_Hossler View Post
    Thanks for that Paul, I didn't know anyone had released an improved version of the CustomUI. That tool has been frustrating me for years, it will be good if this one improves on it.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,702
    Location
    Quote Originally Posted by xld View Post
    Thanks for that Paul, I didn't know anyone had released an improved version of the CustomUI. That tool has been frustrating me for years, it will be good if this one improves on it.

    Yes, the old one was VERY frustrating and took a lot of effort and extra care to use

    This one is very nice and appears (on the surface UI) to be similar to the old one. Internally, it's a vast improvement

    Capture.JPG

    Fernando Andreu is actively maintaining and enhancing it. Seems like a very nice person who's proud of his work (as he should be)

    I offered a few minor suggestions which were considered and some are being implemented

    Try it -- you'll like it
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6

    Unhappy Disable or Lock "HOME" or "DATA" Menus

    With using this code, Menu bar have hide.
    but i don't want to hide, I want Disable or Lock "HOME" or "DATA" Menus with all ribbon button/Command using VBA Programming or VBA Code.

  7. #7
    When I am using below codes in Excel 2003 version, then it properly workbut not work in Excel-2007 version why?

    Private Sub Workbook_Activate()
        Application.CommandBars("Worksheet menu bar").Enabled = False
        Application.CommandBars("Worksheet Menu Bar").Controls("File").Enabled = False
        Application.CommandBars("Worksheet Menu Bar").Controls("Edit").Enabled = False
        Application.CommandBars("Worksheet Menu Bar").Controls("VIEW").Enabled = False
        Application.CommandBars("Worksheet Menu Bar").Controls("INSERT").Enabled = False
        Application.CommandBars("Worksheet Menu Bar").Controls("Format").Enabled = False
    End Sub
    Same like that, what are the VBA Programmig/Code to use in Excel-2007 version by which we Disable/Lock the Menu Bar in Excel-2007 version.
    Last edited by Aussiebear; 04-17-2023 at 11:50 AM. Reason: Added code tags to supplied code

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,439
    Location
    Quote Originally Posted by Paul_Hossler View Post
    Try it -- you'll like it
    It doesn't do that weird scrolling after change to the XML, it has line numbers, it has find and replace. I'm sold already. Unless I find some big problem, I'm not going back.
    Last edited by Bob Phillips; 11-08-2019 at 08:52 AM.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #9
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    neeraj.singh, since the 2007 version, in addition to changing the appearance, the philosophy of menu controls has changed. Because the issue is very extensive, I suggest you start the journey with this article:
    https://docs.microsoft.com/en-us/pre...ectedfrom=MSDN

    Artik

  10. #10
    Using the same method as suggested above, can anyone please help by telling me what is the id, idMso of the "Power Pivot" tab?

    I am trying to make the "Power Pivot" menu item invisible, but can't find the ID

    This works for hiding the Data tab:
    idMso="TabData", visible="false

    This doesn't work for hiding the Power Pivot tab:
    idMso="TabPower Pivot", visible="false


    Thanks!

  11. #11
    VBAX Mentor
    Joined
    Dec 2008
    Posts
    404
    Location
    I guess you didn't notice that the admin separated your post into a separate thread. Please continue the discussion in the new thread.

    Artik

  12. #12
    I didn't notice. Thanks Artik!

Posting Permissions

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