Text to speech condition based on cell value

[chrisgarcia78]

Hello, I have the following sheet code to pronounce a sentence if a condition is met however I have an excel Query that refreshes data every 2 minutes so every two minutes the sentence is pronounced and I would like that to happen only when the number the cell changes to 1 one time.

Private Sub Worksheet_Calculate()
If Range("A1").Value = 1 Then
Application.Speech.Speak "Congratulation!! You reached your goal!"
End If
End Sub

 

[Joe4]

One way is to store a counter in an unused portion of your sheet, i.e.

Private Sub Worksheet_Calculate()
If Range("A1").Value = 1 Then
    Range("Z1").Value = Range("Z1").Value + 1
    If Range("Z1").Value = 1 Then
        Application.Speech.Speak "Congratulation!! You reached your goal!"
    End If
End If
End Sub