CODEWITHSUNDEEP
excelexcel-2007vba
Tim
Tim

Reputation: 111

Disable Insert Worksheet

Is there a way to prevent people from inserting worksheets manually. I have a form that creates the worksheets automatically based on the information the user puts in it. The code I currently uses will prevent people from creating worksheets but it also prevents my form from creating worksheets. Here is what I am using. It is in the workbook module.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Application.DisplayAlerts = False
    Sh.Delete
    Application.DisplayAlerts = True
End Sub

Upvotes: 4

Views: 792

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149295

Declare this in a Module

Public BoolAdd As Boolean

This in your Workbook_NewSheet

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    If BoolAdd = False Then
        Application.DisplayAlerts = False
        Sh.Delete
        Application.DisplayAlerts = True
    Else
        BoolAdd = False
    End If
End Sub

and set the BoolAdd to TRUE in your userform before you add the sheet.

Private Sub CommandButton1_Click()
    BoolAdd = True
    Sheets.Add
End Sub

Logic: The userform will set the public variable to True. This will ensure that sheet deletion code will not run. Also we have to set it back to false else the user will be able to add the sheet after the userform is closed.

Upvotes: 3

Related Questions