Forum Discussion

LouisWood's avatar
LouisWood
Copper Contributor
Jul 13, 2022

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...
Macros and VBA
LouisWood's avatar
LouisWood
Copper Contributor
Jul 13, 2022

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

 

HansVogelaar's avatar
HansVogelaar
MVP
Jul 13, 2022

LouisWood 

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

  • LouisWood's avatar
    LouisWood
    Copper Contributor
    Jul 13, 2022
    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.
    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Jul 13, 2022

      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
      • LouisWood's avatar
        LouisWood
        Copper Contributor
        Jul 14, 2022

        HansVogelaar 

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

         

         

Resources