How to run a macro automatically based on a cell value change?

smjllo
NewLounger
Posts: 5
Joined: 31 Jul 2017, 20:16

How to run a macro automatically based on a cell value change?

Post by smjllo »

I have a macro that runs great except that I have to manually run it. The macro will hide all the columns that are not "true" based on a value in a cell. Here is the macro I have:

Sub HideCols()
Dim cell As Range
For Each cell In ActiveWorkbook.ActiveSheet.Rows("2").Cells
If cell.Value = "False" Then
cell.EntireColumn.Hidden = True
End If
Next cell
End Sub


How do I change it to make it run automatically based on changing the value of the cell the macro is based on?

User avatar
Jay Freedman
Microsoft MVP
Posts: 1185
Joined: 24 May 2013, 15:33
Location: Warminster, PA

Re: How to run a macro automatically based on a cell value change?

Post by Jay Freedman »

You'll need to change your macro into a worksheet-level event handler. Take a look at https://www.mrexcel.com/excel-tips/crea ... ler-macro/. The macro should be made into a Worksheet_Change handler so it will run when any cell is edited.

Because the handler does run for any cell, your code should start with an IF statement that immediately exits the handler if the current cell isn't in row 2.