VBA Hide Rows - Can't Lock to protect formulas

[JMC57]

I have a SS that puls data from another sheet using the following formula

=IF(OR('Site Visit ASSESSMENT'!D9={"PARTIAL COMPLIANCE","NOT COMPLIANT","NOT ASSESSED","ADDITIONAL INFO NEEDED"}),'Site Visit ASSESSMENT'!A9,"")

I also have VB Code in teh sheet as follows to hide rows. If I protect colum A and protect sheet I get an error. How can I protect the sheet and still have the following commnad work?

Private Sub CommandButton1_Click()
'Toggle rows hidden where cells in column D are blank
If ActiveSheet.AutoFilterMode Then
ActiveSheet.AutoFilterMode = False
Else
Range("D3:D1000").AutoFilter Field:=1, Criteria1:="<>", VisibleDropDown:=False
End If
End Sub
Private Sub CommandButton2_Click()
Range("A3:G1000").ClearContents
End Sub

 

[Jonmo1]

You can UNprotect the sheet with the code, then reprotect it at the end of the code..

Private Sub CommandButton1_Click()
ActiveSheet.UnProtect "Passwordgoeshere"
 
'Toggle rows hidden where cells in column D are blank
If ActiveSheet.AutoFilterMode Then
ActiveSheet.AutoFilterMode = False
Else
Range("D3:D1000").AutoFilter Field:=1, Criteria1:="<>", VisibleDropDown:=False
End If
 
ActiveSheet.Protect "Passwordgoeshere"
End Sub
 
 
Private Sub CommandButton2_Click()
ActiveSheet.UnProtect "Passwordgoeshere"
 
Range("A3:G1000").ClearContents
 
ActiveSheet.Protect "Passwordgoeshere"
End Sub

 

[kpark91]

I believe every time you run the code, you must unprotect the sheets then protect the sheets again.

Sheet1.Unprotect
Sheet1.protect

 

[JMC57]

Thanks!

 

[JMC57]

With the protect and unprotect function it does not appear that the wrap text is not working in col a and b. These col pull data from another sheet . Is there code to auto wrap each cell?

 

[Jonmo1]

Well your original posted code has nothing regarding Wrap Text...

But you can use this to set the Wrap Text Property to True..
Columns("B:C").WrapText = True

 

[JMC57]

Thanks- I did not notice the issue until after I posted. I really appreciate ALL your help with this. Take care.