Unprotect sheet error in VBA

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
635
Office Version
  1. 2019
Platform
  1. Windows
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.
Rich (BB code):
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
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
635
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Jul 24, 2019
Messages
635
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,448
Office Version
  1. 365
Platform
  1. Windows
Simply record a macro of you protecting the sheet with the options you want & you will get the code you need.
 

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
635
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,448
Office Version
  1. 365
Platform
  1. Windows
Just add the
Code:
Password:="[COLOR=#333333]SADIE[/COLOR]"
to the end of the recorded code.
 

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
635
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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"
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,448
Office Version
  1. 365
Platform
  1. Windows
You need to add a comma before the word "Password"
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,448
Office Version
  1. 365
Platform
  1. Windows

Watch MrExcel Video

Forum statistics

Threads
1,114,520
Messages
5,548,529
Members
410,845
Latest member
OldSwimmer1650
Top