Some help need with refresh query VBA

[klatlap]

I found this code in a search for an auto refresh macro, originally it refreshed a web query but i removed that part and replaced with rerunning the query macro, i found this a better option.

I have 2 issues i need some help with.

1) How is it linked the F12 and could this be changed?
2) This starts in the On position when the sheet opens, i would like that to be off by default with the message that displays how to turn back on,

Thanks in advance.

'A public variable that will stay in memory while the book is open
'The value of this variable will be False by default.
'Its used as an indicator so you know whether to refresh or not in the RefreshQuery procedure
Public RefreshOn As Boolean
Public RunWhen As Double

Public Sub ToggleRefresh()
'Toggle the refresh to the opposite that it is now
'ie if its currently false then the variable will now be true and visa versa
RefreshOn = Not RefreshOn

If RefreshOn = True Then
'If RefreshOn is true you want to start the refresh process
    Call RefreshQuery
    MsgBox "Web Query Refreshing is ON." & vbLf & vbLf & _
        "To toggle refreshing ON/OFF press the F12 key.", vbInformation, "Web Query Refresh Status"
Else
    'stop the pending ontime procedure
    On Error Resume Next
   Application.OnTime RunWhen, "RefreshQuery", schedule:=False
    On Error GoTo 0
    MsgBox "Web Query Refreshing is OFF." & vbLf & vbLf & _
    "To toggle refreshing ON/OFF press the F12 key.", vbInformation, "Web Query Refresh Status"
End If

End Sub


Public Sub RefreshQuery()
Import

'Repeat this procedure every 30 seconds. The false argument should clear
'the Ontime event if its in memory ready to run (ie stop it running twice in
'quick succession). You check to see if RefreshOn is true before repeating
'the procedure again
If RefreshOn = True Then
    RunWhen = Now + TimeValue("00:00:10")
    On Error Resume Next
    Application.OnTime RunWhen, "RefreshQuery"
    On Error GoTo 0
End If

End Sub

 

[klatlap]

Found the F12 key, forgot this code was in the ThisWorkBook page.

Private Sub Workbook_Open()
'Monitor the F12 key and when pressed run the ToggleRefresh procedure
Application.OnKey "{F12}", "ToggleRefresh"

'The refresh process will be off by default when the book is opened
'If you want it on then run this line below.
'Note because the RefreshOn variable is false by default and ToggleRefresh
'changes it to the opposite then RefreshOn will be changed to True and the
'RefreshQuery procedure will be called by the ToggleRefresh procedure.
Call ToggleRefresh

End Sub

 

[klatlap]

Sorted both issues by adjusting this.

Private Sub Workbook_Open()
'Monitor the F12 key and when pressed run the ToggleRefresh procedure
Application.OnKey "{F12}", "ToggleRefresh"

'The refresh process will be off by default when the book is opened
'If you want it on then run this line below.
'Note because the RefreshOn variable is false by default and ToggleRefresh
'changes it to the opposite then RefreshOn will be changed to True and the
'RefreshQuery procedure will be called by the ToggleRefresh procedure.
MsgBox "Web Query Refreshing is OFF." & vbLf & vbLf & _
    "To toggle refreshing ON/OFF press the F12 key.", vbInformation, "Web Query Refresh Status"


'Call ToggleRefresh

End Sub