Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Unprotect sheet error in VBA

  1. #1
    Board Regular
    Join Date
    Jul 2019
    Posts
    162
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Unprotect sheet error in VBA

    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.
    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 RoryA; Aug 9th, 2019 at 01:03 PM. Reason: Code tags

  2. #2
    Board Regular
    Join Date
    Dec 2018
    Location
    Poland
    Posts
    203
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Unprotect sheet error in VBA

    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

  3. #3
    Board Regular
    Join Date
    Jul 2019
    Posts
    162
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Unprotect sheet error in VBA

    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 by willow1985; Aug 9th, 2019 at 03:33 PM. Reason: additional info

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,958
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Unprotect sheet error in VBA

    Remove the = signs from bot the protect & unprotect lines
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Dec 2018
    Location
    Poland
    Posts
    203
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Unprotect sheet error in VBA

    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"

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,958
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Unprotect sheet error in VBA

    You don't actually need this part
    Code:
    Password:=
    although it doesn't hurt to put it in.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    Board Regular
    Join Date
    Jul 2019
    Posts
    162
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Unprotect sheet error in VBA

    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

  8. #8
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,958
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Unprotect sheet error in VBA

    What gets highlighted?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  9. #9
    Board Regular
    Join Date
    Jul 2019
    Posts
    162
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Unprotect sheet error in VBA

    "Worksheet" of this line gets highlighted:

    Worksheet("Batch Log").Unprotect Password:="SADIE"
    Last edited by willow1985; Aug 9th, 2019 at 05:01 PM.

  10. #10
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Unprotect sheet error in VBA

    Worksheets

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •