Excel Macro automatically refreshes workbook based on user input time

I use a lot of WEBSERVICE calls in my book which is not installed. Therefore, the only way to periodically update the values ​​is with a macro. To do this automatically after opening the workbook and every 30 seconds thereafter, the following works great:

Dim TimeToRun

Sub auto_open()
    Call ScheduleWorkbookRefresh
End Sub

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

Sub WorkbookRefresh()
    Call ScheduleWorkbookRefresh
End Sub

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


As usual, users claim that the 30 second refresh interval is between too short and too long. So the idea is to let users fill in the spacing they want in cell B9. However, it doesn't seem like an acceptable way to put the cell number (or variable) in the TimeValue function.

Any ideas on how I can modify the macro so that users can choose their own refresh interval, other than making the macro editable by the user (similar to transferring a loaded gun, safety to the chimp squad)?


source to share

1 answer

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



All Articles