How to start / stop auto-refresh?

Chartist

Board Regular
Joined
Apr 2, 2007
Messages
126
Office Version
  1. 365
Platform
  1. Windows
I have a API datasource that updates continuously. Instead of having to press Refresh All constantly, I found this VBA code:

VBA Code:
Sub AutoRefresh()

ActiveWorkbook.RefreshAll

'Auto-Refresh in 0 hours, 1 minute, 0 seconds

NextTime = Time + TimeSerial(0, 1, 0)

Application.OnTime NextTime, "AutoRefresh"

End Sub

This refreshes the data every minute.

How do I change this to be able to start/stop the refresh? I.e. when I press the button it should start or stop refreshing the data every minute.

Thanks.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,715
Here's how I'd do it:

Add the following code to your Module & change the button to trigger the RefreshControl macro instead of the AutoRefresh macro.
VBA Code:
Public MyCondition As Boolean


Sub RefreshControl()

MyCondition = Not (MyCondition)

Call AutoRefresh

End Sub


Sub AutoRefresh()

Dim NextTime As Double

If MyCondition Then

ActiveWorkbook.RefreshAll

'Auto-Refresh in 0 hours, 1 minute, 0 seconds
NextTime = Time + TimeSerial(0, 1, 0)
        
        Application.OnTime NextTime, "AutoRefresh"
End If

End Sub
The RefreshControl macro toggles the state of the MyCondition boolean. When you open your workbook the state is False. Nothing happens. Then you fire the RefreshControl the state becomes true and AutoRefresh activates. The next time you press the button the state is False again. AutoRefresh runs again but because it's controlled by the MyCondition nothing happens 'till you run the RefreshControl again and MyCondition becomes True again.
 
Solution

Chartist

Board Regular
Joined
Apr 2, 2007
Messages
126
Office Version
  1. 365
Platform
  1. Windows
Here's how I'd do it:

Add the following code to your Module & change the button to trigger the RefreshControl macro instead of the AutoRefresh macro.
VBA Code:
Public MyCondition As Boolean


Sub RefreshControl()

MyCondition = Not (MyCondition)

Call AutoRefresh

End Sub


Sub AutoRefresh()

Dim NextTime As Double

If MyCondition Then

ActiveWorkbook.RefreshAll

'Auto-Refresh in 0 hours, 1 minute, 0 seconds
NextTime = Time + TimeSerial(0, 1, 0)
       
        Application.OnTime NextTime, "AutoRefresh"
End If

End Sub
The RefreshControl macro toggles the state of the MyCondition boolean. When you open your workbook the state is False. Nothing happens. Then you fire the RefreshControl the state becomes true and AutoRefresh activates. The next time you press the button the state is False again. AutoRefresh runs again but because it's controlled by the MyCondition nothing happens 'till you run the RefreshControl again and MyCondition becomes True again.
Thanks!

An additional question if you don't mind - how do I display the current status (whether it is currently stopped or refreshing every minute?)

Is there a way to make the button text change to Start when the macro is stopped, and Stop when the macro is runing every minute?
 

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,715
The basic code to change the text of a Form Control Button is something like:
VBA Code:
Sheet1.Buttons(1).Caption = "Hello"

If you're using a shape instead of a "real" button the code becomes a bit more complex. What I normally do is instead of writing the text to a shape I select the shape and write something like =A1 to the formula bar. This way the button shows whatever I write into A1. It's a lot easier to write .Range("A1")="Hello" than something like .Shapes("Rounded Rectangle 1").TextFrame2.TextRange.Text = "Hello" The user won't notice any difference between the two as long as the cell your getting the text from is not visible.

You can also record a macro when you change the text and take it from there. When you record a macro you'll get a lot of unnecessary junk as well.

It's normally a good thing to get rid of all the bits you don't really need: If a line ends with ".Select" and the next one starts with "Selection" write them both to a single line.

Try not to have any Selections on your final code and try to use specific sheet names instead of "ActiveSheet". This way your code becomes more reliable. Unless, of course, you want to be able to run the same code on multiple sheets.
 

Forum statistics

Threads
1,144,629
Messages
5,725,387
Members
422,622
Latest member
Paranthem

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top