VBA Lock and protect cells

Hello,

I will try and explain this the bext I can.
The attached sheet is an example sheet of what I am trying to do.
All cells are 'Locked' and 'Hidden' with the exception of W3:X45.

I have a VBA script that, when I save the document, it looks at the cells W3:X45, adds the 'Locked' atribute to the non-empty cells and then protects the sheet. This bit is working but it is also unlocking all the empty cells.

I want to leave all the other cells 'Locked', protected and untouched, I only want the VBA script to work on the section of W3:X45; locking the cells that containing something.

I have tried a few varitions of the script but I am no expert at VBA so decided to go to those that are :-)

Instead of adding all my code attempts to this I have added them all to the VBA Script editor for you to look at and compare.


Thank you all in advance.

Hi mowens74,

Thank you for providing a sample workbook. It makes testing the code a lot easier.

Try the following code:

Sub TestLockCellsContainingDataInRange()
  ActiveSheet.Unprotect
  Call LockCellsContainingDataInRange(Range("W3:X45"))
  ActiveSheet.Protect
End Sub

Sub LockCellsContainingDataInRange(myInputRange As Range)
  'This sets the 'Locked' attribute for all cells in the 'Input Range' that
  'contain data that is NOT a Formula
  '
  'NOTE: It is the calling routine's responsibility to Unprotect the Sheet before
  '      calling this routine

  Dim myRangeConstants As Range

  'Unlock All Cells in the Range
  myInputRange.Locked = False
  
  'Find the Sub-Range of cells that contain Data
  Set myRangeConstants = myInputRange.SpecialCells(xlCellTypeConstants)
  
  'Lock All Cells that Contain Data
  myRangeConstants.Locked = True
  Debug.Print myRangeConstants.Address
   
  'Clear Object Pointer
  Set myRangeConstants = Nothing

End Sub

Lewis