macro that works one time every day

[Spiros]

Hi to everyone,
Is there any macro which delete the data in specific cells one time every
day when I opened the excel worksheet.
For example: when I open one worksheet for the first time in 20-9-2008, the
macro will delete the data from cells a5, a6, b7 and b9. If I open the same
worksheet another time in the same day, the macro mustn’t be worked.
Thanks in advance.
Spiros

 

[Ron de Bruin]

Hi

Try this event in the Thisworkbook module
I use cell G1 to add a date, you can change that location

See this page if you are new to macros
http://www.rondebruin.nl/code.htm

Private Sub Workbook_Open()
With Sheets("Sheet1")
If .Range("G1").Value = Date Then
'do nothing
Else
.Range("G1").Value = Date
.Range("A5,A6,B7,B9").ClearContents
End If
End With
End Sub

 

[Mike H]

Hi,

If your file is saved then you could write the date to a cell but if it
isn't then that method won't work. This uses a text file to record whether
the macro has been run

Private Sub Workbook_Open()
filenum = FreeFile
fname = "C:\timesrun.txt"
If Dir(fname) <> "" Then
Open fname For Input As filenum
Input #filenum, mydate
Close #filenum
End If
Open "C:\timesrun.txt" For Output As filenum
Print #filenum, Format(Date, "ddmmyyyy")
Close #filenum
todaysdate = Format(Date, "ddmmyyyy")
If CStr(mydate) = todaysdate Then
MsgBox "This macro has been run today"
Exit Sub
Else
Sheets("Sheet1").range("A5,A6,B7,B9").ClearContents
End If
End Sub

Mike