VBA Script run on Worksheet change

John554 0
Viewed 45

I am having trouble getting a Excel VBA script to run anytime a change is made to the worksheet. I have a Worksheet_Change method but it doesn't seem to activate when a change is made to the worksheet.

Sub Calculate1(NUM As Integer)
    Dim EntryPrice As Double
    Dim ExitPrice As Double
    Dim Invested As Double
    Dim PL As Double
    Dim Margin As Double
    Dim InvestPercentage As Double
    
    If (Cells(NUM, 6).Value = "L" And IsNumeric(Cells(NUM, 7).Value) And IsNumeric(Cells(NUM, 8).Value) And IsNumeric(Cells(NUM, 10).Value)) Then
        EntryPrice = Cells(NUM, 7)
        ExitPrice = Cells(NUM, 8)
        Invested = Cells(NUM, 10)
        
        InvestPercentage = (Invested / EntryPrice) * 100
        PL = ((ExitPrice / 100) * InvestPercentage) - Invested
        Margin = PL / Invested
        
        If (PL > 0) Then
            Cells(NUM, 11).Font.Color = RGB(255, 255, 255)
            Cells(NUM, 11).Interior.ColorIndex = 10
            Cells(NUM, 12).Font.Color = RGB(255, 255, 255)
            Cells(NUM, 12).Interior.ColorIndex = 10
        Else
            Cells(NUM, 11).Font.Color = RGB(255, 255, 255)
            Cells(NUM, 11).Interior.ColorIndex = 3
            Cells(NUM, 12).Font.Color = RGB(255, 255, 255)
            Cells(NUM, 12).Interior.ColorIndex = 3
        End If

        Cells(NUM, 11).Value = PL
        Cells(NUM, 12).Value = Margin
    ElseIf (Cells(NUM, 6).Value = "S" And IsNumeric(Cells(NUM, 7).Value) And IsNumeric(Cells(NUM, 8).Value) And IsNumeric(Cells(NUM, 10).Value)) Then
        EntryPrice = Cells(NUM, 7)
        ExitPrice = Cells(NUM, 8)
        Invested = Cells(NUM, 10)
        
        InvestPercentage = (Invested / EntryPrice) * 100
        PL = ((ExitPrice / 100) * InvestPercentage) - Invested
        Margin = PL / Invested
        
        Cells(NUM, 11).Value = PL
        If (PL < 0) Then
            Cells(NUM, 11).Font.Color = RGB(255, 255, 255)
            Cells(NUM, 11).Interior.ColorIndex = 10
            Cells(NUM, 12).Font.Color = RGB(255, 255, 255)
            Cells(NUM, 12).Interior.ColorIndex = 10
            PL = -Abs(PL)
            Margin = -Abs(Margin)
        Else
            Cells(NUM, 11).Font.Color = RGB(255, 255, 255)
            Cells(NUM, 11).Interior.ColorIndex = 3
            Cells(NUM, 12).Font.Color = RGB(255, 255, 255)
            Cells(NUM, 12).Interior.ColorIndex = 3
            PL = Abs(PL)
            Margin = Abs(Margin)
        End If
        
        Cells(NUM, 11).Value = PL
        Cells(NUM, 12).Value = Margin
    End If
End Sub
    
Sub CalculatePL()
    For i = 3 To Sheet1.UsedRange.Rows.Count + 1
        Calculate1 (i)
    Next
End Sub

Private Sub Worksheet_Activate()
    For i = 3 To Sheet1.UsedRange.Rows.Count + 1
        Calculate1 (i)
    Next
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    For i = 3 To Sheet1.UsedRange.Rows.Count + 1
        Calculate1 (i)
    Next
End Sub

Am I implementing this completely wrong or is there something I need to change to get this method to activate?

Edit: This code is in the module file that is associated with sheet1.enter image description here

I have also changed all of the references of Calculate to Calculate1 however the Worksheet_Change method is still not firing when the worksheet is changed.

vba excel
2 Answers

Calculate is a keyword in Excel VBA. Try changing all references to Calculate1, to see if your sub routine is called as expected.

Also, your Worksheet_[event] subs must reside in the code module of the worksheet you are "changing" ...

exception0

Moving the Worksheet_Change and Worksheet_Activate methods out of Module1 and into Sheet1 solved this issue for me.

Additionally I had to Application.EnableEvents = False and then set it as true again to stop Worksheet_Change entering an Infinite loop.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    For i = 3 To Sheet1.UsedRange.Rows.Count + 1
        Calculate1 (i)
    Next
    Application.EnableEvents = True
End Sub
John5540
Related