Hi everyone, i'd like to contribute this spreadsheet to the commuinity for all the traders who enjoy working directly from the spreadsheet.
I have created a simple 12 buttons (6 Back and 6 Lay so this would be perfect for the dogs) that are macro'd to place orders quickly and efficiently. All selfexplanatory, odds adjust whether you're backing or laying and the stake is represented in cell I3. When you type the word "PLACED" into any of the status cells it clears the L column first and then the O column so there shouldnt be multiple orders being fired into the market, however when I tried testing this just they didn't clear. Please could somebody handy with VBA look into the following code and let me know why that may be happening, probably something really simple i'm overlooking:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Flag As Boolean
If Target.Address = ActiveSheet.Range("O" & 9).Address Then
If Target.Text = "PLACED" Then
If Not Flag Then
ActiveSheet.Cells(9, 12).ClearContents
ActiveSheet.Cells(9, 15).ClearContents
Flag = True
End If
End If
Flag = True
End If
End Sub
Happy trading!!
Place Orders and Clear Cells Spreadsheet Template
-
- Posts: 575
- Joined: Wed Apr 19, 2017 5:12 pm
- Location: Wolverhampton
You do not have the required permissions to view the files attached to this post.
- MemphisFlash
- Posts: 2222
- Joined: Fri May 16, 2014 10:12 pm
- Location: Leicester
try this instead
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("O9:O17") = "PLACED" Then
Application.EnableEvents = False
Range("O9:O17") = ""
Application.EnableEvents = True
End If
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("O9:O17") = "PLACED" Then
Application.EnableEvents = False
Range("O9:O17") = ""
Application.EnableEvents = True
End If
-
- Posts: 575
- Joined: Wed Apr 19, 2017 5:12 pm
- Location: Wolverhampton
No such luck Memphis
Has it worked when you've put that code in? It's so strange as it works when not connected, you type 'PLACED' and both cells clear perfectly but then not when connected. Perhaps Bet Angel is doing something with cell O9 constantly so it's interfering with the macro? I chose another random cell and (=O9) and then changed the macro to use this cell as a trigger instead but it didn't even clear contents when it wasn't connected. Any ideas?
Has it worked when you've put that code in? It's so strange as it works when not connected, you type 'PLACED' and both cells clear perfectly but then not when connected. Perhaps Bet Angel is doing something with cell O9 constantly so it's interfering with the macro? I chose another random cell and (=O9) and then changed the macro to use this cell as a trigger instead but it didn't even clear contents when it wasn't connected. Any ideas?
Have you updated excel lately?
The recent update has caused some issues
viewtopic.php?f=19&t=15746&start=10
The recent update has caused some issues
viewtopic.php?f=19&t=15746&start=10
If you want to clear a range of cells rather than just a single one you could use:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range
For Each cel In Range("O9:O17")
If cel = "PLACED" Then
Application.EnableEvents = False
cel = ""
Application.EnableEvents = True
End If
Next cel
End Sub
If you know the one you want to clear it'll be along the lines of:
If Range("O9") = "PLACED" Then
Application.EnableEvents = False
Range("O9") = ""
Application.EnableEvents = True
End If
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range
For Each cel In Range("O9:O17")
If cel = "PLACED" Then
Application.EnableEvents = False
cel = ""
Application.EnableEvents = True
End If
Next cel
End Sub
If you know the one you want to clear it'll be along the lines of:
If Range("O9") = "PLACED" Then
Application.EnableEvents = False
Range("O9") = ""
Application.EnableEvents = True
End If
-
- Posts: 575
- Joined: Wed Apr 19, 2017 5:12 pm
- Location: Wolverhampton
Thank you for the input gents, I have just ammended the code and it is working perfectly on practise mode. All 12 buttons place orders into the market and then the status is cleared and even at 20m/s no multiple orders have gone through with my 30 second test. Please everybody feel free to use this template and improve upon it with your own formulas and algorithms. If you have any basic questions, i'll try my best to help out
Callum
You do not have the required permissions to view the files attached to this post.