Macro to prevent adding sheet in a workbook.
 

I need some help with writing a macro which can prevent th euser to add
additional sheets in a workbook.

thanks

Macro to prevent adding sheet in a workbook.
 

wrote in message
oups.com...
I need some help with writing a macro which can prevent th euser to add
additional sheets in a workbook.

Why not just protect the workbook. Tools/Protection/Protect Workbook
from the Excel menu. This will prevent any new sheets from being added.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

Macro to prevent adding sheet in a workbook.
 

Generally I agree with Rob that the best solution is probably to protect the
workbook. If however you want to proceed this code will prevent new sheets
from being added. It does not however stop someone from copying sheets into
the book.

This code goes in the ThisWorkbook module. Right clickt the Excel icon next
to File on the menu and select view code. Add this code...

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Application.DisplayAlerts = False
MsgBox "Sorry. No adding new sheets."
Sh.Delete
Application.DisplayAlerts = True
End Sub
--
HTH...

Jim Thomlinson


" wrote:

I need some help with writing a macro which can prevent th euser to add
additional sheets in a workbook.

thanks

Macro to prevent adding sheet in a workbook.
 

Just some added thoughts.


Rob's method could of course could be easily defeated through overt action
by the user to break the protection.

Jim's method (like any software based protection) could be inadvertently
defeated by setting security to high and the user would never know you cared
whether worksheets were added or not.

--
Regards,
Tom Ogilvy

"Jim Thomlinson" wrote in message
...
Generally I agree with Rob that the best solution is probably to protect
the
workbook. If however you want to proceed this code will prevent new sheets
from being added. It does not however stop someone from copying sheets
into
the book.

This code goes in the ThisWorkbook module. Right clickt the Excel icon
next
to File on the menu and select view code. Add this code...

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Application.DisplayAlerts = False
MsgBox "Sorry. No adding new sheets."
Sh.Delete
Application.DisplayAlerts = True
End Sub
--
HTH...

Jim Thomlinson


" wrote:

I need some help with writing a macro which can prevent th euser to add
additional sheets in a workbook.

thanks

Macro to prevent adding sheet in a workbook.
 

"Tom Ogilvy" wrote in message
...
Just some added thoughts.

Rob's method could of course could be easily defeated through overt action
by the user to break the protection.

Jim's method (like any software based protection) could be inadvertently
defeated by setting security to high and the user would never know you
cared whether worksheets were added or not.

Yes, of course. Clearly, applying a strong file-open password to the
workbook and then not supplying the user with that password would be a more
robust method of preventing new worksheets from being added. ;-)

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

Macro to prevent adding sheet in a workbook.
 

I was on your side.

--
Regards,
Tom Ogilvy

"Rob Bovey" wrote in message
...
"Tom Ogilvy" wrote in message
...
Just some added thoughts.

Rob's method could of course could be easily defeated through overt
action by the user to break the protection.

Jim's method (like any software based protection) could be inadvertently
defeated by setting security to high and the user would never know you
cared whether worksheets were added or not.

Yes, of course. Clearly, applying a strong file-open password to the
workbook and then not supplying the user with that password would be a
more robust method of preventing new worksheets from being added. ;-)

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

Macro to prevent adding sheet in a workbook.
 

"Tom Ogilvy" wrote in message
...
I was on your side.

No worries Tom, I was just pulling your chain. <g We both know that
between determined fools and malicious experience very little in Excel can
ever be made bulletproof.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm