VBA Run time error 1004 stating a protected sheet when not protected??

willow1985

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

I have the below code but am getting run time error 1004: "the cell or chart you're trying to change is on a protected sheet. To make a change, unprotect the sheet. Youu might be requested to enter a password"
However as you can see in the code I already unprotected the sheet prior.

Why am I getting this runtime error if the sheet is unprotected?

Any help would be appreciated.

Thank you :)

VBA 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

Dim Msg As String, Ans As Variant

    Msg = "Would you like to update the Batch Log with this Data?"

    Ans = MsgBox(Msg, vbYesNo)

    Select Case Ans

        Case vbYes


Sheets("Batch Log").Select
        Dim lo As ListObject
 
  For Each lo In ActiveSheet.ListObjects
 
    lo.AutoFilter.ShowAllData
      Next lo
Worksheets("Batch Log").Unprotect Password:="SADIE"
Sheets("New BL Data").Select
Range("A2:J2").Select
Selection.Copy
Sheets("Batch Log").Select
Range("BLTable").Cells(1, 1).End(xlDown).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False 'GETTING THE ERROR ON THIS LINE'

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

Range("BLTable").Cells(1, 1).End(xlDown).Offset(1).Select
Sheets("New BL Data").Select
    Range("B2:D2").Select
    Selection.ClearContents
    Range("F2:I2").Select
    Selection.ClearContents
   Range("B2").Select
ActiveWorkbook.Save
MsgBox "New Batch Submitted"

        Case vbNo
        GoTo Quit:
    End Select
Quit:
'
End Sub

Carla
 
I have no other code running. Not sure why it is giving me that message prompt causing the error.
 
Upvote 0

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
I see no problem in your code.
Do you have any other macro running on the sheet or in the book's events?

I pass your code a little compacted.

VBA Code:
Sub NewDataBL()
  ' NewDataBL Macro
  Dim Msg As String, Ans As Variant, lr As Long, sh As Worksheet, lo As ListObject

  Application.ScreenUpdating = False
  If Application.CountIf(Sheets("New BL Data").Range("B2:I2"), "") > 0 Then
     MsgBox "Please Complete all Fields"
    Exit Sub
  End If
  Ans = MsgBox("Would you like to update the Batch Log with this Data?", vbYesNo)

  If Ans = vbYes Then
    Set sh = Sheets("Batch Log")
    For Each lo In sh.ListObjects
      lo.AutoFilter.ShowAllData
    Next lo
  
    sh.Unprotect Password:="SADIE"
    sh.ListObjects("BLTable").ListRows.Add AlwaysInsert:=True
    lr = sh.ListObjects("BLTable").ListRows.Count
    Sheets("New BL Data").Range("A2:J2").Copy
    sh.ListObjects("BLTable").DataBodyRange(lr, 1).PasteSpecial xlPasteValues
    sh.Protect Password:="SADIE", DrawingObjects:=True, Contents:=True, _
      Scenarios:=True, AllowSorting:=True, AllowFiltering:=True

    Sheets("New BL Data").Range("B2:D2, F2:I2").ClearContents
    Range("B2").Select
    ActiveWorkbook.Save
    MsgBox "New Batch Submitted"
  End If
End Sub

This code however seems to have fixed the problem. I no longer get the error.

Thank you very much! ?
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

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