Auto run Macro every nth minute

[fredrerik84]

A couple of days ago the site where I get my currency from made some changes and killed my project :/
I'm just finished rewriting this. Earlier I had just web page import in excel which worked just fine , but now I have rewritten this as a web scraping project and I'm proud to say its working much better now.

So to my problem. I really would like my currencies script to be run automatically every nth minute which I can change depending on the mood

I made these global variables:

Public RunWhen As Double
Public Const cRunWhat = "Exchangerates"  ' the name of the procedure to run
Public Const cRunIntervalSeconds = 2700  ' 45 min

And this code to auto run the script:

Option Explicit


Sub xratetimer()
Dim wb As Worksheet
Set wb = ActiveWorkbook.sheets("Conversion")
Dim sheet As Worksheet
Set sheet = ActiveWorkbook.sheets("Data")
Dim timer As String
Dim triggertimer As String


timer = wb.Cells(3, "L")


If timer = "Startup" Then
   call StopTimer 
   Exit Sub
ElseIf timer = "Manual" Then
   call StopTimer
   Exit Sub
Else
    RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
    Schedule:=True
End If
End Sub


Sub StopTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
        Schedule:=False
End Sub


Sub updatenow()
Call StopTimer
Call Exchangerates
End Sub

So basically everything woks just fine now. But in my workbook sheet "Conversion" I have in Cell "L3" Auto update frequency selector. How can I integrate this into the global variable I have set as cRunIntervalSeconds ?

I have a list selector here where I want to be able to choose between Never, Startup , 1 min , 5 min, 10, min, 30, min , 1h , 2h, and so on

(also these values are stored in my sheet "Data" I was thinking vlookup to convert 1min to 60 sec , 2 min - 120 sec, like this

[TABLE="width: 253"]
<tbody>[TR]
[TD]Update[/TD]
[TD]time[/TD]
[/TR]
[TR]
[TD]Startup[/TD]
[TD]Startup[/TD]
[/TR]
[TR]
[TD]Manual[/TD]
[TD]Manual[/TD]
[/TR]
[TR]
[TD]2 min[/TD]
[TD]00:02[/TD]
[/TR]
[TR]
[TD]10 min[/TD]
[TD]00:10[/TD]
[/TR]
[TR]
[TD]15 min[/TD]
[TD]00:15[/TD]
[/TR]
[TR]
[TD]20 min[/TD]
[TD]00:20[/TD]
[/TR]
[TR]
[TD]25 min[/TD]
[TD]00:25[/TD]
[/TR]
[TR]
[TD]30 min[/TD]
[TD]00:30[/TD]
[/TR]
[TR]
[TD]45 min[/TD]
[TD]00:45[/TD]
[/TR]
[TR]
[TD]60 min[/TD]
[TD]01:00[/TD]
[/TR]
[TR]
[TD]90 min[/TD]
[TD]01:30[/TD]
[/TR]
[TR]
[TD]2 hour[/TD]
[TD]02:00[/TD]
[/TR]
[TR]
[TD]3 hour[/TD]
[TD]03:00[/TD]
[/TR]
[TR]
[TD]6 hour[/TD]
[TD]05:00[/TD]
[/TR]
[TR]
[TD]12 hour[/TD]
[TD]12:00[/TD]
[/TR]
</tbody>[/TABLE]


Was alot of info here hope anyone takes to time to help a little ..

 

[fredrerik84]

Sorry for the very long post , I think i have found an solution for my problem. I set this public empty: Public cRunIntervalSeconds

and it loads info in it on workbook open ,