Consulting

Results 1 to 9 of 9

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

  2. #2
    VBAX Tutor
    Joined
    Dec 2008
    Posts
    298
    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 Wizard
    Joined
    Apr 2007
    Posts
    6,760
    Location
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    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)
    (multiple files can be selected while holding Ctrl key) / 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 xld's Avatar
    Joined
    Apr 2005
    Posts
    25,095
    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 Wizard
    Joined
    Apr 2007
    Posts
    6,760
    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

    ------------------------------------------------------------------------------------------------------------------------
    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)
    (multiple files can be selected while holding Ctrl key) / 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.

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,095
    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 xld; 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 Tutor
    Joined
    Dec 2008
    Posts
    298
    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

Posting Permissions

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