VBA Hide/Unhide columns based on ROW value

[Fraser101]

Hi,

I have a row A3:AZ3 where I've marked certain cells Y for the columns that I'd like to hide/unhide. I've marked cells as N that I don't want to hide.

I think the tricky part is that it will eventually be a button so that the columns will hide/unhide.

I've tried using the macro recorder, help and these pages but I can't seem to get what I need.

This is what I've got so far (took from another thread) but I stress I don't know if its even close or will make any sense.


Sub HideUnhideColumns()
If Range("A3:AZ3") = "Y" Then
Range("A3:AZ3").Rows.Hidden = True
Range("A3:AZ3").Rows.Hidden = False

End If
If Range("A3:AZ3") = "N" Then
Range("A3:AZ3").Rows.Hidden = False
Range("A3:AZ3").Rows.Hidden = True

End If
End Sub

PLEASE HELP

 

[mikerickson]

If you want to rename your tabs, just use the code names of the sheets.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim oneSheet As Worksheet
    Dim precedentCells As Range
    Set precedentCells = Worksheets("Tab1").Range("A1,B3,C2")

    If Sh.Name = Sheet1.Name Then
        If Not Application.Intersect(Target, precedentCells) Is Nothing Then
    
            For Each oneSheet In Array(Sheet2, Sheet3, Sheet4)
                With oneSheet
                    .Range("S:AA").EntireColumn.Hidden = False
                    Select Case Val(CStr(.Range("N1").Value))
                        Case 1
                            .Range("S:AA").EntireColumn.Hidden = True
                        Case 2
                            .Range("V:AA").EntireColumn.Hidden = True
                        Case 3
                            .Range("Y:AA").EntireColumn.Hidden = True
                    End Select
                End With
            Next oneSheet
        End If
    End If
End Sub

 

[johnlucasprice]

Well, right now tab 2 is actually Sheet7, but Sheet8 to Sheet 37 are completely different content. So if I copy Sheet 7 or add some other content the subsequent sheets may or may not be in sequential order. That's why I want to bury it in the worksheet (tab 2).

Is there a simple way to use the code in tab 2 below that works great (when I click in the cell even though it's a formula), but change from using $N$1 in tab 2 to using $B$6 in tab 1.

How do I change: If Target.Address = "$N$1" <---cell in tab 2
To: If Target.Address = "'ReportSetup!$B$6" <---cell in tab 1
(and make it work)


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$N$1" Then
If LCase(Target.Value) = 4 Then
Columns("R:Z").EntireColumn.Hidden = False
ElseIf LCase(Target.Value) = 3 Then
Columns("R:W").EntireColumn.Hidden = False
Columns("X:Z").EntireColumn.Hidden = True
ElseIf LCase(Target.Value) = 2 Then
Columns("R:T").EntireColumn.Hidden = False
Columns("U:Z").EntireColumn.Hidden = True
Else
Columns("R:Z").EntireColumn.Hidden = True
End If
End If
End Sub

Sorry, I know it's probably frustrating to deal with idiots! But I appreciate your help.

 

[mikerickson]

"Is there a simple way to use the code in tab 2 below that works great (when I click in the cell even though it's a formula), but change from using $N$1 in tab 2 to using $B$6 in tab 1."

No there isn't. A cell containing a formula will change its value (as the result of the formula changing) without triggering a Change event.
In a Worksheet_Change event, one could test "is the key cell (with a formula) one of the Dependent Cells of Target.". But since your situation has all the precedents of the key cells off sheet, one would do that in the Workbook_SheetChange event.

Which is roughly what the code I posted does:

If Sh.Name = Sheet1.Name Then
        If Not Application.Intersect(Target, precedentCells) Is Nothing Then

"Is the precedent cell(s) for all of those key cells (the N1 cells) what changed?"

If so, it loops through the sheets un/hiding cells depending on the value in that sheet's N1.