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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Mentor82

Active Member
Joined
Dec 30, 2018
Messages
311
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
  2. Mobile
  3. Web
Hi,
Instead of using selection.locked use the following:
Worksheet("YourSheetName"). unprotect
Worksheet("YourSheetName").protect
If you have worksheet protected by password you have to write password:="yourpassword" right after protect or unprotect statement.

Regards,
Sebastian
 

willow1985

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

I get a compile error in the Macro now. Could you look at the code and advise what could be wrong?

Thank you

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
Worksheet("Batch Log").Unprotect = "SADIE"
Range("BLTable").Cells(1, 1).End(xlDown).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Worksheet("Batch Log").Protect = "SADIE"
Range("BLTable").Cells(1, 1).End(xlDown).Offset(1).Select
Sheets("New BL Data").Select
Range("A1").Select


'
End Sub
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,487
Office Version
  1. 365
Platform
  1. Windows
Remove the = signs from bot the protect & unprotect lines
 

Mentor82

Active Member
Joined
Dec 30, 2018
Messages
311
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

Hi,
Flufg is right if you protect/unprotect without password. But if you use password for protection/unprotection the correct syntax would be as follows:
Worksheet("Batch Log").Unprotect Password:="SADIE"
Worksheet("Batch Log").Protect Password:="SADIE"
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,487
Office Version
  1. 365
Platform
  1. Windows
You don't actually need this part
Code:
Password:=
although it doesn't hurt to put it in.
 

willow1985

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

ADVERTISEMENT

Still getting error:

Compile error: Sub or Function not defined.

Here is the code now:

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


Worksheet("Batch Log").Unprotect Password:="SADIE"
Range("BLTable").Cells(1, 1).End(xlDown).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


Worksheet("Batch Log").Protect Password:="SADIE"
Range("BLTable").Cells(1, 1).End(xlDown).Offset(1).Select
Sheets("New BL Data").Select
Range("A1").Select


'
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,487
Office Version
  1. 365
Platform
  1. Windows
What gets highlighted?
 

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
635
Office Version
  1. 2019
Platform
  1. Windows
"Worksheet" of this line gets highlighted:

Worksheet("Batch Log").Unprotect Password:="SADIE"
 
Last edited:

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows

Watch MrExcel Video

Forum statistics

Threads
1,114,590
Messages
5,548,897
Members
410,884
Latest member
schreiberralph
Top