Running Macros

Here's a way to do that

Mark,

To calculate, try pasting this in the worksheet's module (right click on the sheet tab, and left click on "View Code"):

Private Sub Worksheet_Activate()
Call Recalculate
End Sub

and then paste this code in a new module in the VBE (that is, press Alt+F11, click Insert > Module):

Public Sub Recalculate()
Calculate
Application.OnTime earliesttime:=Now + TimeValue("00:00:10"), _
procedure:="Recalculate"
End Sub

Return to the worksheet by pressing Alt+Q, and you should be good to go. It will update times as displayed in cells on your worksheet every ten seconds.

If it's just a macro to be repeated without calculations, try a variation like:

Private Sub Worksheet_Activate()
Call MacroMania
End Sub

and

Public Sub MacroMania()
MsgBox "Hello again.", 64, "Your recurring macro example"
Application.OnTime earliesttime:=Now + TimeValue("00:00:10"), _
procedure:="MacroMania"
End Sub

Tom Urtis

Hi Mark,

It's perfectly possible to do this. Right click the worksheet tab and choose View Code. You'll see the code module for that particular sheet. You can use the two combo boxes at the top to create empty procedures for different worksheet events (e.g. someone changing a value, changing selection, deactivating the sheet). Choose Worksheet in the left combobox and Calculate in the right box. You can then call your macro from there i.e.

Private Sub Worksheet_Calculate()
MySub
End Sub

You can also make the macro fire for all sheets or certain sheet by double clicking the ThisWorkbook icon in the project explorer and choosing Workbook on the left and SheetCalculate on the right e.g.

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
'Only fire the macro if sheet1 or sheet2 are calculating
If Sh.Name = "sheet1" Or Sh.Name = "sheet2" Then MySub
End Sub

HTH,
Daniel.