How to loop this function!

[Scorpion Steve]

Hey, I've managed to figured out the layout of my project but need the below function/code to repeat/loop itself.
Can anyone help before I overdose on headache tablets please?
The code says: Once opened, run macro after 10mins

I would like it to say:
Once opened, run macro after ten mins, then count run macro after tens mins, then run macro after ten mins etc etc.

I need this to run every 20 mins from 6am to 5pm once opened.
(the actual time is not important, just the time scale! I.e 11hrs

Thanks

View code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.OnTime dTime, "RUN_SAVE", , False

End Sub

Private Sub Workbook_Open()

Application.OnTime Now + TimeValue("00:00:15"), "RUN_SAVE"

End Sub

 

[Jaafar Tribak]

Is it every 10 mins or 20 mins ? !

Anyways try this code in the workbook module which should run every 20 mins from 6 am to 5 pm :

Private dTime As Date

Private Sub Workbook_Open()

    dTime = Now + TimeValue("00:00:20")
    Application.OnTime dTime, Me.CodeName & ".RUN_SAVE"

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Application.OnTime dTime, "RUN_SAVE", , False

End Sub

Private Sub RUN_SAVE()

    If Time > #6:00:00 AM# And Time < #5:00:00 PM# Then
       [COLOR=SeaGreen][B] 'run your save code here...[/B][/COLOR]
        dTime = Now + TimeValue("00:00:20")
        Application.OnTime dTime, Me.CodeName & ".RUN_SAVE"
    Else
        Application.OnTime dTime, Me.CodeName & ".RUN_SAVE", , False
    End If

End Sub

 

[Scorpion Steve]

A----mazing!!!
perfect! How do you know all this so quick?
I will definitely look into a course to build this up!
Thankyou

 

[Jaafar Tribak]

A----mazing!!!
perfect! How do you know all this so quick?
I will definitely look into a course to build this up!
Thankyou

Yeah! VBA is cool and fairly easy to learn- Go for it

By the way, there is slight mistake in the code and that is the TimeValue("00:00:20") line which should be TimeValue("00:20:00").
20 Mins not 20 Secs.

Thanks for the feedback.

 

[Scorpion Steve]

That's fine. I appreciate the effort.
I am sooooo close to nailing this.
I just need 2 things.
The original code to save the sheet every 5 mins (I can change the times, I just cannot figure out how to make it save itself every time)
I have placed "ActiveWorkbook.Save" where you have written "'run your save code here..." - Is that correct?

Also, below is the code and below that is a macro that I have saved. I click a button to activate that. Can you sink that in the code as well please?
So, the one code should make the sheet save every 5 mins and do what the macro does.
I would like to have no button clicking.

Cheers,

 

[Scorpion Steve]

Private dTime As Date

Private Sub Workbook_Open()

dTime = Now + TimeValue("00:00:20")
Application.OnTime dTime, Me.CodeName & ".RUN_SAVE"

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.OnTime dTime, "RUN_SAVE", , False

End Sub

Private Sub RUN_SAVE()

If Time > #6:00:00 AM# And Time < #5:00:00 PM# Then
ActiveWorkbook.Save
dTime = Now + TimeValue("00:00:20")
Application.OnTime dTime, Me.CodeName & ".RUN_SAVE"
Else
Application.OnTime dTime, Me.CodeName & ".RUN_SAVE", , False
End If

End Sub








Sub RUN_AUTO_REFRESH()
'
' RUN_AUTO_REFRESH Macro
' Macro recorded 10/09/2011 by steve.howard
'

'
Application.RUN "'Console V1.2.xls'!ThisWorkbook.RUN_SAVE"
Range("M11:M12").Select
ActiveCell.FormulaR1C1 = "='[Console V1.2.xls]CONSOLE'!R23C14"
Range("M15:M16").Select
ActiveCell.FormulaR1C1 = "='[Console V1.2.xls]CONSOLE'!R23C14"
Range("M19:M20").Select
ActiveCell.FormulaR1C1 = "='[Console V1.2.xls]CONSOLE'!R23C14"
Range("M23:M24").Select
ActiveCell.FormulaR1C1 = "='[Console V1.2.xls]CONSOLE'!R23C14"
Range("M27:M28").Select
ActiveCell.FormulaR1C1 = "='[Console V1.2.xls]CONSOLE'!R23C14"
Range("M29").Select
End Sub

 

[Jaafar Tribak]

Try this :

Private dTime As Date

Private Sub Workbook_Open()

    dTime = Now + TimeValue("00:05:00")
    Application.OnTime dTime, Me.CodeName & ".RUN_SAVE"

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Application.OnTime dTime, "RUN_SAVE", , False

End Sub

Private Sub RUN_SAVE()

    If Time > #6:00:00 AM# And Time < #5:00:00 PM# Then
        [B][COLOR=Red]RUN_AUTO_REFRESH[/COLOR][/B]
        [COLOR=Red][B]Me.Save[/B][/COLOR]
        dTime = Now + TimeValue("00:05:00")
        Application.OnTime dTime, Me.CodeName & ".RUN_SAVE"
    Else
        Application.OnTime dTime, Me.CodeName & ".RUN_SAVE", , False
    End If

End Sub

 

[Scorpion Steve]

That should work great. I am looking through this now.
One question:
When I come to close the workbook, A Visual basic Message appears stating:
Run-time error '1004':
Method 'OnTime' of object' _Application' failed

On Debug: (highlighted in yellow)
"Application.OnTime dTime, "RUN_SAVE", , False"

Do I change to true? Or do I remove or change the code for Run save?

??

 

[Scorpion Steve]

Also, I have noticed that if I had another worksheet open, the macro uses that sheet once activated!
Where do I look to find out how to control the macro to stick with that particular sheet?

What happens when activated?
The refresh copies that data and pastes it in the active worksheet and not the sheet its running on!
Thanks

 

[Jaafar Tribak]

Also, I have noticed that if I had another worksheet open, the macro uses that sheet once activated!
Where do I look to find out how to control the macro to stick with that particular sheet?

What happens when activated?
The refresh copies that data and pastes it in the active worksheet and not the sheet its running on!
Thanks

Where do you have the RUN_AUTO_REFRESH Macro ? Is ir located in the Workbook module or in a Standard Module ?

Also, it is always safer to fully qualify your Ranges in code so for example the following line :

Range("M11:M12").Select
ActiveCell.FormulaR1C1 = "='[Console V1.2.xls]CONSOLE'!R23C14"

Should be :

ThisWorkbook.Sheets("YOUR SHEET NAME HERE").Range("M11:M12").FormulaR1C1 _
= "='[Console V1.2.xls]CONSOLE'!R23C14"