Hide "View" menu

[candie213]

Is it possible to disable the "View" menu in a protected workbook using vba? Or disable the entire ribbon?

[lynnnow]

By protected do you mean the windows are protected or the structure is protected? Also, do you mean the cells are locked and you want to hide the view menu?

Something you can try is:

[VBA]Sub DisableViewMenu()
If ActiveWorkbook.ProtectWindows = True Then
CommandBars.ActiveMenuBar.Controls("View").Visible = False
Else
CommandBars.ActiveMenuBar.Controls("View").Visible = True
End If
End Sub[/VBA]

HTH

Lincoln

[candie213]

Does this work in 2007

[candie213]

By protected do you mean the windows are protected or the structure is protected? Also, do you mean the cells are locked and you want to hide the view menu?

Something you can try is:

[vba]Sub DisableViewMenu()
If ActiveWorkbook.ProtectWindows = True Then
CommandBars.ActiveMenuBar.Controls("View").Visible = False
Else
CommandBars.ActiveMenuBar.Controls("View").Visible = True
End If
End Sub[/vba]

HTH

Lincoln

Yes, the cells and workbook is protected. Does this work for 2007? I tried it and it is not working. I have a very detailed worksheet that I would like a user to be unable to use 'View' -- 'Page Break Preview' control. If they go in and try to change it screws with how each page prints.

[lynnnow]

I've tried it out in Office 2003, but give it a go and see. I have no way of knowing if it works in 2007.

[Bob Phillips]

You cannot disable ribbon items like that in Excel 2007. You can hide the whole ribbon, but not built-in tabs.

[candie213]

You cannot disable ribbon items like that in Excel 2007. You can hide the whole ribbon, but not built-in tabs.

How do you disable to ribbon?

[Bob Phillips]

By disable, I meant hide. You do it with

[vba]

Application.SendKeys "^{F1}"
[/vba]

but any user could revert it.

[candie213]

By disable, I meant hide. You do it with

[vba]

Application.SendKeys "^{F1}"
[/vba]

but any user could revert it.

That hides the ribbon but the tabs are still available to use. I would like to disable the entire toolbar.

[Paul_Hossler]

You can use the CustomUI Editor

[vba]
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
　
<!-- **************************************************************************-->
<!-- ****Disable 'Exit Excel' and 'Excel Options' on the Office button menu****-->
<!-- **************************************************************************-->
<commands>
<!-- Disable Excel Options on the Office button menu-->
<command idMso="ApplicationOptionsDialog"enabled="false"/>
<!-- Disable Exit Excel on the Office button menu-->
<command idMso="FileExit"enabled="false"/>
</commands>
　
　
<!-- *******************************************************************-->
<!-- **********Set startFromScratch to true to hide the ribbon**********-->
<!-- **********Hide New, Open and Save on the Office button menu********-->
<!-- *******************************************************************-->
　
<!-- Set startFromScratch to true to hide the ribbon-->
<ribbon startFromScratch="true">
<!-- startFromScratch="true" hides all of the ribbon tabs and it hides the QAT. -->
<!-- It also hides most of the commands on the Office button menu, but for some -->
<!-- reason, it does not hide the 'New', 'Open' and 'Save' commands. -->
<!-- So if you want to hide them you have to add this to your RibbonX file: -->
　
</ribbon>
　
</customUI>
[/vba]

Is this what you were looking for?

Paul

[candie213]

You can use the CustomUI Editor

[vba]
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
　
<!-- **************************************************************************-->
<!-- ****Disable 'Exit Excel' and 'Excel Options' on the Office button menu****-->
<!-- **************************************************************************-->
<commands>
<!-- Disable Excel Options on the Office button menu-->
<command idMso="ApplicationOptionsDialog"enabled="false"/>
<!-- Disable Exit Excel on the Office button menu-->
<command idMso="FileExit"enabled="false"/>
</commands>
　
　
<!-- *******************************************************************-->
<!-- **********Set startFromScratch to true to hide the ribbon**********-->
<!-- **********Hide New, Open and Save on the Office button menu********-->
<!-- *******************************************************************-->
　
<!-- Set startFromScratch to true to hide the ribbon-->
<ribbon startFromScratch="true">
<!-- startFromScratch="true" hides all of the ribbon tabs and it hides the QAT. -->
<!-- It also hides most of the commands on the Office button menu, but for some -->
<!-- reason, it does not hide the 'New', 'Open' and 'Save' commands. -->
<!-- So if you want to hide them you have to add this to your RibbonX file: -->
　
</ribbon>
　
</customUI>
[/vba]

Is this what you were looking for?

Paul

I opened your workbook and that is exactly what I need.

Do I add as a Module? or do I add the code to the Workbook?

I tried to copy and paste but I got error messages.

[Bob Phillips]

No, Paul is talking about the XML that defines any ribbon customisations. Any Excel 2007 file is essentially a zip file with lots of compenent iles, one of which is the CustomUI file, whbich holds the XML such as Paul showed.

You can get the CustomUI editor, which edits that file, at http://openxmldeveloper.org/articles...muieditor.aspx.

[Paul_Hossler]

Get the CustomUI editor, and open the Hello.xlsm file. I just pasted in a fragment of one of the Sample files that are available there

You'll see the XML code above in the CustomUI section.

Writing Fluent XML is do-able, but finicky. I only use the basic stuff, and even then it usually takes 3 tries to get it right


In http://www.vbaexpress.com/forum/showthread.php?t=22497 there are some very good references, post#2

Especially the excellent Ken Puls and Stephen Bullen exerpts from their books (both recommended)


Paul