| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
| Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
| Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
| Macro to delete sheets and saves remaining file does not properly delete module | Excel Programming | |||
| Delete every 3rd row, then delete rows 2-7, move info f/every 2nd row up one to the end and delete the row below | Excel Programming | |||
Hybrid Mode
