Macro to prevent adding sheet in a workbook.

[hamad.fatima@gmail.com]

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

thanks

[Rob Bovey]

<hamad.fatima@gmail.com> wrote in message
news:1155567014.592164.313970@h48g2000cwc.googlegroups.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

[Jim Thomlinson]

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


"hamad.fatima@gmail.com" wrote:

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

[Tom Ogilvy]

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" <James_Thomlinson@owfg-Re-Move-This-.com> wrote in message
news:16E537E1-5A95-4F04-9796-F3C3433A3430@microsoft.com...
> 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
>
>
> "hamad.fatima@gmail.com" wrote:
>
>> I need some help with writing a macro which can prevent th euser to add
>> additional sheets in a workbook.
>>
>> thanks
>>
>>

[Rob Bovey]

"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:u50TnwAwGHA.3936@TK2MSFTNGP04.phx.gbl...
> 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

[Tom Ogilvy]

I was on your side.

--
Regards,
Tom Ogilvy

"Rob Bovey" <Rob_Bovey@msn.com> wrote in message
news:u9RaZ1AwGHA.4160@TK2MSFTNGP06.phx.gbl...
> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
> news:u50TnwAwGHA.3936@TK2MSFTNGP04.phx.gbl...
>> 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
>
>

[Rob Bovey]

"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:OyI3cNBwGHA.4416@TK2MSFTNGP03.phx.gbl...
>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