need help with VBA OuickPicks

Please post any questions regarding the program here.

Moderator: 2020vision

need help with VBA OuickPicks

Postby samhayer » Mon Sep 07, 2020 5:41 pm

Hi everyone I need help with this please can any one help I have done a exel sheet which runs ok it looks to place bets on all UK , IRE and US and AUS horse races I have set pre to load these markets so when I enter -3 in cell Q2 this works great but I need it to load automatic just after mid night . I have tried the sample sheets and other VBA codes just cant get it to work so have to get up at midnight to refresh . Please can any one tell me how ti do this thanks for any help
samhayer
 
Posts: 61
Joined: Wed Apr 11, 2012 4:57 am

Re: need help with VBA OuickPicks

Postby Captain Sensible » Mon Sep 07, 2020 7:24 pm

Try this that I posted in another thread, it'll store the date and when it changes stick -3 in Q2 then stick -5 in Q2 to reload the first market.

Code: Select all
Option Explicit
Public Today As Date, triggerQuickPickListReload As Boolean, triggerFirstMarketSelect As Boolean


Private Sub Worksheet_Change(ByVal Target As Range)
Static MyMarket As Variant
Static switched As Variant
           
        If Target.Columns.Count <> 16 Then Exit Sub
            Application.EnableEvents = False
            Application.Calculation = xlCalculationManual




            If Today <> Date Then
            Today = Date
            triggerQuickPickListReload = True
            End If



            If triggerQuickPickListReload Then
                triggerQuickPickListReload = False
                Worksheets("Sheet1").Range("Q2").Value = -3
                triggerFirstMarketSelect = True
            Else
                If triggerFirstMarketSelect Then
                    triggerFirstMarketSelect = False
                    Worksheets("Sheet1").Range("Q2").Value = -5
                 
                End If
            End If
       

           
           
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: need help with VBA OuickPicks

Postby Captain Sensible » Mon Sep 07, 2020 7:37 pm

Code should be as below, I'd just cut and pasted it but had missed taking out a couple of unneccesary pieces.

Code: Select all
Option Explicit
Public Today As Date, triggerQuickPickListReload As Boolean, triggerFirstMarketSelect As Boolean


Private Sub Worksheet_Change(ByVal Target As Range)
           
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False
Application.Calculation = xlCalculationManual




            If Today <> Date Then
            Today = Date
            triggerQuickPickListReload = True
            End If



            If triggerQuickPickListReload Then
                triggerQuickPickListReload = False
                Worksheets("Sheet1").Range("Q2").Value = -3
                triggerFirstMarketSelect = True
            Else
                If triggerFirstMarketSelect Then
                    triggerFirstMarketSelect = False
                    Worksheets("Sheet1").Range("Q2").Value = -5
                 
                End If
            End If
       

           
           
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: need help with VBA OuickPicks

Postby samhayer » Tue Sep 08, 2020 2:15 am

Hi Captain S thanks for your reply , I am no expert in VBA just try and learn as I go on so could you please tell me where I should put this code which sheet or Mod will it matter. As I think it should go in my sheet1which is the Market sheet but this has already got some code in it which looks like this below
Option Explicit

Dim currentMarket As String

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count = 16 Then
If [A1] <> currentMarket Then
currentMarket = [A1]

logBalance
Call Macro2
Call Macro4
Call Macro5
End If
End If
End Sub

Private Sub logBalance()
Application.EnableEvents = False
Dim r As Integer
r = 2
While Sheet2.Cells(r, 1) <> ""
r = r + 1
Wend
Sheet2.Cells(r, 1) = currentMarket
Sheet2.Cells(r, 2) = [I2]
Application.EnableEvents = True
End Sub

any and all advice will be most welcome thanks
samhayer
 
Posts: 61
Joined: Wed Apr 11, 2012 4:57 am

Re: need help with VBA OuickPicks

Postby Captain Sensible » Tue Sep 08, 2020 12:38 pm

You can just add it to the Worksheet_Change routine under Sheet1. I've posted a version with it for you below. I amended a few bits just to make it easier to read and put the routine into a Sub routine for you that'd just go into your modules as you seem to run everything as macros/sub routines. If you need any bits clarifying just ask. You can delete any lines starting with a single quote , they're just comments to explain things


Code: Select all
Option Explicit
Public Today As Date, triggerQuickPickListReload As Boolean, triggerFirstMarketSelect As Boolean
Public currentMarket As String  ' best to be a public variable so can be accessed by any routine

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False ' stopping events here mean we don't need to repeat it elsewhere
Application.Calculation = xlCalculationManual '

If Worksheets("Sheet1").Range("A1").Value <> currentMarket Then
currentMarket =Worksheets("Sheet1").Range("A1").Value ' usually best to write the full address in case the sheet is focussed on another sheet
logBalance
Call Macro2
Call Macro4
Call Macro5
End If

Call reloadQPL

Application.EnableEvents = True ' turn events back on
Application.Calculation = xlCalculationAutomatic
End Sub


Here's a slightly amended logBalance ( no need to turn off events again) and the reload sub routine that you can stick in the module with your macros

Code: Select all
Sub logBalance()
Dim r As Integer
r = 2
While Sheet2.Cells(r, 1) <> ""
r = r + 1
Wend
Sheet2.Cells(r, 1) = currentMarket
Sheet2.Cells(r, 2) = Worksheets("Sheet1").Range("I2").Value
End Sub

Sub reloadQPL()
If Today <> Date Then
    Today = Date
    triggerQuickPickListReload = True
End If

If triggerQuickPickListReload Then

                triggerQuickPickListReload = False
                Worksheets("Sheet1").Range("Q2").Value = -3
                triggerFirstMarketSelect = True
Else
                If triggerFirstMarketSelect Then
                triggerFirstMarketSelect = False
                Worksheets("Sheet1").Range("Q2").Value = -5
                End If
End If

End Sub


I haven't tested them but should be OK
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: need help with VBA OuickPicks

Postby Captain Sensible » Tue Sep 08, 2020 12:57 pm

Just realised the Public variables should be declared in the module rather than worksheet. I don't usually have my routines all called by modules. So change it to

Under Sheet1

Code: Select all
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False ' stopping events here mean we don't need to repeat it elsewhere
Application.Calculation = xlCalculationManual '

If Worksheets("Sheet1").Range("A1").Value <> currentMarket Then
currentMarket =Worksheets("Sheet1").Range("A1").Value ' usually best to write the full address in case the sheet is focussed on another sheet
logBalance
Call Macro2
Call Macro4
Call Macro5
End If

Call reloadQPL

Application.EnableEvents = True ' turn events back on
Application.Calculation = xlCalculationAutomatic
End Sub


And in your module the vaiables are declared

Code: Select all
Option Explicit
Public Today As Date, triggerQuickPickListReload As Boolean, triggerFirstMarketSelect As Boolean
Public currentMarket As String  ' best to be a public variable so can be accessed by any routine

Sub logBalance()
Dim r As Integer
r = 2
While Sheet2.Cells(r, 1) <> ""
r = r + 1
Wend
Sheet2.Cells(r, 1) = currentMarket
Sheet2.Cells(r, 2) = Worksheets("Sheet1").Range("I2").Value
End Sub

Sub reloadQPL()
If Today <> Date Then
    Today = Date
    triggerQuickPickListReload = True
End If

If triggerQuickPickListReload Then

                triggerQuickPickListReload = False
                Worksheets("Sheet1").Range("Q2").Value = -3
                triggerFirstMarketSelect = True
Else
                If triggerFirstMarketSelect Then
                triggerFirstMarketSelect = False
                Worksheets("Sheet1").Range("Q2").Value = -5
                End If
End If

End Sub


Untitled.png
Untitled.png (25.67 KiB) Viewed 14287 times
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: need help with VBA OuickPicks

Postby samhayer » Tue Sep 08, 2020 1:49 pm

Ahh thanks so much for your help, I will try this seems quite a lot to do and understand but I will try, Should keep me busy for while and I will let you know how I get on Thanks very much best Regards Sam Hayer
samhayer
 
Posts: 61
Joined: Wed Apr 11, 2012 4:57 am

Re: need help with VBA OuickPicks

Postby samhayer » Wed Sep 09, 2020 10:36 am

Good morning Captain Been trying to do this made a mess of my sheets stopped working, but got all back to try again . so I think I can do with out the logBalance . I think I will delete that in sheet 1 and try paste

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False ' stopping events here mean we don't need to repeat it elsewhere
Application.Calculation = xlCalculationManual '

If Worksheets("Sheet1").Range("A1").Value <> currentMarket Then
currentMarket =Worksheets("Sheet1").Range("A1").Value ' usually best to write the full address in case the sheet is focussed on another sheet
logBalance
Call Macro2
Call Macro4
Call Macro5
End If

Call reloadQPL

Application.EnableEvents = True ' turn events back on
Application.Calculation = xlCalculationAutomatic
End Sub


and am I right to think I should paste the below into Mod1
Option Explicit
Public Today As Date, triggerQuickPickListReload As Boolean, triggerFirstMarketSelect As Boolean
Public currentMarket As String ' best to be a public variable so can be accessed by any routine

Sub logBalance()
Dim r As Integer
r = 2
While Sheet2.Cells(r, 1) <> ""
r = r + 1
Wend
Sheet2.Cells(r, 1) = currentMarket
Sheet2.Cells(r, 2) = Worksheets("Sheet1").Range("I2").Value
End Sub
samhayer
 
Posts: 61
Joined: Wed Apr 11, 2012 4:57 am

Re: need help with VBA OuickPicks

Postby Captain Sensible » Wed Sep 09, 2020 11:00 am

samhayer wrote:
and am I right to think I should paste the below into Mod1
Option Explicit
Public Today As Date, triggerQuickPickListReload As Boolean, triggerFirstMarketSelect As Boolean
Public currentMarket As String ' best to be a public variable so can be accessed by any routine

Sub logBalance()
Dim r As Integer
r = 2
While Sheet2.Cells(r, 1) <> ""
r = r + 1
Wend
Sheet2.Cells(r, 1) = currentMarket
Sheet2.Cells(r, 2) = Worksheets("Sheet1").Range("I2").Value
End Sub


As long as the Public variables are declared in a module they can go at the top of the one with all your macros in. You can easily log the balance with a couple of lines and stick it in the main Worksheet_Change routine rather than a module if needed. I didn't tinker with it as assumed you wanted lots of modules, maybe to make it easier to amend in the future.

Just make sure the reloadQPL sub routine/macro is also in the module.
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: need help with VBA OuickPicks

Postby samhayer » Wed Sep 09, 2020 11:21 am

Hmm tried but just froze I must be putting in the wrong place all a bit clever for me not to worry I will keep trying thanks
samhayer
 
Posts: 61
Joined: Wed Apr 11, 2012 4:57 am

Re: need help with VBA OuickPicks

Postby Captain Sensible » Wed Sep 09, 2020 11:25 am

Do you still have your original working version of the sheet and know how to copy your macros into the module?
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: need help with VBA OuickPicks

Postby rluk » Wed Sep 09, 2020 11:31 am

I get mine to update with the following.

Under "Microsoft Excel Objects" Sheet1(Market) I have the below code. Note in my workbook "Market" is where prices log to. Cell AD1 is to trigger a refresh of my historical price log when the market changes (not relevant for quick pick update) and AJ1 is to trigger a Quick Pick Update.

Code: Select all
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False
If Range("AD1") = "Y" Then
Refresh
Else
If Range("AJ1") = "Y" Then
QuickPickUpdate
Else
End If
End If
Application.EnableEvents = True
End Sub


The value of Cell AJ1 is set by:
AJ1=IF(AND(Market!$AK$1>Parameters!$D$29,Market!$AK$1<Parameters!$E$29),"Y","N")
Where:
AK1=NOW()-INT(NOW())
Parameters!$E$29=12:00:05 AM
Parameters!$D$29=12:00:07 AM

So once it hits 5 seconds past midnight the macro QuickPickUpdate triggers which just puts a number in Q2.

Code: Select all
Sub QuickPickUpdate()
Application.ScreenUpdating = False
    Sheets("Market").Select
    Range("Q2").Select
    ActiveCell.FormulaR1C1 = "-4.1"
    Range("AA2").Select
Application.ScreenUpdating = True
End Sub


This code is in Modules.

Probably not the most correct way to program, but it works and I am no VBA expert, just trying to get my bot to do what I want it to!
rluk
 
Posts: 9
Joined: Wed May 13, 2020 11:44 pm

Re: need help with VBA OuickPicks

Postby samhayer » Wed Sep 09, 2020 11:40 am

Yes I my sheet and I can copy the macros and I have deleted the logBalance part out of sheet one now so it looks like

Option Explicit

Dim currentMarket As String

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count = 16 Then
If [A1] <> currentMarket Then
currentMarket = [A1]


Call Macro2
Call Macro4
Call Macro5
End If
End If
End Sub

the sheet seems to be working okay so I just need it to auto load the quick picks but when I tried this line went yellow Private Sub Worksheet_Change(ByVal Target As Range)
samhayer
 
Posts: 61
Joined: Wed Apr 11, 2012 4:57 am

Re: need help with VBA OuickPicks

Postby Captain Sensible » Wed Sep 09, 2020 11:43 am

I sent you a link to a sheet so you can just copy and paste to your working sheet. IT's the second link as the first one I'd commented out your macros cos I didn't have them
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: need help with VBA OuickPicks

Postby samhayer » Wed Sep 09, 2020 12:10 pm

Okay so I cleared all from sheet 1 on my sheet and pasted from your sheet with the makro on from sheet 1 to my sheet and again this line
Private Sub Worksheet_Change(ByVal Target As Range)
turns yellow shows as something is not right ,got all day so I will try and keep trying till we get this thanks for your help
samhayer
 
Posts: 61
Joined: Wed Apr 11, 2012 4:57 am

Next

Return to Help

Who is online

Users browsing this forum: No registered users and 33 guests

Sports betting software from Gruss Software


The strength of Gruss Software is that it’s been designed by one of you, a frustrated sports punter, and then developed by listening to dozens of like-minded enthusiasts.

Gruss is owned and run by brothers Gary and Mark Russell. Gary discovered Betfair in 2004 and soon realised that using bespoke software to place bets was much more efficient than merely placing them through the website.

Gary built his own software and then enhanced its features after trialling it through other Betfair users and reacting to their improvement ideas, something that still happens today.

He started making a small monthly charge so he could work on it full-time and then recruited Mark to help develop the products and Gruss Software was born.

We think it’s the best of its kind and so do a lot of our customers. But you can never stand still in this game and we’ll continue to improve the software if any more great ideas emerge.