Absurd Bug with Protected Cells

L

legolas97

New Member
Joined
May 24, 2022
Messages
30
Office Version
  1. 365
Platform
  1. Windows
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.

Debug.png

Debug 2.png
 
Sort by date Sort by votes
Coding4Fun said:
VBA Code: 
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
Click to expand...
I'm not able to make it work mate.

I tried pasting your code as it is, tried modifying it
Coding4Fun said:
VBA Code: 
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
Click to expand...
Hey mate thanks for the attempt.

The issue still remains with cell B11 though. It throws up the same runtime error 1004
 
Upvote 0

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
legolas97 said:
I'm not able to make it work mate.

I tried pasting your code as it is, tried modifying it

Hey mate thanks for the attempt.

The issue still remains with cell B11 though. It throws up the same runtime error 1004
Click to expand...
I can reproduce the error and I can get it to work like this, you need to replace "Password" with your password to unlock the sheet or workbook.

This code:

VBA Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 ' Unlock workbook
    ActiveWorkbook.Unprotect "Password"
    ActiveSheet.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
   
    ActiveWorkbook.Protect "Password"
    ActiveSheet.Protect "Password"
 


End Sub


Or this code:


VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
 ' Unlock workbook
    ActiveWorkbook.Unprotect "Password"
    ActiveSheet.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
   
    ActiveWorkbook.Protect "Password"
    ActiveSheet.Protect "Password"
End Sub
 
Upvote 0
VBA Code: 
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 "Sheet1"

 
    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 "Sheet1"
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 "Sheet1"

 
    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 "Sheet1"
End Sub


I tried this variant apart from copy pasting yours as well as this one below.


VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    ' Unlock workbook
    ActiveWorkbook.Unprotect ""
 
    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 ""

 
    ' Unlock Sheet
    ActiveSheet.Unprotect "Sheet1"

 
    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 "Sheet1"
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 ' Unlock workbook
    ActiveWorkbook.Unprotect ""
 
    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 ""

 
    ' Unlock Sheet
    ActiveSheet.Unprotect "Sheet1"

 
    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 "Sheet1"
End Sub





[CODE=vba]
[/CODE]
 
Upvote 0
Use this code below without the unprotect being commented out.
See below I can reproduce the error.

If you unprotect the workbook and or possibly the worksheet (There are 2 different levels of locking) before you run your code it will work.

2022-05-24 00_04_33-Microsoft Visual Basic for Applications - Book1 [break] - [Sheet1 (Code)].png
2022-05-24 00_04_18-Microsoft Visual Basic for Applications - Book1 [running] - [Sheet1 (Code)].png




Use this code:

VBA Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 ' Unlock workbook
    ActiveWorkbook.Unprotect "Password"
    ActiveSheet.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
    
    ActiveWorkbook.Protect "Password"
    ActiveSheet.Protect "Password"
 


End Sub
 
Upvote 0
Looks like we're getting there but the issue is not fully resolved yet :geek:

VBA Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 ' Unlock workbook
    
    ActiveSheet.Unprotect ""
    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
    
    
    ActiveSheet.Protect ""
 


End Sub

I've removed the password as I'm currently protecting it without a password.
It allows me to change yes/no, yes, no without throwing up an error now, but its no longer conditional hiding the cells its supposed to be hiding.

Debug 6.png
 
Upvote 0
It's hiding and unhiding randomly. Like it works, but in a very buggy way. I don't know how to explain.

If i click yes, it doesn't automatically hide, if click on random locked places in the sheet, then it hides like very delayed.

Is the code taking time to run or is the end select function happening too late?
 
Upvote 0
legolas97 said:
It's hiding and unhiding randomly. Like it works, but in a very buggy way. I don't know how to explain.

If i click yes, it doesn't automatically hide, if click on random locked places in the sheet, then it hides like very delayed.

Is the code taking time to run or is the end select function happening too late?
Click to expand...
So basically, I have to change the drop down from yes/no, yes, no. Then change the value of any other drop down and click on this drop down again, then it hides or unhides properly.

Until then it doesn't hide.
 
Upvote 0
VBA Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 ' Unlock workbook

    ActiveSheet.Activate
    If Not Application.Intersect(Range("B11"), Range(Target.Address)) Is Nothing Then
    ActiveWorkbook.Unprotect "Password"
    ActiveSheet.Unprotect "Passwordsheet"
    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
    
    Else
    
    ActiveWorkbook.Protect "Password"
    ActiveSheet.Protect "Passwordsheet"
  
    End If
    
    If Not Application.Intersect(Range("B12"), Range(Target.Address)) Is Nothing Then
    
    Range("B11").Select
  
    End If
    
    If Not Application.Intersect(Range("B13"), Range(Target.Address)) Is Nothing Then
    
    Range("B11").Select
  
    End If


End Sub
 
Upvote 0
Solution
Just an after thought....

Activesheet.Activate

If its the active sheet its already active, this line is not needed. :)

Also that previous code should work provided you hit enter after you change the value in B11, if you tab or click somewhere else in the sheet it wont work until you click on B11 again.
 
Last edited:
Upvote 0
Coding4Fun said:
Just an after thought....

Activesheet.Activate

If its the active sheet its already active, this line is not needed. :)

Also that previous code should work provided you hit enter after you change the value in B11, if you tab or click somewhere else in the sheet it wont work until you click on B11 again.
Click to expand...
Thanks a ton mate! It does work if I hit enter after clicking the dropdown.

Apart from that no issue. If there's a work around to that as well, it will sort it 100%. But I guess this resolves 99% of my problems. Thanks a bunch!
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
VBA to protect/unprotect sheet...
Replies
6
Views
78
jkpieterse
jkpieterse
D
  • Question Question
Make the code work in a protected sheet
Replies
7
Views
97
dmt32
D
D
  • Solved
How to control formatting of unprotected cells in a protected sheet? Ideas?
Replies
2
Views
343
Dman333
D
A
  • Question Question
VBA help - Locking & unlocking on certain times of day
2
Replies
17
Views
342
dmt32
D
E
  • Solved
Locking of cells with protected sheet not working
Replies
5
Views
412
Eurekaonide
E

Forum statistics

Threads
1,223,893
Messages
6,175,248
Members
452,623
Latest member
cliftonhandyman

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top