PDA

View Full Version : Hide "View" menu



candie213
05-03-2010, 08:41 AM
Is it possible to disable the "View" menu in a protected workbook using vba? Or disable the entire ribbon?

lynnnow
05-03-2010, 09:56 AM
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:

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

HTH

Lincoln

candie213
05-03-2010, 10:06 AM
Does this work in 2007

candie213
05-03-2010, 10:07 AM
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:

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

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
05-03-2010, 10:08 AM
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
05-03-2010, 11:28 AM
You cannot disable ribbon items like that in Excel 2007. You can hide the whole ribbon, but not built-in tabs.

candie213
05-03-2010, 11:31 AM
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
05-03-2010, 11:45 AM
By disable, I meant hide. You do it with



Application.SendKeys "^{F1}"


but any user could revert it.

candie213
05-03-2010, 12:01 PM
By disable, I meant hide. You do it with



Application.SendKeys "^{F1}"


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
05-03-2010, 01:49 PM
You can use the CustomUI Editor


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


Is this what you were looking for?

Paul

candie213
05-03-2010, 02:35 PM
You can use the CustomUI Editor


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


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
05-03-2010, 02:41 PM
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/customuieditor.aspx.

Paul_Hossler
05-03-2010, 03:38 PM
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