I need a macro to lock all worksheets and cells. ChatGPT gave me the following but it does not work.

Quote:

---

Sub LockAllWorksheetsAndCells()
Dim ws As Worksheet
Dim cell As Range

' Loop through all worksheets in the workbook
For Each ws In ThisWorkbook.Sheets
' Protect the worksheet
ws.Protect

' Loop through all cells in the worksheet
For Each cell In ws.UsedRange
' Lock each cell
cell.Locked = True
Next cell
Next ws
End Sub

---

I get the following error:

Quote:

---

Unable to set the Locked property of the Range class

---

When I debug it highlights the following line in the macro:

Quote:

---

cell.Locked = True

---

Does anyone have any suggestions?

try locking the cells first, then the worksheets. You may also need to set permissions or other attributes of the cells and worksheets being locked depending on your needs

Pretty sure cells are defaulted to locked, so protecting the sheet locks all the cells on it. Then you can't set properties like .Locked because they're already locked. If you still need to perform operations on them from macros, set UserInterfaceOnly=True when you lock them. You'll need to unprotect and reprotect on workbook open because that gets reset when you close.

If you want to lock only specific cells, do a ws.Cells.Locked = False first. Then lock your specific ranges. If you want to lock the UsedRange only, use ws.UsedRange.Locked=True instead of looping through the cells. The cells are just special instances of the range object, so you're repeating an operation you could just do on the entire range itself. If you want all cells locked, then just leave them or set them all using ws.Cells.Locked and specifying the specific properties you want locked.

From ChatGPT:

I apologize for the confusion. The error you're encountering is likely due to the fact that you can't directly set the `Locked` property of a cell when the worksheet is protected. To resolve this, you need to unprotect the worksheet before setting the `Locked` property and then protect it again afterward. Here's an updated version of the code:


Sub LockAllWorksheetsAndCells()
Dim ws As Worksheet
Dim cell As Range

' Loop through all worksheets in the workbook
For Each ws In ThisWorkbook.Sheets
' Unprotect the worksheet
ws.Unprotect
' Lock all cells in the worksheet
ws.Cells.Locked = True
' Protect the worksheet with a password (optional)
ws.Protect Password:="YourPassword"
Next ws

' Save the changes
ThisWorkbook.Save
End Sub



Replace "YourPassword" with a password of your choice if you want to protect the sheets with a password. If you don't want a password, you can remove the `Password:="YourPassword"` part.

Now, try running the modified macro again. This should resolve the "Unable to set the Locked property of the Range class" error.

This is an example of "AI is not going to do all your work for you". When I go to ChatGPT for assistance on things, sometimes it takes a little back and forth with the tool before you get exactly what you need. I have had many instances where the first macro it gave me didn't completely do what I needed. I simply told ChatGPT what was lagging or what my error was, and it revised the macro for me to resolve that situation.

Thanks everyone for input. The problem was that some of the worksheets were already locked.