clear the results page at midnight with AutoReloadQuickpickL

[dflowe]

Maybe Garry can help.....
I am trying to AutoReloadQuickpickList and clear the results page.
I have tried several ideas see thread dflowe
now adding the line Worksheets("Sheet2").Range("A2:F200").Value = "" will not clear sheet 2 but
adding Worksheets("Sheet3").Range("A2:F200").Value = "" will work and sheet 3 is cleared, so the code works
but somthing is stopping the clear for the sheet 2 that is tied to bet ass as the results page.

Is there something else i have to do to clear the results page???????????????

regards
Dave

[MarkRussell]

Hi,

There is a trigger for clearing the Results Sheet.

Put -7 in cell Q2

Regards,
Mark

[dflowe]

oh bugger, missed that, sorry Mark, thanks for the answer...............
Dave

[dflowe]

will this work?

If triggerFirstMarketSelect Then
triggerFirstMarketSelect = False
Range("Q2").Value = -5
Range("Q2").Value = -7
ta
Dave

[bolpx001]

Hi Dave- Maybe i9t is just a typo but why are you setting q2 to -5 and then immediately setting it to -7 - Paul

If triggerFirstMarketSelect Then
triggerFirstMarketSelect = False
Range("Q2").Value = -5
Range("Q2").Value = -7

[dflowe]

[quote="bolpx001"]Hi Dave- Maybe i9t is just a typo but why are you setting q2 to -5 and then immediately setting it to -7 - Paul

If triggerFirstMarketSelect Then
triggerFirstMarketSelect = False
Range("Q2").Value = -5
Range("Q2").Value = -7


to set the first runner in the qpl and then clear the results page i hope......but i may have to add some more code....thats why i posted it......hoping someone would say yay or nay
dave

[bolpx001]

Hi Dave, I think you need to let the system process Q2=-5 and on the next refresh set Q2=-7 - best regards Paul

[dflowe]

If anyone is interested this works........................
in the module...............
Public triggerQuickPickListReload As Boolean
Public triggerFirstMarketSelect As Boolean
Public triggerClearResults As Boolean

Public Sub loadQuickPickList()
triggerQuickPickListReload = True
Application.OnTime TimeValue("00:00:00"), "loadQuickPickList"
End Sub

and in the sheet.....................

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count = 16 Then
Application.EnableEvents = False
If triggerQuickPickListReload Then
triggerQuickPickListReload = False
Range("Q2").Value = -3.1
triggerClearResults = True
Else
If triggerClearResults Then
triggerClearResults = False
Range("Q2").Value = -5
triggerFirstMarketSelect = True
Else
If triggerFirstMarketSelect Then
triggerFirstMarketSelect = False
Range("Q2").Value = -7
End If
End If
End If
Application.EnableEvents = True
End If
End Sub