Results 1 to 5 of 5

Delete Active Worksheet

This is a discussion on Delete Active Worksheet within the Excel Questions forums, part of the Question Forums category; I am trying to delete an activesheet based on the current date and have not been able to find any ...

  1. #1
    Board Regular
    Join Date
    Jan 2004
    Location
    Melbourne
    Posts
    3,459

    Default Delete Active Worksheet

    I am trying to delete an activesheet based on the current date and have not been able to find any help in VBA help on this.

    The background is that I have a number of sheets and when I reach the next month, I would like the oldest sheet deleted and a new sheet created. I will work on the sheet names and identity of the date and sheet myself, but my VBA code to delete the sheet will be something like :

    Sub TimeBomb()
    TimeValue1 = Now()
    If TimeValue1 < DateValue("January 1, 2004") Then
    Exit Sub
    Else
    ActiveWindow.SelectedSheets.Delete
    End If
    End Sub

    My problem is that when I run this in January, the message appears: "Selected sheets will be permanently deleted" and I have to click on "Yes".

    Is there a way to avoid this message, or have the macro choose "Yes" for me?

  2. #2
    Board Regular SIXTH SENSE's Avatar
    Join Date
    Oct 2003
    Posts
    1,884

    Default

    hi!

    before deleting insert this code
    application.displayalerts=false
    There is always a better way!!

  3. #3
    Board Regular Chitosunday's Avatar
    Join Date
    Jul 2003
    Posts
    1,017

    Default

    Application.DisplayAlerts = False
    ActiveWindow.SelectedSheets.Delete
    Application.DisplayAlerts = True
    Mr Young at Heart - Learning is like chasing the wind, it's boundless.

  4. #4
    MrExcel MVP HalfAce's Avatar
    Join Date
    Apr 2003
    Location
    Alaska
    Posts
    9,236

    Default Re: Delete Active Worksheet

    Hi tactps,
    You can avoid the dialog box by using this:
    Application.DisplayAlerts = False
    right above your line "ActiveWindow.SelectedSheets.Delete".
    (Just remember to use Application.DisplayAlerts = True
    somewhere before the end of your routine.)

    This help?
    Dan

    Edit:
    Guess you got your answer...(Those weren't there when I started.
    Man you guys are quick!)
    My greatest fear is that when I die my wife will sell my guns and my hot rod for what I told her they cost ...


  5. #5
    Board Regular
    Join Date
    Jan 2004
    Location
    Melbourne
    Posts
    3,459

    Default Re: Delete Active Worksheet

    That'll do it! The help on display alerts just confused me.

    Thank you all!


Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com