In desperate need of help!

[breakbacktennis]

Hi, Im in desperate need of help with this VBA code. The code works fine when entered manually however I need the code to work when linked with a live feed.

[COLOR=#333333]Private Sub Worksheet_Change(ByVal Target As Range)[/COLOR]    Dim KeyCells As Range




   If Target.Address <> Range("G1").Address Then Exit Sub
    


        If Range("G1").Value = "In-play" Then
               Range("U9").Value = Range("G9").Value
               Range("U11").Value = Range("G11").Value
        End If

 [COLOR=#333333]End Sub[/COLOR]

 

[breakbacktennis]

No problems Silentwolf appreciate your time, thankyou.

 

[CodeNinja]

Sorry Breakbacktennis and Silentwolf... I had to go home at the end of the day there...

You need a few things to handle this... First, you need to prevent a recursive loop (You change something on the sheet which causes the macro to run which changes something which causes the macro to run again and repeat for ever). To do that, you need to turn enable events off. After that, you need a global constant to keep track of whether you started in the "In-Play" position. This is to prevent the situation where it already says "In-play" in G1 and something else changes on the screen... I assume you don't want the changes to happen then as well... The global variable will remember it's value even after the macro has fired. The global variable needs to be set in a module so that it can be set upon workbook open (This will prevent a saved version from being confused the first time something is changed on the sheet after opening).


So... insert a module and put this code in it:

Global bIsInTarget As Boolean

In your thisworkbook object, add the following code:
Change sheet2 to whatever sheet has the reference

Private Sub Workbook_Open()
 bIsInTarget = Sheet2.Range("G1") = "In-play"
End Sub

Finally, add the following code to your worksheet

Private Sub Worksheet_Calculate()Application.EnableEvents = False
    If Range("G1").Value = "In-play" And bIsInTarget = False Then
           Range("U9").Value = Range("G9").Value
           Range("U11").Value = Range("G11").Value
           bIsInTarget = True
    Else
        bIsInTarget = False
    End If
    
Application.EnableEvents = True
End Sub

 

[breakbacktennis]

Hi CodeNinja, not a problem there is more to life that sorting my problems out lol. Iv tried everything uv typed above but still no luck unfortunately. Someone has mentioned that if i refer to the
destination cell being empty or not that will stop the values updating as the cell will have a value if that makes sense. Wouldnt know how to code that however

 

[CodeNinja]

Hi Breakbacktennis,
If the code above did not work, I need to know how you reference G1... is there a formula there? What is the formula?

Thanks,
CN.

 

[breakbacktennis]

Hi codeninja.... there is no formula in G1 as such. It is linked to a betting app which automatically displays G1 as in-play when an event starts. There is no formula in the cell just an automatic change by the betting app. Hope that makes sense

 

[CodeNinja]

How did you create that link? VBA? Data connection? Please explain...

 

[CodeNinja]

Actually, we can try something... Put in H1 (or some un-used cell) the formula "=G1" and then adjust the code to reference H1... That should work.

 

[breakbacktennis]

Hi code ninja i have just tested the above and still not quite working. The values appear again but change as the spreadsheet refreshes. A real nuisance this sheet is lol.

 

[CodeNinja]

Hi code ninja i have just tested the above and still not quite working. The values appear again but change as the spreadsheet refreshes. A real nuisance this sheet is lol.

Ok. First, please provide details on the connection... how it was created etc. Second, what do you mean by "The values appear again but change as the spreadsheet refreshes"? Is column U updating and then being overwritten by the connection? I'm not sure exactly what you mean...

Thanks,

CN.

 

[breakbacktennis]

Ok. First, please provide details on the connection... how it was created etc. Second, what do you mean by "The values appear again but change as the spreadsheet refreshes"? Is column U updating and then being overwritten by the connection? I'm not sure exactly what you mean...

Thanks,

CN.

The spreadsheet is connected to a betting app that displays odds. What I am trying to do is capture the odds when the event starts and keep them as a record. The odds are in cells G9 and G11. The spreadsheet will refresh every second as the betting app does. As this refresh happens the values I am looking for change. I want to capture only one set of values.

- Event starts signalled by cell G1 saying In-play
- I want to capture cells G9 and G11 at that point and have the values displayed in cells U9 and U11
-I want the macro to stop then even though the spreadsheet will keep refreshing with updated prices.

Hope this helps and thankyou once again CN