How to disable “Insert worksheet “ form tab ? except “input”,”output”, and “temp”.

Dear all , i have three worksheets “input”,”output”, and “temp”. here sheet “temp” is not visible . it is being created for processing some activity and after that it deleted during run macro. I want to implement below macro code sothat no one add new sheet except “temp “sheet. below code is working fine if “temp” sheet is not created by macro . how to implement some logic sothat it can ignore the “temp” sheet .

'code by RoyUK
Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Application.DisplayAlerts = False
    ActiveSheet.Delete
    MsgBox "No new sheets allowed in this workbook", vbCritical, "User warning"
    Application.DisplayAlerts = True
End Sub

Hi,

Could you disable events in your code and then re-enable them when complete?

Sub YourSub()
Application.EnableEvents = False

'code from your temp sheet macro

Application.EnableEvents = True

Hi Nur,

your problem is ActiveSheet.Delete should be sh.Delete

In workbook change event sh as object is refer to Activesheet

the same thing goes to ActiveCell.Row is refer to Target.Row. Just FYI.


you may try this,

Private Sub Workbook_NewSheet(ByVal Sh As Object)


        Application.DisplayAlerts = False
        Sh.Delete
        Application.DisplayAlerts = True

    MsgBox "No new sheets allowed in this workbook", vbCritical, "User warning"
End Sub