Excel

Prevent Sheets Being Added

Ease of Use

Easy

Version tested with

2000, 2002 

Submitted by:

Justinlabenne

Description:

Prevent a user from adding new sheets to your workbook 

Discussion:

This code attempts to prevent a user from adding new sheets to your Excel workbook. The code is stored in the "ThisWorkbook" code module, and uses VBA's native workbook level event {NewSheet} to detect if a new sheet has been added. The new sheet is quickly removed before it can barely be seen, and the user is warned. Disabling macros will override the codes functionality, so your workbook will need to be designed with that in mind. 

Code:

instructions for use

			

Option Explicit Private Sub Workbook_NewSheet(ByVal Sh As Object) With Application .ScreenUpdating = False .DisplayAlerts = False ActiveSheet.Delete MsgBox "Cannot add new sheets!", 16 .DisplayAlerts = True .ScreenUpdating = True End With End Sub

How to use:

  1. Open an Excel Workbook
  2. Copy the code
  3. Right Click on the small Excel Icon in the top left corner > View Code
  4. Paste code into the right pane
  5. Press Alt+Q to return to Excel
  6. Save workbook before any other changes
 

Test the code:

  1. With macros enabled, try to add a new sheet to the workbook
  2. The code will prevent you from doing so and warn you.
 

Sample File:

Prevent Sheet Insert.zip 6.78KB 

Approved by mdmackillop


This entry has been viewed 188 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express