Hide menus etc in Excel 2007

[Sandy]

How can I convert the following to hide the menus and ribbon in Excel
2007?

**********
Dim Wksht As Worksheet

With Application
.DisplayFormulaBar = False
.CommandBars("Worksheet Menu Bar").Enabled = False
.CommandBars("Standard").Visible = False
.CommandBars("Formatting").Visible = False
.CommandBars("Drawing").Visible = False
End With
**********

Thanks
Sandy

 

[Ron de Bruin]

See this page Sandy
http://www.rondebruin.nl/ribbon.htm

See the xml examples (2)

.DisplayFormulaBar = False

This is the same in 2007

 

[Rick Rothstein \(MVP - VB\)]

And, if you want to use a single routine to toggle the visibility of the
Ribbon and Formula bar between visible and not visible with each call (so
you don't have to remember or check what state it is currently in), you can
use this macro....

Sub ToggleRibbonAndFormulaBar()
ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon""," & _
CStr(Not Application.DisplayFormulaBar) & ")"
Application.DisplayFormulaBar = Not Application.DisplayFormulaBar
End Sub

Rick

 

[Sandy]

Thanks Ron

Perfect - can I ask another.........

I notice there is a 'new sheet' tab in 2007 - don't suppose you know how to
hide that or at least disable it?

Sandy

 

[Ron de Bruin]

I think it is not possible but I not looked very good at it

Protect the workbook and it is not possible to insert new sheets (Review>Protect workbook)

For example Shift F11 will also insert a sheet so you must also disable this shortcut,
protecting the workbook is easier.

 

[Rick Rothstein \(MVP - VB\)]

You could use the Workbook's NewSheet event to delete the newly added sheet
as soon as it is added. Let's give you a way to toggle this 'feature' on and
off. Add a Module to your project (or use an existing Module if you have
one) and put this declaration into it...

Public BlockInsertSheet As Boolean

Now, double click on the ThisWorkbook entry in the Project window and put
the following code in the code window that appeared...

Private Sub Workbook_NewSheet(ByVal Sh As Object)
If Not BlockInsertSheet Then Exit Sub
With Application
.DisplayAlerts = False
.ScreenUpdating = False
End With
Sh.Delete
With Application
.DisplayAlerts = True
.ScreenUpdating = True
End With
End Sub

If you set BlockInsertSheet to True anywhere within your project, the user
will not be able to insert any new sheets. Unfortunately, the "Insert
Worksheet Tab" is still visible, but it won't function (so you might want to
consider putting a MessageBox at the end of the NewSheet event code telling
the user that Sheet inserts are disabled). Neither will right-clicking a tab
and selecting Insert from the popup menu. If you want, we can disable/enable
the popup menu's Insert item to coincide with the disabling or enabling of
inserting sheets. To do that, simply add this subroutine to the Module (do
not change the Module's BlockInsertSheet declaration nor the NewSheet event
code posted above)...

Sub AllowInsertSheets(Optional Status As Boolean = True)
BlockInsertSheet = Not Status
Application.CommandBars("Ply").FindControl(, 945).Enabled = Status
End Sub

But now, you do NOT set the BlockInsertSheet variable directly; instead, you
call the AllowInsertSheets subroutine with either a True (optional) or False
(required) argument to allow or disallow the inserting of sheets (the
subroutine will handle enabling or disabling the popup menu's Insert item).

Rick