Re calculate
 

Hiya

I have a spreadsheet which is used a lot and often left
open over night. Several cells include =Today() to
generate various dates.
The problem is that unless the sheet is closed and re-
opened each day, the day doesn't change.

Is there any way I can get the sheet to re-calculate every
time the workbook is activated.
I've tried the following but without success.
Sub Workbook_Activate
sheet1.calculate
end sub

thanks

Re calculate
 

Hi,
In the workbook that you want to recalculate, place the
following code in the object module of ThisWorkbook

Option Explicit

Dim MyxlApp As New clsMyxlAppEvents

Private Sub Workbook_Open()
Set MyxlApp.xlApp = Application
End Sub

The create a class module call clsMyxlAppEvents and place
the following code in the new module,

Private Sub xlApp_WorkbookActivate(ByVal Wb As
Excel.Workbook)

if wb.name = "My Workbook.xls" then
application.activeworkbook.sheets("sheet1").calcul ate


end sub

You can add additional code so that it only runs once per
day e.g add one date variable that records when the last
calculate was done.

Hope this helps
regards
KM




-----Original Message-----
Hiya

I have a spreadsheet which is used a lot and often left
open over night. Several cells include =Today() to
generate various dates.
The problem is that unless the sheet is closed and re-
opened each day, the day doesn't change.

Is there any way I can get the sheet to re-calculate
every
time the workbook is activated.
I've tried the following but without success.
Sub Workbook_Activate
sheet1.calculate
end sub

thanks
.