Jul 13 2022 10:47 AM - edited Jul 13 2022 10:51 AM
Hello,
I want a macro to run when the value in a cell meets a certain criteria but I want it to only run once, not every second or two for as long as the condition is met. So in addition to checking the value of the target cell, I also check that the value in another cell is not "Alerted." In my macro, I set the value of that second cell to "Alerted" which should prevent the formula from calling the function again.
It works perfectly when run in the debugger, but when called from a sheet it fails on the line that should set the value of the second cell to "Alerted."
I have tried using
Application.EnableEvents = False
and
Application.Calculation = False
but it still fails. When I set a break point and then step through the code, when it gets to the line that should set the value execution jumps back to the first line in the macro as if it is called again (which is why I thought about turning automatic calculation off).
This is the function:
=IF(AND(AT1139<>"",AZ1139<>"Alerted"),PlayWAV(),"")
This is the macro (including some commented out lines that did not help):
Function PlayWAV()
Application.EnableEvents = False
'Application.Calculation = xlManual
'Application.Calculation = xlCalculationManual
Application.Calculation = XlCalculation.xlCalculationManual
Range("$AZ$1139").Value = "Alerted" 'This is the line that fails
'Application.Calculation = xlAutomatic
'Application.Calculation = xlCalculationAutomatic
Application.Calculation = XlCalculation.xlCalculationAutomatic
Application.EnableEvents = True
WAVFile = "C:\Users\Louis Wood\OneDrive\Documents\Media\Sounds\Custom\PriceAlert.wav"
'Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
End Function
Jul 13 2022 12:32 PM
A function in a cell formula cannot change the value of another cell.
Will the user change the value of AT1139 or does it contain a formula?
Jul 13 2022 12:36 PM
@Hans VogelaarYes, AT1139 is normally empty and I want the function to run when another process puts text in it.
Jul 13 2022 12:58 PM
You write "I want the function to run when another process puts text in it." What other process?
Jul 13 2022 01:07 PM
Jul 13 2022 01:31 PM
For example:
Sub PlayWAV()
Dim WAVFile As String
WAVFile = "C:\Users\Louis Wood\OneDrive\Documents\Media\Sounds\Custom\PriceAlert.wav"
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
End Sub
Private Sub Worksheet_Calculate()
If Range("AT1139").Value <> "" And Range("AZ1139").Value <> "Alerted" Then
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
PlayWAV
Range("AZ1139").Value = "Alerted"
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End If
End Sub
Jul 13 2022 06:58 PM
Thank you again for your help.
I just tried your suggestion and when the conditions are met nothing happens - I placed a break point and verified that the macro doesn't get called.
I guess some additional information is needed:
1) There are currently 3 worksheets in the same workbook that I want this to work with, so it would be better in a code module than in the sheet's code.
2) The cell function will be on at least 300 rows in the worksheet - I am referencing Range("AT1139") right now just to simplify troubleshooting. Once I figure out why it is not working specifically with Range("AT1139") I plan to replace that reference with Application.Caller.Address to get the cell that is calling the function, then use Offset(0, -1) to place the string "Alerted" into the cell one column to the left of the formula cell.
The following works:
Formula in cell BA1139:
=IF(AND(AT1139<>"",AZ1139<>"Alerted"),playWAV(),"")
In code module:
Function PlayWAV()
WAVFile = "C:\Users\Louis Wood\OneDrive\Documents\Media\Sounds\Custom\PriceAlert.wav"
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
End Function
The problem is that the sound is played about every second for as long as the conditions are met, so I just need a way to put the string "Alerted" into cell AZ1139 (or, more specifically, into the cell one column to the left of the formula cell).
Jul 14 2022 05:07 AM
That's far too complicated for me, sorry.
Jul 14 2022 05:46 AM