Hiding/Unhiding a row in a Password Protected Spreadsheet

[Chris Waller]

Hi Everyone,

I am trying to find out if it is possible to hide/unhide a row on a password protected spreadsheet when the data changes in a cell?

I have a Spreadsheet that contains a lot of formulae. In order to prevent people messing with the formulae either inadvertently or maliciously I have protected all the Formulae, however, If cell F1 equals 2022/2023 I need row 71 to be visible, however, if the cell changes to any other value I need the row to be hidden. The row contains the extra banh holiday for the Queen's Platinum Jubilee, so after 2022/2023 this row is no longer required.

TIA

 

[mumps]

Place this macro in the code module for ThisWorkbook:

Private Sub Workbook_Open()
    With Sheets("Sheet1")
        .Unprotect
        If .Range("F1") = "2022/2023" Then
            .Rows(71).Hidden = False
        Else
            .Rows(71).Hidden = True
        End If
        .Protect
    End With
End Sub

Place this module in the code module for your worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("F1")) Is Nothing Then Exit Sub
    With Sheets("Sheet1")
        .Unprotect
        .Rows(71).Hidden = True
        .Protect
    End With
End Sub

Change the sheet name (in red) to suit your needs.

 

[Chris Waller]

Place this macro in the code module for ThisWorkbook:

Private Sub Workbook_Open()
    With Sheets("Sheet1")
        .Unprotect
        If .Range("F1") = "2022/2023" Then
            .Rows(71).Hidden = False
        Else
            .Rows(71).Hidden = True
        End If
        .Protect
    End With
End Sub

Place this module in the code module for your worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("F1")) Is Nothing Then Exit Sub
    With Sheets("Sheet1")
        .Unprotect
        .Rows(71).Hidden = True
        .Protect
    End With
End Sub

Change the sheet name (in red) to suit your needs.

Morning mumps,

Thanks for the code above, it worked a treat, however, I keep being prompted for the password. Whilst this is OK while I'm using it, this is for someone else and they will not have my password, so is there a way around this?

I would like to keep the password secret so is there a way to automate the whole process?

 

[maabadi]

Add Password after .Unprotect and .Protect

.Unprotect "password"


.protect "password"

AND
In the VBA main menu, click on Tools | VBAProject Properties and under the tab "Protection", Check the box "Lock project for viewing" and enter the Specific password to Hiding code from Viewing.

 

[Chris Waller]

Add Password after .Unprotect and .Protect

.Unprotect "password"


.protect "password"

AND
In the VBA main menu, click on Tools | VBAProject Properties and under the tab "Protection", Check the box "Lock project for viewing" and enter the Specific password to Hiding code from Viewing.

Maabadi,

Thanks for that. It worked a treat.

 

[Chris Waller]

Place this macro in the code module for ThisWorkbook:

Private Sub Workbook_Open()
    With Sheets("Sheet1")
        .Unprotect
        If .Range("F1") = "2022/2023" Then
            .Rows(71).Hidden = False
        Else
            .Rows(71).Hidden = True
        End If
        .Protect
    End With
End Sub

Place this module in the code module for your worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("F1")) Is Nothing Then Exit Sub
    With Sheets("Sheet1")
        .Unprotect
        .Rows(71).Hidden = True
        .Protect
    End With
End Sub

Change the sheet name (in red) to suit your needs.

Good Evening Mumps,

The code above was great and did what I needed it to do. I just have a query regarding the hiding and unhiding of row 71. I know your code will hide row 71 when I change the year to anything other than 2022/2023, however, if I then change back to 2022/2023 it does not then unhide row 71. Is this something that you could perhaps look at for me? TIA

 

[maabadi]

Please test this as worksheet change event:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("F1")) Is Nothing Then Exit Sub
    With Sheets("Sheet1")
        .Unprotect "password"
        If .Range("F1") = "2022/2023" Then
            .Rows(71).Hidden = False
         Else
            .Rows(71).Hidden = True
        End If
       .Protect "password"
    End With
End Sub

 

[Peter_SSs]

What about just this?

Private Sub Worksheet_Change(ByVal Target As Range)
  ActiveSheet.Unprotect Password:="password"
  Rows(71).Hidden = Range("F1").Value <> "2022/2023"
  ActiveSheet.Protect Password:="password"
End Sub

 

[Chris Waller]

Please test this as worksheet change event:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("F1")) Is Nothing Then Exit Sub
    With Sheets("Sheet1")
        .Unprotect "password"
        If .Range("F1") = "2022/2023" Then
            .Rows(71).Hidden = False
         Else
            .Rows(71).Hidden = True
        End If
       .Protect "password"
    End With
End Sub

Maabadi,

That's twice you have come to my rescue and another fantastic job. Brilliant I cannot thank you enough.

 

[maabadi]

Please test also Peter solution, it is smaller and work perfect.