Absurd Bug with Protected Cells

[legolas97]

Hi I have locked/protected a couple of cells, while simultaneously running some VBA code to hide those cells on a conditional basis.

The protected cells cannot be hidden. I have to unprotect the sheet in order to hide the cells. Please help with the same.

Also I have a cell B2, who's input affects cell B3 (protected cell). I'm not allowed to make changes to B2, it says runtime error 1004 if i do so. Please help with the same.
I'm attaching a picture of the code as well as the excel file. The fields in green are to be edited, the fields in white are to be blank.

 

[Coding4Fun]

Not ideal but a means of managing this would be to password protect the VBA module and then unlock/lock the workbook before and after your VBA code runs.

Sub Macro1()
'
' Macro1 Macro
'

'

    ' Unlock workbook
    ActiveWorkbook.Unprotect "Password"
  
    ' Do some stuff in here
  
    ' Lock Workbook
    ActiveWorkbook.Protect "Password"

  
    ' Unlock Sheet
    ActiveSheet.Unprotect "Passwordsheet"

  
    ' Do some stuff in here
  
     ' Lock Sheet
    ActiveSheet.Protect "Passwordsheet"

  
End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

Your title says there is a bug but I haven't seen you identify one in your post. The point of having a sheet protected is stop certain things happening.
Having said that, when you are protecting a sheet you can allow certain things to happen. Have you investigated these options.

@Coding4Fun
Are not these the wrong way around?

 

[legolas97]

Hi thanks for your reply. I figured a partial fix, would what you suggested resolve the other half?
So I figured that turning on autofilter will allow for values in cells B2 to be changed for instance resulting in change in B3 even when B3 is locked.

However with the conditionally hiding cells, its not happening when cell is protected.
Here's a picture of the same below.

As you can see in my OP, if I set B11 to yes, I want row 13 to hide. If i set it to No, I want row 12 to hide. I just want that to be possible when I protect B12 and B13. Please let me know if your solution above will allow me to do this.

 

[legolas97]

Welcome to the MrExcel board!

Your title says there is a bug but I haven't seen you identify one in your post. The point of having a sheet protected is stop certain things happening.
Having said that, when you are protecting a sheet you can allow certain things to happen. Have you investigated these options.

View attachment 65376

@Coding4Fun
Are not these the wrong way around?

View attachment 65377

Hi Peter! Yes thank you! I applied the autofilter button, it allows me to make changes in B2, and allows for B3 to change while it remains protected/uneditable.

However, I'm not able to hide B12 and B13 conditionally using the code in the OP, when the cells are protected. Is there another box I need to check or some code I need to modify?

 

[Coding4Fun]

Welcome to the MrExcel board!

Your title says there is a bug but I haven't seen you identify one in your post. The point of having a sheet protected is stop certain things happening.
Having said that, when you are protecting a sheet you can allow certain things to happen. Have you investigated these options.

View attachment 65376

@Coding4Fun
Are not these the wrong way around?

View attachment 65377

I was giving an example for unlocking both the workbook and the sheet, they are in the correct order, you must have caught your screenshot prior to edit

 

[Coding4Fun]

Hi Peter! Yes thank you! I applied the autofilter button, it allows me to make changes in B2, and allows for B3 to change while it remains protected/uneditable.

However, I'm not able to hide B12 and B13 conditionally using the code in the OP, when the cells are protected. Is there another box I need to check or some code I need to modify?

Maybe try applying the unlock and lock code to on worksheet change?

It sounds like you have your sheet protected but want to be able to filter the sheet which is being prevented by the sheet being protected..

 

[legolas97]

Maybe try applying the unlock and lock code to on worksheet change?

It sounds like you have your sheet protected but want to be able to filter the sheet which is being prevented by the sheet being protected..

I'm able to filter B2 values while the sheet remains protected, allowing for B3 to change.

Only B11 because of the conditional hiding code in OP.

 

[legolas97]

Maybe try applying the unlock and lock code to on worksheet change?

It sounds like you have your sheet protected but want to be able to filter the sheet which is being prevented by the sheet being protected..

I've not locked the cells in green.

 

[Coding4Fun]

I've not locked the cells in green.

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Unlock workbook
    ActiveWorkbook.Unprotect "Password"
 
    ActiveSheet.Activate
    If Not Application.Intersect(Range("B11"), Range(Target.Address)) Is Nothing Then
    Select Case Target.Value
        Case Is = "Yes/No": Rows("1:80").EntireRow.Hidden = False
                            Rows("7:18").EntireRow.Hidden = False
        Case Is = "Yes": Rows("13").EntireRow.Hidden = True
                            Rows("12").EntireRow.Hidden = False
        Case Is = "No": Rows("12").EntireRow.Hidden = True
                            Rows("13").EntireRow.Hidden = False
      
    End Select
  
    End If
 
    ' Lock Workbook
    ActiveWorkbook.Protect "Password"

 
    ' Unlock Sheet
    ActiveSheet.Unprotect "Passwordsheet"

 
    ActiveSheet.Activate
    If Not Application.Intersect(Range("B11"), Range(Target.Address)) Is Nothing Then
    Select Case Target.Value
        Case Is = "Yes/No": Rows("1:80").EntireRow.Hidden = False
                            Rows("7:18").EntireRow.Hidden = False
        Case Is = "Yes": Rows("13").EntireRow.Hidden = True
                            Rows("12").EntireRow.Hidden = False
        Case Is = "No": Rows("12").EntireRow.Hidden = True
                            Rows("13").EntireRow.Hidden = False
      
    End Select
  
    End If
 
 
     ' Lock Sheet
    ActiveSheet.Protect "Passwordsheet"
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 ' Unlock workbook
    ActiveWorkbook.Unprotect "Password"
 
    ActiveSheet.Activate
    If Not Application.Intersect(Range("B11"), Range(Target.Address)) Is Nothing Then
    Select Case Target.Value
        Case Is = "Yes/No": Rows("1:80").EntireRow.Hidden = False
                            Rows("7:18").EntireRow.Hidden = False
        Case Is = "Yes": Rows("13").EntireRow.Hidden = True
                            Rows("12").EntireRow.Hidden = False
        Case Is = "No": Rows("12").EntireRow.Hidden = True
                            Rows("13").EntireRow.Hidden = False
      
    End Select
  
    End If
 
    ' Lock Workbook
    ActiveWorkbook.Protect "Password"

 
    ' Unlock Sheet
    ActiveSheet.Unprotect "Passwordsheet"

 
    ActiveSheet.Activate
    If Not Application.Intersect(Range("B11"), Range(Target.Address)) Is Nothing Then
    Select Case Target.Value
        Case Is = "Yes/No": Rows("1:80").EntireRow.Hidden = False
                            Rows("7:18").EntireRow.Hidden = False
        Case Is = "Yes": Rows("13").EntireRow.Hidden = True
                            Rows("12").EntireRow.Hidden = False
        Case Is = "No": Rows("12").EntireRow.Hidden = True
                            Rows("13").EntireRow.Hidden = False
      
    End Select
  
    End If
 
 
     ' Lock Sheet
    ActiveSheet.Protect "Passwordsheet"
End Sub