Unprotect sheet error in VBA

[willow1985]

Hello,

I have an error with the below code and am not sure how to fix it. I want to unprotect the sheet (columns A:I, on Sheet "Batch Log"), paste the info in the first blank cell (that was copied from the New BL Data sheet) and re-protect columns A:I , however I get the error at the Selection.Locked = False portion of the code.
Error: 1004, unable to set the locked property of the range class

PS the locked sheet is not password protected. Thank you for your help with this.

Sub NewDataBL()
'
' NewDataBL Macro


With ThisWorkbook.Sheets("New BL Data")
   If Application.CountIf(.Range("B2:I2"), "") > 0 Then
      MsgBox "Please Complete all Fields"
      Exit Sub
   End If
End With
    Range("A2:I2").Select
    Selection.Copy
Sheets("Batch Log").Select
Columns("A:I").Select
Range("BLTable[[#Headers],[TYPE]]").Activate
    Selection.Locked = False
    Selection.FormulaHidden = False
Range("BLTable").Cells(1, 1).End(xlDown).Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
   Columns("A:I").Select
Range("BLTable[[#Headers],[TYPE]]").Activate
    Selection.Locked = True
    Selection.FormulaHidden = False
 Range("BLTable").Cells(1, 1).End(xlDown).Offset(1).Select
    Sheets("New BL Data").Select
    Range("A1").Select


'
End Sub

 

[willow1985]

Thank you

 

[willow1985]

you stated this was the code to protect and unprotect a worksheet, but how would you go about selecting the parameters of the locked sheet?

Example:
AllowSorting:=True, AllowFiltering:=True

Thank you

Carla

 

[willow1985]

This is the current code:

Worksheets("CAPA Log").Protect Password:="SADIE"

But I want to have the following parameters:

DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowSorting:=True, AllowFiltering:=True

How do I combine this?

Thank you

Carla

 

[Fluff]

Simply record a macro of you protecting the sheet with the options you want & you will get the code you need.

 

[willow1985]

I did that but it no longer added my password.

This is the code I got even though I entered the password:

Sub Macro2()
'
' Macro2 Macro
'


'
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True
End Sub

 

[Fluff]

Just add the

Password:="[COLOR=#333333]SADIE[/COLOR]"

to the end of the recorded code.

 

[willow1985]

I get compile error, expected expression on the "=" sign after password OR expected end of statement, depending where I have the Password line (same line or new line)

Worksheets("CAPA Log").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True Password:="SADIE"

 

[Fluff]

You need to add a comma before the word "Password"

 

[willow1985]

Thank you very much

 

[Fluff]

My Pleasure