Unprotect/protect sheet and still hide the formula while running VBA

[MMM_84]

Hi folks,

I would appreciate if you help in following.
I have a password protected sheet and the vba that uprotects/protects and runs the code. However while unprotecting it shows the formula bar, which I would like to keep hidden. Any suggestions, please?

Private Sub Worksheet_Change(ByVal Target As Range)
ThisWorkbook.Worksheets("Sheet1").Unprotect Password:="password"
'Unprotect

If Range("B20").Value = "Yes" Then
Rows("26:40").EntireRow.Hidden = False
End If

If Range("B20").Value = "No" Then

Rows("26:40").EntireRow.Hidden = True

ElseIf Range("B20").Value = 0 Then
Rows("26:40").EntireRow.Hidden = True


ThisWorkbook.Worksheets("Sheet1").Protect Password:="password"
'Protect
End If
End Sub

 

[jmacleary]

I've not tested this, but it should work:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating =False
ThisWorkbook.Worksheets("Sheet1").Unprotect Password:="password"
'Unprotect

  If Range("B20").Value = "Yes" Then
      Rows("26:40").EntireRow.Hidden = False
 End If

 If Range("B20").Value = "No" Then
       
      Rows("26:40").EntireRow.Hidden = True

 ElseIf Range("B20").Value = 0 Then
       Rows("26:40").EntireRow.Hidden = True
       

           ThisWorkbook.Worksheets("Sheet1").Protect Password:="password"
'Protect
       End If
Application.ScreenUpdating =True
   End Sub

 

[MMM_84]

End If Application.ScreenUpdating =True

still formulas are shown in a formula bar....

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Me.Unprotect Password:="password"
   'Unprotect
   
   If Range("B20").Value = "Yes" Then
      Rows("26:40").EntireRow.Hidden = False
   ElseIf Range("B20").Value = "No" Then
      Rows("26:40").EntireRow.Hidden = True
   ElseIf Range("B20").Value = 0 Then
      Rows("26:40").EntireRow.Hidden = True
   End If
   
   Me.Protect Password:="password"
   'Protect
End Sub

You're code only protects the sheet if B20=0

 

[MMM_84]

Е

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Me.Unprotect Password:="password"
   'Unprotect
  
   If Range("B20").Value = "Yes" Then
      Rows("26:40").EntireRow.Hidden = False
   ElseIf Range("B20").Value = "No" Then
      Rows("26:40").EntireRow.Hidden = True
   ElseIf Range("B20").Value = 0 Then
      Rows("26:40").EntireRow.Hidden = True
   End If
  
   Me.Protect Password:="password"
   'Protect
End Sub

You're code only protects the sheet if B20=0

Thanks a ton! Works perfectly

 

[Fluff]

Glad we could help & thanks for the feedback.