Vba Macro Help - Sheet Protection

DaveR

Board Regular
Joined
May 10, 2006
Messages
176
I have this code.

Code:
Sub Worksheet_change(ByVal Target As Range)

    ActiveSheet.Unprotect
    
    
    Dim i As Integer
    For i = 17 To 43 Step 2
        Sheet6.Rows(i).EntireRow.Hidden = Sheet5.Range("M" & i).Value = "Yes" 'Unmet needs
        Sheet6.Rows(i + 1).EntireRow.Hidden = Sheet5.Range("M" & i).Value = "Yes" 'Unmet need
        Sheet10.Rows(i + 17).EntireRow.Hidden = Sheet5.Range("M" & i).Value = "Yes" 'checklist
        Sheet10.Rows(i + 18).EntireRow.Hidden = Sheet5.Range("M" & i).Value = "Yes"   'Checklist
        Sheet6.Range("L" & i).ClearContents
        Sheet6.Range("N" & i).ClearContents
    Next i

ActiveSheet.protect

End Sub

However, if I protect sheet 6 (or indeed sheet 10) then the code crashes with error code 'Runtime error 1004 - Unable to set the Hidden property of the range class.

I need to unprotect sheet 6 & 10 and then protect them again when the loop has finished.


Help!
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Watch MrExcel Video

Forum statistics

Threads
1,108,979
Messages
5,526,024
Members
409,677
Latest member
ssh99

This Week's Hot Topics

Top