how to auto run VBA code when cells are changed?

Question

I borrowed (OK stolen) the following code off another website.

Sub HideRowCellTextValue()
    StartRow = 14
    LastRow = 25
    iCol = 5
    For i = StartRow To LastRow
        If Cells(i, iCol).Value <> "0" Then
            Cells(i, iCol).EntireRow.Hidden = False
        Else
            Cells(i, iCol).EntireRow.Hidden = True
        End If
    Next i
End Sub

This code does everything I want it to do but I would like it to auto-run when a cells value is changed.

The cells are all linked to another worksheet within the same workbook.

when a cells value is 0 I want to hide the entire row that the cell is in.

This is probably what you are looking for: https://stackoverflow.com/questions/409434/automatically-execute-an-excel-macro-on-a-cell-change — DecimalTurn, Jun 21 '23 at 04:34
If the cells are "linked" using formulas then to respond to changes there you'd need to use the worksheet's Calculate event. — Tim Williams, Jun 21 '23 at 05:51

CLR · Answer 1 · 2023-06-21T08:45:27.543

You can use the following, inserted into the worksheet code area which will run whenever any changes are made anywhere on the worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    StartRow = 14
    LastRow = 25
    iCol = 5
    For i = StartRow To LastRow
        Me.Cells(i, iCol).EntireRow.Hidden = (Me.Cells(i, iCol).Value = "0")
    Next i
End Sub

If you'd like the code to only run when changes are made within your tested range, you could do this:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    StartRow = 14
    LastRow = 25
    iCol = 5
    
    With Me
        If Not Intersect(Target, .Range(.Cells(StartRow, iCol), .Cells(LastRow, iCol))) Is Nothing Then
    
            For i = StartRow To LastRow
                .Cells(i, iCol).EntireRow.Hidden = (.Cells(i, iCol).Value = "0")
            Next i
        
        End If
    End With
    
End Sub

Black cat · Answer 2 · 2023-06-21T08:32:41.550

0

Place your(?) code to the sheet's code pane which you want to modify. Rename the Sub to

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

or select it from the dropdowns of the codepane, and add only the body of the code into it. Create a reference to the Sheet, and add the object variable name before all of the Cells properties, something like this

Set ws = ActiveWorkbook.Worksheets("TheSheetName")

and for the properties

ws.Cells(row,column)

or (according to comment)

No reference needed, only

Me.Cells(row,column)

edited Jun 21 '23 at 08:32

answered Jun 21 '23 at 04:53

Black cat

1,056
1
2
11

It the code is inserted into the worksheet, then `Me` can be used to reference the worksheet being changed. e.g. `Me.Cells(row,column)` – CLR Jun 21 '23 at 07:42

how to auto run VBA code when cells are changed?

2 Answers2