VBA to hide/unhide columns on range of protected worksheets if value in row 1 is "1"

[Jason44136]

Greetings experts...

I have a workbook with three summary sheets and 31 different sheets (1, 2, 3 …). On my "Setup" sheet, I have a command button that will have a macro assigned. I am trying to write the VBA to hide all columns on sheets 1-31 if the value in row 1 is "1". All sheets are protected, so I know we'd have to have an Unprotect and protect line in the VBA. I tried using some other suggestions from my other threads, but no luck. If the cells that trigger the 1 in row 1 change (and thus the 1 is no longer present)… this should update when the command button is pressed again. It does not need to update automatically. I should note that sheets 1-31 will always be identical with the row 1 value based on the setup sheet.

I would prefer the macro just works without having to use a command button at all, but having the button does not bother me.

 

[Logit]

When you say "all columns", do you really mean to leave Col #1 always viewed ?

 

[Logit]

Option Explicit

Sub ChkForOnes()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets
        If ws.Range("A1").Value = 1 Then
           Worksheets("Sheet1").Range("B:XFD").EntireColumn.Hidden = True
        Else
           Worksheets("Sheet1").Range("B:XFD").EntireColumn.Hidden = False
        End If
Next ws
End Sub
Option Explicit

Sub ChkForOnes()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Sheets
        If ws.Range("A1").Value = 1 Then
           Worksheets("Sheet1").Range("B:XFD").EntireColumn.Hidden = True
        Else
           Worksheets("Sheet1").Range("B:XFD").EntireColumn.Hidden = False
        End If
Next ws
End Sub

 

[Jason44136]

Thank you. By all columns, I mean any column that has the value of 1 in row 1. For more clarity, only columns E through R would potentially have a 1 in row 1 and thus need to be hidden.
The other concern I have about the suggest code is it looks as if it only checks sheet 1... but I need it to check sheets 1, 2, 3, 4 etc to 31. (31 total sheets)

 

[Logit]

Option Explicit

Sub chkForOnes()
Dim rng As Range, c As Range, ws As Worksheet

For Each ws In Worksheets
   
    For Each c In ws.Range("E1:R1")
        If c.Value = 1 Then
            c.EntireColumn.Hidden = True
        Else
            c.EntireColumn.Hidden = False
        End If
    Next c
Next ws
End Sub

 

[Jason44136]

Thank you. This works if the sheets are unprotected, but they will be protected sheets. I've tried adding some additional code to unprotect and then run macro then re-protect, but it is not working. Any other suggestions?

 

[Peter_SSs]

Are you saying that sheets 1-31 all have exactly the same columns with "1" in row 1 as the setup sheet?

What is the name of the setup sheet?

Are the "1" values in row 1 of that sheet the result of formulas or are they entered manually or by vba?

Do the columns in E:R that do not have a 1 have anything in row 1 or are those cells blank?

Can you confirm whether the 1 values in row 1 are numerical 1 values or text "1" values?

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Jason44136]

Thanks Peter -
The setup sheet is different and is called "Setup"
There are 4 sheets that will not be impacted by this code ("Setup", "Instructions", "Monthly" & "Associate Summary")
**These 4 sheets have a hidden row 1 that is blank**
Sheets 1-31 ("1", "2", "3", "4", etc) have nothing in row 1 except the value of "1" may populate in E1, F1, G1 -> R1 depending on values entered on the setup tab. Example: =If(Setup!B20=0,1,"") is the formula in E1.
The remainder of row 1 is blank. Row 1 is hidden.

I also updated my account details, thank you.

 

[Peter_SSs]

I also updated my account details

Great, thanks.

See if this would work (automatically) for you sufficiently. It should hide those 1 columns when the sheet is activated. Is that good enough?

This is Workbook event code. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Double click 'ThisWorkbook' in the project explorer pane at the left of the vba window.
3. Copy and Paste the code below into the main right hand pane that opens at step 2. (Adjust for your password in two places)
4. Close the Visual Basic window & test by selecting any of the 1-31 sheets (or any of the other sheets when nothing should happen)

Test in a copy of your workbook.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  With Sh
    Select Case .Name
      Case 1 To 31
        .Unprotect Password:="abc"
        With .Range("E1:R1")
          .EntireColumn.Hidden = False
          On Error Resume Next
          .SpecialCells(xlFormulas, xlNumbers).EntireColumn.Hidden = True
          On Error GoTo 0
        End With
        .Protect Password:="abc"
    End Select
  End With
End Sub

 

[Jason44136]

Thank you Peter. This works fantastic. Even better than I expected and super fast. Much much appreciated!!