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

Update	time
Startup	Startup
Manual	Manual
2 min	00:02
10 min	00:10
15 min	00:15
20 min	00:20
25 min	00:25
30 min	00:30
45 min	00:45
60 min	01:00
90 min	01:30
2 hour	02:00
3 hour	03:00
6 hour	05:00
12 hour	12:00

<tbody>
</tbody>

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 ,