code to prevent users from adding new worksheets

[jat]

i want to prevent users from adding new or blank worksheets on a form, but i
cannot get the code below to work:

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


i have te code in the Microsoft Excel Objects, This Workbook module but i
cannot get it to work. what's missing?

jat

 

[Mike H]

Hi,

Try this. Double click 'ThisWorkbook' and paste this in on the right

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Application.DisplayAlerts = False
MsgBox "No new sheets thank you"
Sh.Delete
Application.DisplayAlerts = True
End Sub


Mike

 

[Alan]

You can do that by password protecting the workbook,
Regards,
Alan.