Disable adding sheet, except from macro

[ajaykgarg]

Hi

I want to dis-allow users to add sheets to my file manually, so I wrote following code:-

Please Login or Register  to view this content.

But, this disables adding sheet by my macro code as well.

Is there a way to disable adding sheet manually from menu options, but allow macro to add sheets?

tia
Ajay

[JBeaucaire]

In your OTHER macros, disable the triggering of "events" while your macro is running, then turn events back on.

Please Login or Register  to view this content.

[Marcol]

Try this

Please Login or Register  to view this content.

Hope this helps

[EDIT]
Never saw your post Jerry, sorry about that.

[royUK]

Why not just protect the workbook?

Please Login or Register  to view this content.

Using inbuilt protection will work even if macros aren't enabled

[ajaykgarg]

Jerry and Marcol - That works. Thanks.

Roy - I don't want to protect the workbook, as I want to let users add columns, etc, but not sheets.

Closing the thread. Thanks.

Ajay

[nimrod]

Hello ajaykgarg:
Are you aware that excel 2007 has specfic feature to protect/prevent "structural changes" . Structural changes include the addition, deletion, movement, hiding or unhiding of worksheets within a workbook. Your solution is easily defeated by not activating macros in the first place ... while this solution requires no activation of macros and needs a password.

To protect a workbook from these kinds of changes, click Review > Changes > Protect Workbook > Protect Structure and Windows. In the Protect Structure and Windows dialog box ensure that Structure is checked and assign a password if required. Similar steps need to be followed when unprotecting the workbook’s structure: click Review > Changes > Protect Workbook > Protect Structure and Windows. You will need to enter a password if a password was previously assigned.

http://www.msexcel07.com/protecting-workbooks.htm


ADDING NEW SHEET

Please Login or Register  to view this content.