Protected Excelsheet becomes unprotect if clicked in any cell

[kkyuvaraj]

Hi,

I have an Excel sheet, which I locked certain cells and protected the sheet.

But the protected sheet automatically becomes unprotected if I enter or clcick in any cell, even if protect the sheet with password, the sheet becomes unprotected without password.

can anyone help on this.

 

[mole999]

what is the VBA you have attacthed to that shee, specifically worksheet_change

 

[kkyuvaraj]

Private Sub Worksheet_Change(ByVal Target As Range)
Me.Unprotect Password:="password"
   If Not Application.Intersect(Range("F50"), Range(Target.Address)) Is Nothing Then
        Select Case Target.Value
        Case Is = "Select": Rows("51:59").EntireRow.Hidden = True
                           
        Case Is = "No":  Rows("51:59").EntireRow.Hidden = True
       
        Case Is = "Yes":  Rows("51:59").EntireRow.Hidden = False
                           
         Me.Protect Password:="password"
        End Select
         

End If
End Sub

 

[mole999]

so your unprotect is the first item and will be processed first, which is why it unprotects

 

[kkyuvaraj]

Can you help to rearrange this code without unprotecting and the code also works well if sheet protected.

 

[mole999]

not tested

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Application.Intersect(Range("F50"), Range(Target.Address)) Is Nothing Then
        Select Case Target.Value
Me.Unprotect Password:="password"
        Case Is = "Select": Rows("51:59").EntireRow.Hidden = True
                        
        Case Is = "No":  Rows("51:59").EntireRow.Hidden = True
      
        Case Is = "Yes":  Rows("51:59").EntireRow.Hidden = False
                          
        
        End Select
    Me.Protect Password:="password"     
End If
End Sub

 

[kkyuvaraj]

That doesn't work

 

[Fluff]

In what way doesn't it work?

 

[kkyuvaraj]

It gives below Error

---------------------------
Microsoft Visual Basic for Applications
---------------------------
Compile error:

Statements and labels invalid between Select Case and first Case
---------------------------
OK Help
---------------------------

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("F50")) Is Nothing Then Exit Sub
    ActiveSheet.Unprotect Password:="password"
    Select Case Target.Value
       Case "Select", "No"
           Rows("51:59").EntireRow.Hidden = True
       Case "Yes"
           Rows("51:59").EntireRow.Hidden = False
    End Select
    ActiveSheet.Protect Password:="password"
End Sub