Run a macro only once

[LouisWood]

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

[#2]

@LouisWood 

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?

[#3]

@Hans VogelaarYes, AT1139 is normally empty and I want the function to run when another process puts text in it.

[#4]

@LouisWood 

You write "I want the function to run when another process puts text in it." What other process?

[#5]

The cell contains an IFS() formula that, when one of several conditions are met, makes the value of the cell equal to one of several strings of text.

I am sorry that I did not provide complete information - I appreciate your help.

[#6]

@LouisWood 

For example:

• Remove the formula - it won't work
• Change the PlayWAV function to a macro:

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

•  Right-click the sheet tab and select 'View Code' from the context menu.
• Copy the code listed below into the worksheet module:

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

[#7]

@Hans Vogelaar 

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).

[#8]

@LouisWood 

That's far too complicated for me, sorry.

[#9]

Thank you, I appreciate your time.

Here is a much simpler question: I can put a formula in a cell that calls VBA code that will play a WAV file. Is there a way to play the file only once instead of every time the sheet calculates?

I am open to any suggestions from anyone.
Thank you,
Louis