Delete All Q
 

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

Delete All Q
 

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

"Seanie" wrote:

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

Delete All Q
 

On Mar 21, 8:34*am, Seanie wrote:
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

Delete All Q
 

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" wrote:

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

"Seanie" wrote:

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

Delete All Q
 

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

"Mike H" wrote:

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" wrote:

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

"Seanie" wrote:

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

Delete All Q
 

On Mar 21, 2:25*pm, Mike H wrote:
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?

Delete All Q
 

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


On Mar 25, 4:40*am, Seanie wrote:
On Mar 21, 2:25*pm, Mike H wrote:

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?