How to start / stop auto-refresh?

[Chartist]

I have a API datasource that updates continuously. Instead of having to press Refresh All constantly, I found this 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.

 

[Misca]

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.

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.

 

[Chartist]

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.

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]

The basic code to change the text of a Form Control Button is something like:

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.