PDA

View Full Version : Disable Menu Bar ("HOME" AND "DATA") Menu in EXCEL 2007



neeraj.singh
11-04-2019, 03:44 AM
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
11-05-2019, 11:25 AM
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
11-07-2019, 08:32 AM
Custom UI Editor for Microsoft Office



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

https://github.com/fernandreu/office-ribbonx-editor/releases/tag/v1.5.1

Bob Phillips
11-07-2019, 09:03 AM
Custom UI Editor for Microsoft Office



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

https://github.com/fernandreu/office-ribbonx-editor/releases/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
11-07-2019, 11:40 AM
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

25380

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 :thumb

neeraj.singh
11-07-2019, 09:28 PM
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
11-07-2019, 10:03 PM
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
11-08-2019, 04:02 AM
Try it -- you'll like it :thumb

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
11-08-2019, 04:23 AM
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/previous-versions/office/developer/office-2007/aa338202(v=office.12)?redirectedfrom=MSDN

Artik

GoofyMan
04-17-2023, 01:28 PM
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
04-17-2023, 03:48 PM
I guess you didn't notice that the admin separated your post into a separate thread (http://www.vbaexpress.com/forum/showthread.php?70779-Disable-Power-Pivot-Menu). Please continue the discussion in the new thread.

Artik

GoofyMan
04-17-2023, 05:17 PM
I didn't notice. Thanks Artik!