Excel VBA Tips: Stop Users from Adding Sheets

Do you have a spreadsheet that you want to stop users from adding sheets to? Of course, you could lock the workbook using the “protect workbook” feature and ensuring that the protect structure is selected.

However, say that you need to protect the workbook via vba. Place the code into “ThisWorkbook” module to allow you to stop users from adding worksheets. I found this code here.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
    Sh.Delete
    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
     MsgBox "Adding Sheets isn't allowed"
End Sub

Hope this is useful!

Come check out the directory for the rest of my excel tips!

Submeg

IT

Posted by:submeg

Musician, Writer and Inspirer. I discuss the projects I am working on, the services I provide and my general thoughts.

Tell me what you think!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s