midnight update quickpick list

[dflowe]

Hi was wondering if anyone could help.... i need the words to add to the macro to get the macro to clear an area of sheet 2 in this work book
i dont seem to be able to find the right way......I have added the 2 lines in red and it clears sheet 1 ok but i want it to clear sheet 2 so I added
Range("Sheet2!A2:F200").Select but it crashes so obviously incorrect.
anyone know what i should put in??
thanks Dave

Private Sub Worksheet_Change(ByVal Target As Range)
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
triggerFirstMarketSelect = True
Else
If triggerFirstMarketSelect Then
triggerFirstMarketSelect = False
Range("Q2").Value = -5
Range("A2:F200").Select
Selection.ClearContents End If
End If
Application.EnableEvents = True
End If
End Sub

[Marshy]

To select a range on sheet2 you want
Sheets("Sheet2").Range("A2:F200").select
Selection.ClearContents

but you can do it all in one line if you just want to clear the contents:
Sheets("Sheet2").Range("A2:F200").ClearConents

Hope this helps
Marshy

[dflowe]

Ahhh thanks, I was almost right but almost is not good enough in this instance

thanks again
Dave

[Captain Sensible]

I just set the cells to be blank whenever I want to clear a range

Sheets("Sheet2").Range("A2:F200").Value=""

[dflowe]

Thanks for the help but it still did not clear the sheet 2 overnight but it did update the quick pick list and set to first race.
now here is a conundrum.
If i start the system fresh.....it all works as it connects (on open)
if i run the macro.......it all works....
but it will not refresh the sheet2 at midnight so I have missed somethige maybe out of a module???????

Anyone got any ideas?

thanks dave

[Captain Sensible]

Is that all the coding? Have you copied it from somewhere else? You have things duplicated like

Private Sub Worksheet_Change(ByVal Target As Range)
Private Sub Worksheet_Change(ByVal Target As Range)

the statement

If triggerQuickPickListReload Then

doesn't seem to mean anything so it will go to the else statement and trigger Q2 to equal -5 i.e go to the start of the quick pick, it'll load the quickpick by default when you open the program. Are any of these variables like triggerQuickPickListReload declared anywhere ? Might be easier to post the code you're currently using to see if there's any obvious errors rather than jus t us trying to guess.

[Captain Sensible]

I'm sure gary posted an example sheet somewhere to reload markets at midnight. I just use code similar to this in the ThisWorkbook module to reload my quickpick routine for the morning.

Code: Select all


Option Explicit
Private Sub Workbook_Open()
Application.OnTime TimeValue("00:30:00"), "quickpick"
   
End Sub

[dflowe]

HIya thanks for replying the code in the sheet is:-
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
triggerFirstMarketSelect = True
Else
If triggerFirstMarketSelect Then
triggerFirstMarketSelect = False
Range("Q2").Value = -5
Sheets("Sheet2").Range("A2:F200").Value = ""
End If
End If
Application.EnableEvents = True
End If
End Sub

and in the module

Public triggerQuickPickListReload As Boolean
Public triggerFirstMarketSelect As Boolean

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

[Captain Sensible]

Do you have the following code under ThisWorkbook? That sets the loadQuickPickList to run at 12. And then quickpicklist resets it again for 12 once it runs.

Code: Select all

Option Explicit

Private Sub Workbook_Open()
    Application.OnTime TimeValue("00:00:00"), "loadQuickPickList"
End Sub


Maybe change the line

Sheets("Sheet2").Range("A2:F200").Value = ""

to

Worksheets("Sheet2").Range("A2:F200").Value = ""

if that's not clearing things.

You can always test things by changing the times to 10 minutes from now to check they work.

[dflowe]

thanks, cant understand why it worked when i tested the macro from the spread sheet and it worked ok at startup

[Captain Sensible]

When you tested the macro you ran the loadQuickPickList routine which then resets the loadQuickPickList to run at 00:00, when you open the program it automatically loads the quickpick list by default both would trigger your -5

[dflowe]

ahhh no it clears the sheet 2 as well in both those senarios, it just did not clear sheet 2 at midnight....thast what so confusing

[Captain Sensible]

You can always put the Worksheets("Sheet2").Range("A2:F200").Value = "" within your module instead

Code: Select all

Public Sub loadQuickPickList()
triggerQuickPickListReload = True
Application.OnTime TimeValue("00:00:00"), "loadQuickPickList"
Worksheets("Sheet2").Range("A2:F200").Value = ""
End Sub

[dflowe]

hi and thanks for that, will try that tonight in the sub routine

[dflowe]

hiya, thanks for your patience but that mod into the module stopped the macro completely......
and if i remove the line it works so
should there be a declaration or something as well?