VBA to unprotect sheet, hide/unhide rows based on column A value=1, then protect sheet for multiple sheets

[Jason44136]

Greetings. I have a schedule tool created and struggling to find the right code. We have a sheet named "Associates" and will have a button on this sheet to link a macro module to update a number of other sheets. There are 7 protected sheets, let's just say "Sheet 1", "Sheet 2", "Sheet 3" etc for purpose of this thread. Each of these has Column A that pulls a value (1 or 2 - or blank) from the Associates tab. Looking to run this macro by clicking a button on "Associates" that will"

1. Unlprotect the specific 7 sheets
2. Hide/Unhide entire row IF value in Column A = 1
3. Protect the sheet again

Then if "Associate" tab is changed, the button would be clicked again to update the 7 other sheets again.

P.S. I know how to assign the module to the button, just struggling with the actual code to get 1-3 above executed.

 

[Lukasier]

Sub Okey()
Dim Sheets(1 To 7) As String
Dim i As Integer
Dim SheetToWork As Worksheet
Dim Cell As Range

Sheets(1) = "Sheet1"
Sheets(2) = "Sheet2"
'....
Sheets(7) = "Sheet7"


For i = 1 To 7
Set SheetToWork = Worksheets(Sheets(i))
SheetToWork.Unprotect ("Password")

    For Each Cell In SheetToWork.Range("A1", "A" & SheetToWork.Range("A1").End(xlDown).Row)
        If Cell.Value = 1 Then
            If Cell.EntireRow.Hidden = True Then
            Cell.EntireRow.Hidden = False
            Else
            Cell.EntireRow.Hidden = True
            End If
        End If
    Next Cell
SheetToWork.Protect ("Password")
Next i
End Sub

 

[Jason44136]

Thank you. Unfortunately none of the rows with 1 in the A column were hidden when I tested the macro. It didnt "error out" but also did not hide the rows that should have been hidden.

 

[Lukasier]

can I take a look at your range?

 

[Fluff]

How about

Sub Jason()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array("Sheet1", "Sheet2", "Sheet3")
   For i = 0 To UBound(Ary)
      With Sheets(Ary(i))
         .Unprotect "Password"
         .Range("A1").AutoFilter 1, "1"
         .Protect "Password"
      End With
   Next i
End Sub

 

[Lukasier]

That is actually clever, but it does not hide rows per say, does it?

 

[Jason44136]

Lukasier - Here is the range captured from one of the sheets. FYI, i only have sheets 1 & 2 ready for this. I wanted to test it out first before prepping the other 5 sheets. I thus changed your original code to 1 to 2 instead of 1 to 7 in both spots.

State College test of Graduate Hotels Universal Scheduling Template 4.15.20.xlsm
	C	D
11
Front Office Schedule

Named Ranges
Name	Refers To	Cells
Wages	=FOWageReference!$A$1:$C$107	C11:D11

Not sure i did the Capture Range function of the XL2BB add-in correctly....?

 

[Fluff]

Yes it does.

 

[Jason44136]

Fluff- thank you. I tried this one, but it works opposite. I want the rows with "1" in column A to be hidded. This code hides everything BUT the rows with 1 in A.

 

[Fluff]

OOps, got that wrong, it should be

.Range("A1").AutoFilter 1, "<>1"