Unprotect sheet error in VBA

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
871
Office Version
  1. 365
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:

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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
 
Upvote 0
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:
Upvote 0
Remove the = signs from bot the protect & unprotect lines
 
Upvote 0
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"
 
Upvote 0
You don't actually need this part
Code:
Password:=
although it doesn't hurt to put it in.
 
Upvote 0
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
 
Upvote 0
What gets highlighted?
 
Upvote 0
"Worksheet" of this line gets highlighted:

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

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top