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


"Sandy" wrote in message
...
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" wrote in message
...
See this page Sandy
http://www.rondebruin.nl/ribbon.htm

See the xml examples (2)

.DisplayFormulaBar = False
This is the same in 2007


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Sandy" wrote in message
...
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