i have a protected worksheet (has formulas and formatted) to be distributed to 200 other users. after filling the data all the worksheets come back to me. i will merge all the worksheets and work with them. so i don't want others to insert a new worksheet copy formulas, formatting and messing it in the distributed worksheet. either disabling from inserting new worksheet for copying the contents of the protected worksheet in to new worksheet or encrypt this sheet not to copy in another workseet/workbook.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
barring of inserting new worksheet
- Thread starter teayesbee
- Start date
Tom Urtis
MrExcel MVP
- Joined
- Feb 10, 2002
- Messages
- 11,300
Here is how to accomplish what you say you want.
Find the little Excel workbook icon near the upper left corner of your workbook window, usually just to the immediate left of the File menu option. Right click on that icon, left click on View Code, and paste the following procedure into the large white area that is the workbook module:
Next, while you are there in the Visual Basic Editor, from the menu bar at the top, click Insert > Module and paste this into that new module:
Press Alt+Q to return to the worksheet.
Now when you attempt to insert a new worksheet, you'll get a message saying it cannot be done for that workook.
Find the little Excel workbook icon near the upper left corner of your workbook window, usually just to the immediate left of the File menu option. Right click on that icon, left click on View Code, and paste the following procedure into the large white area that is the workbook module:
Code:
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Run "NoNewSheet"
End SubNext, while you are there in the Visual Basic Editor, from the menu bar at the top, click Insert > Module and paste this into that new module:
Code:
Private Sub NoNewSheet()
With Application
.ScreenUpdating = False
.DisplayAlerts = False
ActiveSheet.Delete
.DisplayAlerts = True
.ScreenUpdating = True
End With
MsgBox "No new worksheets are allowed to be added.", 48, "FYI..."
End SubPress Alt+Q to return to the worksheet.
Now when you attempt to insert a new worksheet, you'll get a message saying it cannot be done for that workook.
Last edited:
Upvote
0
As you said the two codes given by you were copied and pasted in respective places in view code as well as macro insert. but was unsuccessful. when i tried to insert a new worksheet, a new sheet was inserted. where i have done wrong??
Upvote
0
Similar threads
- Question
