Forum Discussion
Run a macro only once
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...
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?
HansVogelaarYes, AT1139 is normally empty and I want the function to run when another process puts text in it.
You write "I want the function to run when another process puts text in it." What other process?
- 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.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