How do you actually use Worksheet_Change event?

[Nhouse1234]

Can anyone help!??

Trying to automate a ditching strategy using excel.

I need odds to be captured & copied form one part of my workbook to another. The copy is triggered when match timer hits a certain point. - which changes a cell value form 0 to 1.

I currently use worksheet_calculate to fire on the formula calculation, then use an IF statement to check if the value is 1. If it is - calls the copy sub.

But the problem is the worksheet is constantly firing due to the number of other formulas I have.

I thought a simple fix would be to use the worksheet_change event - like many people seem to in this forum - but that doesn't seem to work with formula changes - only manual inputs?

Can anyone explain how to use it in automation?

[ODPaul82]

From something I used a while ago, cant be arsed to shorten it or explain it but works fine for me
EMPTY_STRING is a global variable which is simply
Public Const EMPTY_STRING As String = ""

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    If IsNumeric(Me.Range("F4")) Then
        gPriceUpdate "BA Data - 5", Me.Range("F4"), Me
    End If
End Sub

Code: Select all

Public Sub gPriceUpdate(ByVal pstrSheet As String, _
                        ByVal pdteCountdown As Date, _
                        ByRef pobjParent As Excel.Worksheet)

    Dim objWs As Excel.Worksheet

    Dim lngRow As Long
    Dim n As Long
    
    Const LTP_ROW As Long = 15
    
    Set objWs = ThisWorkbook.Worksheets(pstrSheet)
    
    ' If SET_SVs are set and outside of time range then clear. The first row will have a value populated so can check that
    If pobjParent.Cells(9, 12) <> EMPTY_STRING Then
    
        If pdteCountdown < TimeSerial(0, 2, 50) Or pdteCountdown > TimeSerial(0, 3, 0) Then
            mClearRuleCells pobjParent
        End If
    
    End If

    ' If time is in range then set the values
    If pdteCountdown > TimeSerial(0, 2, 55) And pdteCountdown <= TimeSerial(0, 3, 0) Then
    
        If pobjParent.Cells(9, 12) = EMPTY_STRING Then
            mTriggerBet pobjParent
        End If
        
    End If

    ' Time is in capture range, find the associated time and make sure data is empty
    If pdteCountdown <= TimeSerial(0, 10, 10) And pdteCountdown >= TimeSerial(0, 1, 0) Then

        lngRow = objWs.Range("AF2").Value
        If lngRow > 0 Then
            ' Empty - populate all selections with the LTP
            If objWs.Cells(lngRow, 2) = EMPTY_STRING Then
            
                Application.EnableEvents = False
            
                For n = 2 To 29 Step 3
                    If Not IsError(objWs.Cells(LTP_ROW, n)) Then
                        objWs.Cells(lngRow, n) = objWs.Cells(LTP_ROW, n)
                    End If
                Next n

                Application.EnableEvents = True
        
            End If
        
        End If

    End If
    
    
    ' Does the associated data worksheet need to have its data cleaned
    If (pdteCountdown < TimeSerial(0, 1, 0) And Application.WorksheetFunction.CountA(objWs.Range("B18:B628")) > 0) Or _
        (pdteCountdown > TimeSerial(0, 10, 11) And Application.WorksheetFunction.CountA(objWs.Range("B18:B628")) > 0) Then

        Application.EnableEvents = False
        For n = 2 To 29 Step 3
            With objWs
                .Range(.Cells(18, n), .Cells(628, n)).ClearContents
            End With
        Next n
        Application.EnableEvents = True

    End If
    
End Sub