Application.OnTime

[Biz]

Dear All,

I'm trying change colour every sec in worksheet.

This Workbook module

Private Sub Workbook_Open()
'MsgBox "Bank_format_print " & Now
Call RunEverySec
End Sub

Standard Module

Public nTime As Double
Sub RunEverySec()
nTime = Now + TimeValue("00:00:01")
Application.OnTime nTime, "Colors56"
Calculate
End Sub
 
Sub colors56()
  'Application.Calculation = xlCalculationManual
Dim i As Long

  Cells(1, 1).Interior.ColorIndex = Application.RandBetween(0, 56)
done:
  'Application.Calculation = xlCalculationAutomatic
End Sub

Code fails at Application.OnTime nTime, "Colors56"

Any help would be greatly appreciated.

Biz

 

[VoG]

Try

Public nTime As Date
Sub RunEverySec()
Call colors56
Calculate
End Sub
 
Sub colors56()
  'Application.Calculation = xlCalculationManual
Dim i As Long

  Cells(1, 1).Interior.ColorIndex = Evaluate("RandBetween(0, 56)")
done:
  'Application.Calculation = xlCalculationAutomatic
nTime = Now + TimeValue("00:00:01")
Application.OnTime nTime, "Colors56"

End Sub

 

[Biz]

Hi Vog,

Thanks for Vog code works.

Only problem I have now if I exit saving the macro. It starts the macro automatically again.

What vba code do I use to fix this problem?

Kind Regards,

Biz

 

[VoG]

Try

Sub StopIt()
Application.OnTime nTime, "Colors56", , False
End Sub

 

[Biz]

Hi Vog,

How about using Sub Workbook_BeforeSave?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI = True Then
    Call StopIt
    ActiveWorkbook.Close True ' closes the active workbook and saves any changes
Else: If Cancel = True Then ActiveWorkbook.Close False ' closes the active workbook without saving any changes
End If
End Sub

The above macro is failing.

Biz

 

[VoG]

In what way failing?

 

[Biz]

Hi Vog,

Macro is failing because when exiting the file:
* When "Yes" clicked the macro not only saves file but restarts the macro again. It should save the file and then exit file only.
* when "No" clicked the macro quits the files and restarts it again. It should not save the file and then exit file only.

Is there a way to fix this problem?

Biz