0

I know how to lock and protect selected cells in a sheet. But are there any way to do it in one sheet, and then it counts for all the sheets. I want to lock the selected yellow cells, in all the sheets.

Image 1

  • 1
    When you open a new workbook/sheet **all the cells are locked**. If you're going to protect the sheet then you have to unlock the cells to allow user interaction. – Mark Fitzgerald Feb 14 '19 at 09:03

1 Answers1

0

This can be done in one go using the UI:

  • Select all the sheets (shift-click on the tabs)
  • Select the range on one sheet (which selects it on all the selected sheets)
  • lock the cells (this does the same for all selected cells)

Unfortunately the VBA Range object does not support "3D ranges" spanning multiple sheets so your options within code are:

  • loop over the sheets and set the Locked property of the appropriate range for each sheet
  • code the equivalent to the UI version above (using Select. This works as Selection can include ranges from multiple sheets)

I would always go for the first option in code - using Selection is slow and very prone to bugs see this question for more. And I can't think of any reason you would need to do it "in one go" when a loop is readable, fast and reliable.

aucuparia
  • 2,021
  • 20
  • 27