0

I know that there are already a bunch of threads on error 1004, but after digging through them I haven't found a situation similar to mine. I'm trying to lock a workbook except for a range of cells, using the info I found here and the following code:

If SecurityActive= "yes" Then
    Worksheets("Invullen").Range("D7, D9, D11, D15, D17, D19, D23, J12, J14:J16").Locked = False

    'INVULLEN
    Worksheets("Invullen").Protect UserInterfaceOnly:=True
    'AFDRUKKEN
    Worksheets("Afdrukken").Protect UserInterfaceOnly:=True
    'AFDRUKKENBEFR
    Worksheets("AfdrukkenBEfr").Protect UserInterfaceOnly:=True
    'BENL
    Worksheets("BEnl").Protect UserInterfaceOnly:=True
    'BEFR
    Worksheets("BEfr").Protect UserInterfaceOnly:=True
    'NL
    Worksheets("NL").Protect UserInterfaceOnly:=True
    'FR
    Worksheets("FR").Protect UserInterfaceOnly:=True
    'UK
    Worksheets("UK").Protect UserInterfaceOnly:=True
    'DE
    Worksheets("DE").Protect UserInterfaceOnly:=True
    'TECHNISCH
    Worksheets("Technisch").Protect UserInterfaceOnly:=True

End If

SecurityActive is a variable I use to be able to quickly change wether I want to secure the workbook or not. The error highlights the second line of the code. Does anyone how to fix the error?

Bart Vos
  • 13
  • 1
  • 4
  • Have you tried setting the `.Locked` state **after** Protecting the Worksheet? – CLR Jun 20 '17 at 09:48
  • Yes. It did not work. – Bart Vos Jun 20 '17 at 09:54
  • You are using `.Range` in wrong way. E.g. instead of `.Range("D7, D9")` use `Application.Union(.Range("D7"), .Range("D9"))`. Read about `Range` and `Union` in help files. – Egan Wolf Jun 20 '17 at 09:55
  • Are you sure there are no typos in your worksheet names? What is the exact error message? @EganWolf His Range method works fine, that's not the issue. – Pᴇʜ Jun 20 '17 at 09:57
  • EganWolf your suggestion didn't work. My code returns the same error when I only try to unlock one cell, so that shouldn't be the problem. @Peh I'm sure there are there are no typos in the worksheet names. The exact error message is: `Fout 1004 tijdens uitvoering: Door de toepassing of door object gedefinieerde fout` Which translates to something like: `Runtime error 1004: Error defined by the application or the object` That's not an exact translation though. – Bart Vos Jun 20 '17 at 10:08
  • Are you sure the sheet is unprotected before? And any possibility that there is more than one open workbook? If so try `ThisWorkbook.Worksheets("Invullen")` to specify the correct workbook. – Pᴇʜ Jun 20 '17 at 10:13
  • @Peh I verified that the sheet had no protection. I have only one workbook open, and trying `ThisWorkbook.Worksheets("Invullen")` doesn't work. – Bart Vos Jun 20 '17 at 10:18
  • @EganWolf the reference to `.Range("D7, D9")` works fine in VBA. @BartVos (Goedendag!) You need to have the sheet unprotected before you can alter the locked state of cells. – Rik Sportel Jun 20 '17 at 10:19
  • That's odd on an unprotected sheet your code works fine for me without issues. – Pᴇʜ Jun 20 '17 at 10:20
  • @Peh Well after clicking away the error message I can edit any cell on the worksheet, so that should mean the worksheet was unprotected right? – Bart Vos Jun 20 '17 at 10:22

1 Answers1

1

Runtime 1004 will be thrown while trying to alter cell properties while the sheet is still protected.

Use:

Worksheets("Invullen").Unprotect
Worksheets("Invullen").Range("D7, D9, D11, D15, D17, D19, D23, J12, J14:J16").Locked = False
'... Other stuff
Worksheets("Invullen").Protect UserInterfaceOnly:=True

etc.

Since Excel 2016 the runtime error 1004 is more specific than until 2010. With protection on it'll throw "1004 Unable to set the Locked property of the Range class."

Edit: This error also get's thrown when one or more cells in the address range are merged cells.

Rik Sportel
  • 2,661
  • 1
  • 14
  • 24