Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Delete worksheet quietly? ie without prompt (WinNT/XL97)

    Is there a way to delete a spreadsheet in VBA without getting a prompt? worksheets(1).delete gets the "this worksheet will be permanently deleted, are you sure this is what you want?" prompt, and I don't want that, but there don't seem to be any switches with that command. Should I be using a different one?

    Thanks in advance!
    Beryl M


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete worksheet quietly? ie without prompt (WinNT/XL97)

    You can use code like this:

    Application.DisplayAlerts = False
    ActiveWorkbook.Worksheets("Sheet2").Delete
    Application.DisplayAlerts = True

    Setting DisplayAlerts to False will cause Excel to choose the default response in dialogs without prompting the user.

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Delete worksheet quietly? ie without prompt (WinNT/XL97)

    Now that is one of those supremely useful bits of info/code that one needs to keep forevermore!!

    Many thanks, Hans!!
    Beryl M


  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Delete worksheet quietly? ie without prompt (WinNT/XL97)

    It works in Word VBA and in PowerPoint VBA too!

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Delete worksheet quietly? ie without prompt (WinNT/XL97)

    Be sure to set it back to true though, it doesn't get reset automatically and will cause Excel to stop showing the messages also through the user interface.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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