Automatically Run Macros Based on Values in a Row

[JHCali]

Greetings,

I currently have two macros in my workbook, one to Hide Columns based on values in row 5, and the other to Unhide Columns based on values in row 5.

Sub Hide_Columns()
'
' Hide_Columns Macro
'
Dim c As Range

    For Each c In Rows("5:5").Cells
        If c.Value = "Hide" Then
            c.EntireColumn.Hidden = True
   
            'You can change the property above to False
            'to unhide the columns.
        End If
    Next c

'
End Sub

Sub Unhide_Columns()
'
' Hide_Columns Macro
'
Dim c As Range

    For Each c In Rows("5:5").Cells
        If c.Value = "Unhide" Then
            c.EntireColumn.Hidden = False
   
            'You can change the property above to False
            'to unhide the columns.
        End If
    Next c

'
End Sub

I am trying to have these macros run automatically based on dynamic changes in values in row 5. In row 5, I have formulas that populate the cells with "Hide" and "Unhide". As the values in row 5 for specific cells change from "Hide" to "Unhide", I'd like for the whole columns to automatically hide or be unhidden. To accomplish this, I am trying the following code, but keep getting a Type Mismatch error.

Sub worksheet_change(ByVal target As Range)
Set target = Range("A5:XFD5")
If target.Value = "Hide" Then
 Call Hide_Columns
End If
If target.Value = "Unhide" Then
Call Unhide_Columns
End If
End Sub

I would really appreciate help understand what I am doing wrong and fixing the code.

Thanks so much!

 

[ACommandLineKindaGuy]

Assuming that row 5 only contains cells with text "Hide" and "Unhide", the first thing you should do is combine the Hide and Unhide macros

Sub HideOrUnhideColumns()

Dim c As Range
    For Each c In Rows("5:5").Cells
        If c.Value = "Hide" Then
            c.EntireColumn.Hidden = (c.Value="Hide")
        End If
    Next c
End Sub

Notice the term (c.Value="Hide") evaluates only to True or False so columns get hidden if True and unhidden if false

Looking at the next part of your question

 

[ACommandLineKindaGuy]

If you only want to trap dynamic changes to row 5, where such when ONE change fires it will toggle ONE cell's value between "hide" and "unhide", then
there's no need to cycle through the value of row 5 in every column:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 5 Then
     Target.EntireColumn.Hidden = (Target.Value = "Hide")
End If

End Sub

If however, the values in row 5 are unpredictably tied in some fashion to values in other cells say A1 which result in a value of either "Hide" or "Unhide" to SOME or ALL cells in row 5, THEN you would have to test ALL cells in row 5

Private Sub Worksheet_Change(ByVal Target As Range)

dim c as Range
For each c In Rows("5:5").Cells
     c.EntireColumn.Hidden = (c.Value = "Hide")
Next c

End Sub

 

[johnnyL]

Worksheet_Change does not trigger from a cell value change as a result of a formula.

 

[JHCali]

If you only want to trap dynamic changes to row 5, where such when ONE change fires it will toggle ONE cell's value between "hide" and "unhide", then
there's no need to cycle through the value of row 5 in every column:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 5 Then
     Target.EntireColumn.Hidden = (Target.Value = "Hide")
End If

End Sub

If however, the values in row 5 are unpredictably tied in some fashion to values in other cells say A1 which result in a value of either "Hide" or "Unhide" to SOME or ALL cells in row 5, THEN you would have to test ALL cells in row 5

Private Sub Worksheet_Change(ByVal Target As Range)

dim c as Range
For each c In Rows("5:5").Cells
     c.EntireColumn.Hidden = (c.Value = "Hide")
Next c

End Sub

Thank you so much! That last bit of code was key. It completely negated the need for a separate macro. Also, having the formula search the entire row was causing it to run slowly. So I modified the formula to look at a finite range of cells in row 5 and now the cells hide and unhide as needed instantly.

 

[johnnyL]

Worksheet_Change does not trigger from a cell value change as a result of a formula.

Worksheet_Change

Hmm, I think Microsoft needs to edit that page.

It probably should say "a 'Target' is not set when a recalculation occurs."