PC Review
Home Forums Reviews Articles Register
Member Login:

PC Review > Forums > Newsgroups > Microsoft Excel > Microsoft Excel Programming > Delete All Q

Reply
Thread Tools Rate Thread

Delete All Q

 
 
Seanie
Guest
Posts: n/a
 
      21st Mar 2009
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
 
Reply With Quote
 
 
 
 
Mike H
Guest
Posts: n/a
 
      21st Mar 2009
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
>
 
Reply With Quote
 
meh2030@gmail.com
Guest
Posts: n/a
 
      21st Mar 2009
On Mar 21, 8:34*am, Seanie <seanrya...@yahoo.co.uk> 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
 
Reply With Quote
 
Mike H
Guest
Posts: n/a
 
      21st Mar 2009
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
> >
 
Reply With Quote
 
Mike H
Guest
Posts: n/a
 
      21st Mar 2009
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
> > >
 
Reply With Quote
 
Seanie
Guest
Posts: n/a
 
      25th Mar 2009
On Mar 21, 2:25*pm, Mike H <Mi...@discussions.microsoft.com> 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 With Quote
 
Susan
Guest
Posts: n/a
 
      25th Mar 2009
try using .Clear instead of .ClearContents......... but i'm not sure
it'll remove the comments.

susan


On Mar 25, 4:40*am, Seanie <seanrya...@yahoo.co.uk> wrote:
> On Mar 21, 2:25*pm, Mike H <Mi...@discussions.microsoft.com> 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 With Quote
 
 
 
Reply

« Filtering For Data Using Macros | Office 2007 Shortcut Menus »
Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Delete data in a linked Excel sheet using Access code or seql delete Rocky Microsoft Access External Data 9 26th Jun 2005 12:42 AM
Re: Macro to delete sheets and saves remaining file does not properly delete module gazornenplat Microsoft Excel Programming 0 22nd Jun 2005 01:12 AM
Macro to delete sheets and saves remaining file does not properly delete module pherrero Microsoft Excel Programming 7 21st Jun 2005 05:16 PM
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 Annette Microsoft Excel Programming 2 21st Sep 2004 02:40 PM
Re: When I highlight a sentence to delete, Word won't let me. I have to backspace. How can I deleted selected text with my delete key? Bill Foley Microsoft Word Document Management 1 4th Feb 2004 11:06 PM


Features
Forums Startup Database Reviews Articles FAQ / Guidelines About Us
 

Advertising
 

Newsgroups
Windows Vista Windows XP Microsoft Word Microsoft DotNet Windows 2000 AntiSpyware Microsoft Access Microsoft Excel Microsoft Outlook Hardware
 


All times are GMT +1. The time now is 06:54 PM.
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc.
Contact Us - Archive - Privacy Statement - Top