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

[neeraj.singh]

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

[Artik]

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

[Paul_Hossler]

Custom UI Editor for Microsoft Office



https://github.com/fernandreu/office-ribbonx-editor

https://github.com/fernandreu/office...ses/tag/v1.5.1

[Bob Phillips]

Custom UI Editor for Microsoft Office



https://github.com/fernandreu/office-ribbonx-editor

https://github.com/fernandreu/office...ses/tag/v1.5.1

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.

[Paul_Hossler]

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

[neeraj.singh]

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.

[neeraj.singh]

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.

[Bob Phillips]

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.

[Artik]

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

[GoofyMan]

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!

[Artik]

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

[GoofyMan]

I didn't notice. Thanks Artik!