CODEWITHSUNDEEP
excelvbaexcel-formula
DMO
DMO

Reputation: 49

Change a VBA to run automatically or when moving tabs

I have the below VBA which only runs when the cell is updated. there is a formula that updates the cell but the VBA is not picking this up as a change. how do i get the VBA to run automatically so i dont have to tab inside each cell to make the VBA run. preferably fully automatic but if its easy can be a change of tab etc.

any help would be appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)    
     If Target.Column = 9 Or Target.Column = 13 Then 'Column I or column M
         If UCase(Target) = "YES" Then Target.Offset(0, -1).ClearContents
    End If
End Sub

Upvotes: 0

Views: 815

Answers (1)

Doug Coats
Doug Coats

Reputation: 7107

@DMO

Here, just tested and this works perfectly. And its somewhat fast

Public Sub UpdatedEIghtAndTwelve()
    Dim arr As Variant
    arr = ThisWorkbook.ActiveSheet.UsedRange.Formula
    Dim i As Long, j As Long, z As Long

    j = UBound(arr, 1)
    z = UBound(arr, 2)

    For i = LBound(arr, 1) To UBound(arr, 1)
        If arr(i, 9) = "YES" Then
            arr(i, 8) = vbNullString
        End If
        If arr(i, 13) = "YES" Then
            arr(i, 12) = vbNullString
        End If
    Next i

    With ThisWorkbook.ActiveSheet
        .Range(.Cells(1, 1), .Cells(j, z)).Value2 = arr
    End With

End Sub

I forgot to add that you need to put this in the worksheet Code Behind of the sheet(s) you want this run for

 Private Sub Worksheet_Activate()
     Call UpdatedEIghtAndTwelve
 End Sub

Upvotes: 1

Related Questions