Delete All Q

[Seanie]

What code could would I use to do the following when a workbook is
opened:-

1) Delete Contents ALL in Sheet1; Sheet2 and Sheet3 IF Today() > Range
value in AA1 Sheet4+14

2) After (1) above has been actioned a Message Box appears that shows
"File has expired"

Of course IF Today() <= Range value in AA1 Sheet4+14, then do nothing
apart from opening the workbook

 

[Mike H]

Hi,

Maybe this

Private Sub Workbook_Open()
If Date <= Sheets("Sheet4").Range("AA1").Value + 14 Then
Sheets("Sheet1").UsedRange.ClearContents
Sheets("Sheet2").UsedRange.ClearContents
Sheets("Sheet3").UsedRange.ClearContents
End If
End Sub


Mike

 

[meh2030]

What code could would I use to do the following when a workbook is
opened:-

1) Delete Contents ALL in Sheet1; Sheet2 and Sheet3 IF Today() > Range
value in AA1 Sheet4+14

2) After (1) above has been actioned a Message Box appears that shows
"File has expired"

Of course IF Today() <= Range value in AA1 Sheet4+14, then do nothing
apart from opening the workbook

You can use something similar to what is below in the Workbook_Open
event.

Best,

Matt Herbert

Private Sub Workbook_Open()

Dim MyDate As Date
Dim rngDateCompare As Range

MyDate = Date

Set rngDateCompare = Worksheets("Sheet4").Range("aa1")
If rngDateCompare.Value <> "" Then
If MyDate <= rngDateCompare.Value Then
'insert code
MsgBox "Today is less than the cell value."
End If
End If

End Sub

 

[Mike H]

OOPS,

wrong way around, try this

Private Sub Workbook_Open()
If Date > Sheets("Sheet4").Range("AA1").Value + 14 Then
Sheets("Sheet1").UsedRange.ClearContents
Sheets("Sheet2").UsedRange.ClearContents
Sheets("Sheet3").UsedRange.ClearContents
End If
End Sub


Mike

 

[Mike H]

Hmmm,

I forgot your message box

Private Sub Workbook_Open()
If Date > Sheets("Sheet4").Range("AA1").Value + 14 Then
Sheets("Sheet1").UsedRange.ClearContents
Sheets("Sheet2").UsedRange.ClearContents
Sheets("Sheet3").UsedRange.ClearContents
MsgBox "File expired", vbInformation
End If
End Sub

Mike

 

[Seanie]

Hmmm,

I forgot your message box

Private Sub Workbook_Open()
If Date > Sheets("Sheet4").Range("AA1").Value + 14 Then
Sheets("Sheet1").UsedRange.ClearContents
Sheets("Sheet2").UsedRange.ClearContents
Sheets("Sheet3").UsedRange.ClearContents
MsgBox "File expired", vbInformation
End If
End Sub

Mike

One thing on above is that border lines and comments remain after
"ClearContents", how can I clear everything?

 

[Susan]

try using .Clear instead of .ClearContents......... but i'm not sure
it'll remove the comments.

susan