How to Insert Workbook_SheetCalculate Event

[HJA14]

Dear all,

I have written the following code:

Workbook_SheetCalculate(ByVal Sh As Worksheet)
    Dim myRange          As Range
    Dim product          As Double
    Dim n                As Long


    Set myRange = Sh.Range("N1:N50,AA1:AA50,AN1:AN50,BA1:BA50,BN1:BN50,CA1:CA50")
    Productroduct = Sh.Range("A1").Value * Sh.Range("A2").Value
    For n = 1 To myRange.Count
        If myRange(n) > Product Then
            MsgBox "Ticker: " & Sh.Name & ". Today's abnormal volume in the  " & myRange(n).Offset(, -3).Value & " serie is " & myRange(n) & " contracts."
			myRange(n).ColorIndex = 6
			Sh.Select
        End If
    Next n
End Sub

However, it does not run. If try to insert a module and press F5, the Macro box appears. And I am unable to press Run. Maybe I am missing something, and I would love to know what exactly!!

Thank you

 

[Sektor]

First:

[COLOR=#ff0000][B]Private Sub [/B][/COLOR]Workbook_SheetCalculate(ByVal Sh As [COLOR=#ff0000][B]Object[/B][/COLOR])

Second, this procedure must be located in ThisWorkbook module.

 

[Martvg]

That is because this code runs when an event occurs, , macros based on events don't show up when you press F5 because they should be triggered by an event in the application itself.

In this case the code runs each time a calculation changes on a certain worksheet.

For further explanation:
Events In Excel VBA

Best regards,

 

[HJA14]

Thank you for the reply. I have included the module in "ThisWorkbook".

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
   With Sh
    Dim myRange          As Range
    Dim product          As Double
    Dim n                As Long
    Set myRange = Sh.Range("N1:N50,AA1:AA50,AN1:AN50,BA1:BA50,BN1:BN50,CA1:CA50")
    Set product = 0.001 * Sh.Range("A3").Value * Sh.Range("A5").Value
    For n = 1 To myRange.Count
        If myRange(n) > product Then
            MsgBox "Ticker: " & Sh.Name & ". Today's abnormal volume in the  " & myRange(n).Offset(, -3).Value & " serie is " & myRange(n) & " contracts."
            myRange(n).ColorIndex = 9
            Sh.Select
        End If
    Next n
End Sub

Is it possible that you take a look at my code and give feedback?

 

[Sektor]

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    
    Dim myRange As Range
    Dim product As Double
    Dim n As Long
    Dim rng As Range, rngArea As Range
    
    With Sh
        Set myRange = .Range("N1:N50,AA1:AA50,AN1:AN50,BA1:BA50,BN1:BN50,CA1:CA50")
        Set product = 0.001 * .Range("A3").Value * .Range("A5").Value
        For Each rngArea In myRange.Areas
            For Each cell In rngArea
                If cell > product Then
                    MsgBox "Ticker: " & .Name & ". Today's abnormal volume in the  " & cell.Offset(, -3).Value & " serie is " & cell & " contracts."
                    cell.ColorIndex = 9
                    'Sh.Select - why select in a loop?
                End If
            Next
        Next
    End With
    
End Sub

End Sub

 

[HJA14]

Thank you Sektor!

 

[Sektor]

You're welcome!