VBA: Worksheet_Calculate() Not being called

[SLL]

All-

I have read plenty about how people feel that their
worksheet_calculate gets called too often, but how about my situation:
It never gets called, and I am not so sure why.

I tried recalcing the sheet but the event never seems to get called.
when I open the file though, it does get called. I am very confused.
Any help woudl be appreciated. Thanks.

Here is the code for my worksheet:

Option Explicit

Dim StartProg As Boolean

Dim i As Integer

Private Declare Function PlaySound Lib "winmm.dll" _
Alias "PlaySoundA" (ByVal lpszName As String, _
ByVal hModule As Long, ByVal dwFlags As Long) As Long

Const SND_SYNC = &H0
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000

Sub PlayWAV(File As String)

Call PlaySound(File, 0&, SND_ASYNC Or SND_FILENAME)

End Sub

Private Sub ResetButton_Click()
Worksheets("Front").Range("B3") = ""
End Sub

Private Sub StopButton_Click()

Application.EnableEvents = False
Call PlayWAV("C:\sounds\Down.wav")
Worksheets("Front").Range("B3") = ""
Worksheets("Feeds").Range("G1").Value = "OFF"
StartProg = False
Application.EnableEvents = True

End Sub

Private Sub StartButton_Click()

Call PlayWAV("C:\sounds\Up.wav")
StartProg = True

End Sub


Sub Worksheet_Calculate()

Application.EnableEvents = False

If StartProg = True Then
Worksheets("Feeds").Range("G1").Value = "ON"
Call PlayWAV("C:\sounds\drumroll.wav")

'Instructions, calcuations, etc

Application.EnableEvents = True

'Instructions, calcuations, etc

End If

End Sub

 

[Guest]

SLL,

Your code turns off events at the start of the calculate event. But, if
StartProg <> True, it never gets turned back on. So try moving this line

Application.EnableEvents = True

outside of the If statement.


Sub Worksheet_Calculate()

Application.EnableEvents = False

If StartProg = True Then
Worksheets("Feeds").Range("G1").Value = "ON"
Call PlayWAV("C:\sounds\drumroll.wav")

'Instructions, calcuations, etc

'Instructions, calcuations, etc
End If

Application.EnableEvents = True

End Sub

 

[SLL]

SLL,

Your code turns off events at the start of the calculate event. But, if
StartProg <> True, it never gets turned back on. So try moving this line

Application.EnableEvents = True

outside of the If statement.

Sub Worksheet_Calculate()

Application.EnableEvents = False

If StartProg = True Then
Worksheets("Feeds").Range("G1").Value = "ON"
Call PlayWAV("C:\sounds\drumroll.wav")

'Instructions, calcuations, etc

'Instructions, calcuations, etc
End If

Application.EnableEvents = True

End Sub

--
Hope that helps.

Vergel Adriano































- Show quoted text -

Thanks...Looked like I was staring at this a bit too long.