TL;DR Does anyone have a VBA snippet to trigger some more code *just once* that works effectively?!
Hoping you can help; I've been trying to get some automation to trigger based on the time before the event start. I'm going to preface this message with the disclaimer that I do know a reasonable amount about coding but VBA (and Excel really) is entirely new to me! Into the worm can...
I started by using the Worksheet_Change() method and then limiting that to the countdown value cell, but this didn't seem to work and from my research online it looked like a change is not registered if it's coming from an external source. I.e Bet Angel, not me as a user updating the cell manually.
So I then moved on to the Worksheet_Calculate() method. As this does trigger every time the Worksheet updates, and you can't restrict it to a cell, and I want the worksheet to update quickly, I copied the countdown to a cell on Sheet3. The formula for the cell in Sheet3 for the countdown is this:
Code: Select all
=TEXT('Bet Angel'!F4, "hh:mm:ss")
The VBA looks like this:
Code: Select all
Private Sub Worksheet_Calculate()
Dim countdown As String
Dim timeToRunCode As String
countdown = Range("B3")
timeToRunCode = "00:00:45"
If countdown = timeToRunCode Then
Debug.Print "Running Python"
RunPython "import my_code; my_code.run_my_code()"
Sleep (1500)
End If
End Sub
(Sleep was a function I stuck in a module to try and see if I could get it to sleep until the If wouldn't evaluate as True, but that doesn't seem to be working either.)
This is the code in a module for Sleep, grabbed off the internet:
Code: Select all
Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
Many Thanks, Rich