|
|
|
|
|
|
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:
|
- Open an Excel Workbook
- Copy the code
- Right Click on the small Excel Icon in the top left corner > View Code
- Paste code into the right pane
- Press Alt+Q to return to Excel
- Save workbook before any other changes
|
Test the code:
|
- With macros enabled, try to add a new sheet to the workbook
- 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.
|
|