Excel Macro auto-refresh workbook based on user input time

Question

I use a lot of WEBSERVICE calls in my workbook, which is connectionless. Therefore, the only way to periodically refresh values is with a macro. To do so automatically upon workbook open and every 30 seconds thereafter, the following works great:

Dim TimeToRun

Sub auto_open()
Sheets("DataInput").Select
Sheets("DataInput").Range("A1").Activate
    Application.CalculateFull
    Call ScheduleWorkbookRefresh
End Sub

Sub ScheduleWorkbookRefresh()
    TimeToRun = Now + TimeValue("00:00:30")
    Application.OnTime TimeToRun, "WorkbookRefresh"
End Sub

Sub WorkbookRefresh()
    Application.CalculateFull
    Call ScheduleWorkbookRefresh
End Sub

Sub auto_close()
    Application.OnTime TimeToRun, "WorkbookRefresh", , False
End Sub

As usual, users claim the refresh interval of 30 seconds is somewhere between too short and too long. So, the idea is to let users fill in the interval they want in cell B9. However, there doesn't seem to be an acceptable way to put a cell number (or variable) into the TimeValue function.

Any ideas on how I might modify the macro to allow users to choose their own refresh interval, other than making the macro available for user edit (similar to handing a loaded gun, safety off, to a troop of chimpanzees)?

Answer 1

Use TimeToRun = Now + TimeValue("00:00:" & Sheets("DataInput").Range("A1").Value)