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() Sheets("DataInput").Select Sheets("DataInput").Range("A1").Activate Application.CalculateFull Call ScheduleWorkbookRefresh End Sub Sub ScheduleWorkbookRefresh() TimeToRun = Now + TimeValue("00:00:30") TimeToRun, "WorkbookRefresh" End Sub Sub WorkbookRefresh() Application.CalculateFull Call ScheduleWorkbookRefresh End Sub Sub auto_close() 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