expire, clear content, save

ranthrave

Board Regular
Joined
Aug 26, 2010
Messages
141
Hello sir/maam!

I use this code to expire a workbook and close.
Can i ask to you if it's possible that after workbook expire it clear or delete all the contents in worksheets (all/specific worksheet) then eventually autosave then close.

Any insights or suggestion is much appreciated.

Code:
 Private Sub Workbook_Open()
    Dim Edate As Date
    Edate = Format("20/11/2011", "DD/MM/YYYY") ' Replace this with the date you want
    If Date > Edate Then
        MsgBox ("This worksheet was valid upto " & Format(Edate, "dd-mmm-yyyy") & " and will be closed")
        ActiveWorkbook.Close
    End If
    If Edate - Date < 30 Then
    MsgBox ("This worksheet expires on " & Format(Edate, "dd-mmm-yyyy") & " You have " & Edate - Date & " Days left ")
    End If
End Sub
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

John Davis

Well-known Member
Joined
Sep 11, 2007
Messages
3,457
Hello sir/maam!

I use this code to expire a workbook and close.
Can i ask to you if it's possible that after workbook expire it clear or delete all the contents in worksheets (all/specific worksheet) then eventually autosave then close.

Any insights or suggestion is much appreciated.

Code:
 Private Sub Workbook_Open()
    Dim Edate As Date
    Edate = Format("20/11/2011", "DD/MM/YYYY") ' Replace this with the date you want
    If Date > Edate Then
        MsgBox ("This worksheet was valid upto " & Format(Edate, "dd-mmm-yyyy") & " and will be closed")
        ActiveWorkbook.Close
    End If
    If Edate - Date < 30 Then
    MsgBox ("This worksheet expires on " & Format(Edate, "dd-mmm-yyyy") & " You have " & Edate - Date & " Days left ")
    End If
End Sub

Would this do what you want?

Code:
 Private Sub Workbook_Open()
    Dim Edate As Date
    Edate = Format("20/11/2011", "DD/MM/YYYY") ' Replace this with the date you want
    If Date > Edate Then
        MsgBox ("This worksheet was valid upto " & Format(Edate, "dd-mmm-yyyy") & " and will be closed")
        Call ranthrave
        ActiveWorkbook.Close
    End If
    If Edate - Date < 30 Then
    MsgBox ("This worksheet expires on " & Format(Edate, "dd-mmm-yyyy") & " You have " & Edate - Date & " Days left ")
    End If
End Sub

In standard module

Code:
Sub ranthrave()
Dim ws As Worksheet

For Each ws In Thisworkbook.Worksheets

    ws.Cells.Clear
    
Next ws
    
End Sub
 

ranthrave

Board Regular
Joined
Aug 26, 2010
Messages
141
Thank you sir for reply.
But this is not I'm looking for
Code:
 Sub ranthrave()
Dim ws As Worksheet

For Each ws In Thisworkbook.Worksheets

    ws.Cells.Clear
    
Next ws
    
End Sub
 

Jaye7

Well-known Member
Joined
Jul 7, 2010
Messages
1,060
You could specify each sheet that you want to clear if there are multiple sheets that you do not want to clear.

Code:
Sub Macro3()
    Sheets(Array("Sheet1", "Sheet3")).Select
    Sheets("Sheet1").Activate
    Cells.Select
    Selection.ClearContents
    Range("A1").Select
End Sub
 

ranthrave

Board Regular
Joined
Aug 26, 2010
Messages
141

ADVERTISEMENT

You could specify each sheet that you want to clear if there are multiple sheets that you do not want to clear.

Code:
Sub Macro3()
    Sheets(Array("Sheet1", "Sheet3")).Select
    Sheets("Sheet1").Activate
    Cells.Select
    Selection.ClearContents
    Range("A1").Select
End Sub

Thanks for reply sir

Let me again sir specify
Code:
 Private Sub Workbook_Open()
    Dim Edate As Date
    Edate = Format("20/10/2011", "DD/MM/YYYY") ' Replace this with the date you want
    If Date > Edate Then
        MsgBox ("This worksheet was valid upto " & Format(Edate, "dd-mmm-yyyy") & " and will be closed")
        ActiveWorkbook.Close
    End If
    If Edate - Date < 30 Then
    MsgBox ("This worksheet expires on " & Format(Edate, "dd-mmm-yyyy") & " You have " & Edate - Date & " Days left ")
    End If
End Sub
Assuming the macro was enable.
After a period of time it will expired and close. And then I ask if it is possible or there's a code that will clear contents or delete sheets, then eventually autosave before it close?

something like expired --> (clear contents/ delete sheets --> autosave) --> close
 

John Davis

Well-known Member
Joined
Sep 11, 2007
Messages
3,457
Thanks for reply sir

Let me again sir specify
Code:
 Private Sub Workbook_Open()
    Dim Edate As Date
    Edate = Format("20/10/2011", "DD/MM/YYYY") ' Replace this with the date you want
    If Date > Edate Then
        MsgBox ("This worksheet was valid upto " & Format(Edate, "dd-mmm-yyyy") & " and will be closed")
        ActiveWorkbook.Close
    End If
    If Edate - Date < 30 Then
    MsgBox ("This worksheet expires on " & Format(Edate, "dd-mmm-yyyy") & " You have " & Edate - Date & " Days left ")
    End If
End Sub
Assuming the macro was enable.
After a period of time it will expired and close. And then I ask if it is possible or there's a code that will clear contents or delete sheets, then eventually autosave before it close?

something like expired --> (clear contents/ delete sheets --> autosave) --> close

Still not sure I understand but, let's try again.

Code:
Private Sub Workbook_Open()
    Dim Edate As Date
    Dim ws As Worksheet

    Edate = Format("20/11/2011", "DD/MM/YYYY") ' Replace this with the date you want
    If Date > Edate Then
        MsgBox ("This worksheet was valid upto " & Format(Edate, "dd-mmm-yyyy") & " and will be closed")

            For Each ws In ThisWorkbook.Worksheets

                ws.Cells.Clear
    
            Next ws
            
        ActiveWorkbook.Close SaveChanges:=True
    End If
    If Edate - Date < 30 Then
    MsgBox ("This worksheet expires on " & Format(Edate, "dd-mmm-yyyy") & " You have " & Edate - Date & " Days left ")
    End If

End Sub
 

ranthrave

Board Regular
Joined
Aug 26, 2010
Messages
141
Thank You Very Much Sir John Davis

Buy the way sir, cells in some sheet can't clear it contents (because it was password protected).
Can I possibly targeted a specific cell range so that it will ignore protected cells?

Code:
 Private Sub Workbook_Open()
    Dim Edate As Date
    Dim ws As Worksheet

    Edate = Format("20/11/2011", "DD/MM/YYYY") ' Replace this with the date you want
    If Date > Edate Then
        MsgBox ("This worksheet was valid upto " & Format(Edate, "dd-mmm-yyyy") & " and will be closed")

            For Each ws In ThisWorkbook.Worksheets

                ws.Cells.Clear
    
            Next ws
            
        ActiveWorkbook.Close SaveChanges:=True
    End If
    If Edate - Date < 30 Then
    MsgBox ("This worksheet expires on " & Format(Edate, "dd-mmm-yyyy") & " You have " & Edate - Date & " Days left ")
    End If

End Sub

Again thank you very much! :)
 

John Davis

Well-known Member
Joined
Sep 11, 2007
Messages
3,457
Thank You Very Much Sir John Davis

Buy the way sir, cells in some sheet can't clear it contents (because it was password protected).
Can I possibly targeted a specific cell range so that it will ignore protected cells?

Code:
 Private Sub Workbook_Open()
    Dim Edate As Date
    Dim ws As Worksheet

    Edate = Format("20/11/2011", "DD/MM/YYYY") ' Replace this with the date you want
    If Date > Edate Then
        MsgBox ("This worksheet was valid upto " & Format(Edate, "dd-mmm-yyyy") & " and will be closed")

            For Each ws In ThisWorkbook.Worksheets

                ws.Cells.Clear
    
            Next ws
            
        ActiveWorkbook.Close SaveChanges:=True
    End If
    If Edate - Date < 30 Then
    MsgBox ("This worksheet expires on " & Format(Edate, "dd-mmm-yyyy") & " You have " & Edate - Date & " Days left ")
    End If

End Sub

Again thank you very much! :)

You're welcome. It's possible what is the specific cell range.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,303
Messages
5,641,425
Members
417,209
Latest member
Agbarker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top