I'm a novice when it comes to VBA and am usually able to find solutions by searching the forums, however I just can't seem to get this one right.
I've read most of the sources I could find in regards to starting a looping timer on workbook open and then stopping it upon workbook close, and have gotten it to start and continue looping just fine... the problem is I can't get it to stop. If I close the workbook and continue working on other spreadsheets it will open again shortly after.
Essentially I'm trying to get a macro to run every 5 minutes, starting when the workbook is initially opened and ending when the workbook closes.
Below is the code I'm using:
Update = the procedure I want to run every 5 minutes
RefreshXML = the Ontime loop
StopRefresh = cancel the Ontime loop
Within the ThisWorkbook object:
Within a module:Code:Private Sub Workbook_Open() Update End Sub Private Sub Workbook_BeforeClose(cancel As Boolean) StopRefresh End Sub
If anyone could let me know what I'm doing wrong i'd be grateful.Code:Public dtime As Double Sub Update() Windows("Testrss.xls").Activate ActiveWorkbook.XmlMaps("rss_Map").DataBinding.Refresh Sheet2.Cells.EntireRow.AutoFit RefreshXML End Sub Sub RefreshXML() dtime = Now + TimeValue("00:05:00") Application.OnTime dtime, "Update" End Sub Sub StopRefresh() On Error Resume Next Application.OnTime dtime, "Update", Schedule:=False End Sub
Please let me know if I need to provide any additional details.
Thanks so much,
I presume you have seen this thread:
There are currently 1 users browsing this thread. (0 members and 1 guests)