code to prevent users from adding new worksheets
 

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

code to prevent users from adding new worksheets
 

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

"jat" wrote:

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

code to prevent users from adding new worksheets
 

You can do that by password protecting the workbook,
Regards,
Alan.
"jat" wrote in message
...
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