Under ThisWorkbook you enter the following code only. This code runs when the workbook is opened and sets up the loadQuickPickList to run at the next 6am
- Code: Select all
Option Explicit
Private Sub Workbook_Open()
Application.OnTime TimeValue("06:00:00"), "loadQuickPickList"
End Sub
In a module you need to enter the following , you can add it to an existing module but make sure to declare the Public Boolean variables at the top.
- Code: Select all
Public triggerQuickPickListReload As Boolean
Public triggerFirstMarketSelect As Boolean
Public Sub loadQuickPickList()
triggerQuickPickListReload = True
Application.OnTime TimeValue("06:00:00"), "loadQuickPickList"
End Sub
Sub Refresh()
If triggerQuickPickListReload Then
triggerQuickPickListReload = False
Worksheets("Sheet1").Range("Q2").Value = -4
triggerFirstMarketSelect = True
Else
If triggerFirstMarketSelect Then
triggerFirstMarketSelect = False
Worksheets("Sheet1").Range("Q2").Value = -5
Worksheets("Sheet2").Range("Q2").Value = -5
Worksheets("Sheet3").Range("Q2").Value = -5
Worksheets("Sheet4").Range("Q2").Value = -5
Worksheets("Sheet5").Range("Q2").Value = -5
Worksheets("Sheet6").Range("Q2").Value = -5
End If
End If
End Sub
Because you already have a worksheet change routine running we need to call the Refresh sub routine from that, because you having code running to switch markets we need to ensure the code runs at the bottom of your Worksheet_Change routine so any changes to cell Q2 aren't overwritten. I don't know if you're turning events on and off in the worksheet_change routine but if you are it should be put before them. So to check the Refresh at each refresh we would stick in Call Refresh at the botto of your exisiting Worksheet_Change in Sheet1 only, it doesn't go in all Sheets. So Sheet1 Worksheet_Change will now look like
- Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False 'Turn off events
.....all your exisiting code
Call Refresh
Application.EnableEvents = True 'Turn on events again
End Sub
I haven't tested it but you can easily test it yourself by tweaking the time in loadQuickPickList to say 2 minutes from now and running the loadQuickPickList from macros. I'll test it later if i get time.