old chestnut (resolved)

[sykes]

Hi again team
I've looked hard with the search, but can't find what I want, however I know it's out there because I've seen it crop up.

I want a macro to run when the present date equals a date in a cell, even if the workbook's closed.
For example, cell date is 28 07 02, and when the actual date is the same, an extra row is added to the top of a sheet of data, and the top line of data changed (obviously I've already written the macro, which works well etc.)
Trouble is this will problably be at midnight local, when the computer is turned off.

Ta

Sykes

_________________
....and the meek shall inherit the earth...
(but not the mineral rights!)
This message was edited by sykes on 2002-07-27 00:33

 

[Al Chara]

I doubt you are going to be able to find a macro that runs when the computer is off.

 

[KniteMare]

If it does not HAVE to happen while the workbook is closed (i.e. You are not accessing this workbooks information from another workbook while this one is closed) then to the user it only needs to APPEAR as if it happened while closed.
This will cause the range called CellDate to triger a row insert at the top of the pageif the date is equal to today's date.


Private Sub Workbook_Open()
If Range("CellDate").Value = Date Then
Rows(1).Insert

MsgBox ("DONE")
End If
End Sub



If you need many rows added, if say, the user opened the file today but SHOULD have opened it yesterday and therefore should have an additional row at the top, WELL that is a different story which I suppose could also have a happy ending. Sadly I do not have the time right now to write that conditional code.

Yours in EXCELent Frustration
KniteMare

 

[hedrijw]

Very nice Knitemare. I took a slightly different approach, i.e, when the file was opened add one row for each day that had elapsed shice it was closed. Day of last closure is in range named CellDate.

Private Sub Workbook_Open()

If Range("CellDate").Value < Date Then
to_add = Date - [CellDate]
Rows(to_add).Insert
End If
MsgBox ("Added " & Str(to_add) & " row(s)"

End Sub

 

[sykes]

Well done team, as usual.
Your solutions have given me enough ideas to complete my worksheet.

Many thanks

Sykes