Error 1004 when setting Range.Locked

by

Here’s a complete explanation:

Typically, there are two causes of this error: trying to change a cell’s Locked property on a protected sheet and/or trying to change the Locked property of a single cell in a merged range.

In the first case, you can either unlock the sheet or set UserInterfaceOnly protection for it, which is highly recommended since you then don’t have to mess with repeatedly unlocking/locking it.

With regard to merged cells, you can’t lock a single cell that is part of a merged range but there are reasonable options:

  • If you are using cell-address notation to reference the cell/range,

    Range("A1").Locked = True

    then reference the whole merged range instead:

    Range("A1:A3").Locked = True 'where "A1:A3" is the entire merged range

  • If you are using a named range for a set of merged cells, by default it will be defined to reference the first of the merged cells only. You can either edit its definition to include the entire merged range or use its MergeArea property to reference its associated merged range:

    Range(“SomeNamedRange”).MergeArea.Locked = True

    But note that you can’t do both since the MergeArea property is, apparently, undefined for a range that is not a strict subset of a larger merged area!

  • Of course, you can always unmerge the merged range before setting the Locked property of an included cell and then re-merge it afterward, but I have never seen a situation in which one of the above two solutions wasn’t sufficient (and much cleaner).

More Related Contents:

  1. How do you change the value of a cell in excel by clicking on it using VBA? [closed]
  2. How to avoid using Select in Excel VBA
  3. How do I use FileSystemObject in VBA?
  4. Convert Text to Date?
  5. Run Excel Macro from Outside Excel Using VBScript From Command Line
  6. Importing text file into excel sheet
  7. Loop through each cell in a range of cells when given a Range object
  8. Checking for #N/A in Excel cell from VBA code
  9. Non-web SQL Injection
  10. Error: “Could Not Find Installable ISAM”
  11. Filling any empty cells with the value above
  12. Is there a way to slow down a Web Scraper so it will pick up the code?
  13. Create a folder and sub folder in Excel VBA
  14. One-dimensional array from Excel Range
  15. VBA Excel sort range by specific column
  16. Select method of Range class failed via VBA
  17. Renaming Files with Excel VBA
  18. Delete cells in an Excel column when rows = 0
  19. Break out of a While…Wend loop
  20. How to make safe API Timers in VBA?
  21. How to copy data from closed workbooks (keeping them closed) into master workbook using VBA?
  22. EntireRow.Delete performance issue
  23. How do I reference tables in Excel using VBA?
  24. Using Excel VBA Macro To Capture + Save Screenshot of Specific Area In Same File
  25. Type Mismatch in Loop to scan Outlook Messages [duplicate]
  26. VBA: How to combine two ranges on different sheets into one, to loop through
  27. Create folder path if does not exist (saving from VBA)
  28. Concatenate multiple ranges using vba
  29. How to highlight selected text within excel
  30. How to fix a VBA “type mismatch” error after switching to 64-bit Excel

Leave a Comment