+ Reply to Thread
Results 1 to 4 of 4

Code to Prevent Adding Worksheets

  1. #1
    Paige
    Guest

    Code to Prevent Adding Worksheets

    I am trying to prevent a user from adding worksheets to a particular
    workbook. The code I am using (see below) doesn't work; I don't get any
    error messages. Any suggesions on what I am doing wrong?

    Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Application.DisplayAlerts = False
    MsgBox "New sheets may not be added. " & _
    "Sheet will be deleted"
    Sh.Delete
    Application.DisplayAlerts = True
    End Sub

    Thanks...Paige

  2. #2
    Ron de Bruin
    Guest

    Re: Code to Prevent Adding Worksheets

    Hi Paige

    Why don't you protect the workbook


    --
    Regards Ron de Bruin
    http://www.rondebruin.nl



    "Paige" <[email protected]> wrote in message news:[email protected]...
    >I am trying to prevent a user from adding worksheets to a particular
    > workbook. The code I am using (see below) doesn't work; I don't get any
    > error messages. Any suggesions on what I am doing wrong?
    >
    > Private Sub Workbook_NewSheet(ByVal Sh As Object)
    > Application.DisplayAlerts = False
    > MsgBox "New sheets may not be added. " & _
    > "Sheet will be deleted"
    > Sh.Delete
    > Application.DisplayAlerts = True
    > End Sub
    >
    > Thanks...Paige




  3. #3
    Rob Bovey
    Guest

    Re: Code to Prevent Adding Worksheets

    "Paige" <[email protected]> wrote in message
    news:[email protected]...
    >I am trying to prevent a user from adding worksheets to a particular
    > workbook. The code I am using (see below) doesn't work; I don't get any
    > error messages. Any suggesions on what I am doing wrong?
    >
    > Private Sub Workbook_NewSheet(ByVal Sh As Object)
    > Application.DisplayAlerts = False
    > MsgBox "New sheets may not be added. " & _
    > "Sheet will be deleted"
    > Sh.Delete
    > Application.DisplayAlerts = True
    > End Sub


    Hi Paige,

    Your code seems to work OK for me here. But wouldn't it be easier to
    just protect the workbook? That would prevent the user from adding sheets
    without any code at all.

    --
    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



  4. #4
    Myrna Larson
    Guest

    Re: Code to Prevent Adding Worksheets

    Hmmm.... your code works fine for me. What do you mean when you say it doesn't
    work? Do not see the message box? If so, are you sure that events are enabled?
    You could add the line

    Application.EnableEvents = True

    to the Workbook_Open event code.

    But have you considered protecting the workbook's structure? That disallows
    adding sheets (and possibly some other things). Check it out in Help.

    On Mon, 21 Feb 2005 13:01:04 -0800, "Paige" <[email protected]>
    wrote:

    >I am trying to prevent a user from adding worksheets to a particular
    >workbook. The code I am using (see below) doesn't work; I don't get any
    >error messages. Any suggesions on what I am doing wrong?
    >
    >Private Sub Workbook_NewSheet(ByVal Sh As Object)
    > Application.DisplayAlerts = False
    > MsgBox "New sheets may not be added. " & _
    > "Sheet will be deleted"
    > Sh.Delete
    > Application.DisplayAlerts = True
    >End Sub
    >
    >Thanks...Paige



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1