how to auto run VBA code when cells are changed? [duplicate]

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: stackoverflow.com/questions/409434/… — DecimalTurn
– DecimalTurn, Commented Jun 21, 2023 at 4: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
– Tim Williams, Commented Jun 21, 2023 at 5:51

CLR · Accepted Answer · 2023-06-21 08:45:27Z

1

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

edited Jun 21, 2023 at 8:45

answered Jun 21, 2023 at 7:48

CLR

12.3k1 gold badge13 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Black cat · Accepted Answer · 2023-06-21 08:32:41Z

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, 2023 at 8:32

answered Jun 21, 2023 at 4:53

Black cat

7,6358 gold badges67 silver badges99 bronze badges

1 Comment

CLR Over a year ago

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)

Collectives™ on Stack Overflow

how to auto run VBA code when cells are changed? [duplicate]

2 Answers 2

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Linked

Related