VBA - Select All Unlocked Cells

We saw how to use Excel protection features in one of our earlier post. We could unlock certain cells and lock other cells so that users can only have option to modify unlocked cells. We also noted that Locking and Unlocking cells will have no effect until the sheet is protected. All good till there, however there is no direct way in Excel to quickly see which cells are locked or unlocked.

When the sheet is protected, you can use Tab key to move through all the unlocked cells. But imagine a situation where you are preparing a sheet to distribute to your colleagues with only few unlocked cells spreading across sheet. To make sure that you have locked / unlocked required cells, you will have to go through Format Cells option and check if the cell you selected is locked or not.

Now imagine yourself as a user of such sheet that someone else designed. You cannot know which cell is unlocked and ready to be modified, unless such cells are highlighted by different colour or any such other markings.

In this post, we are going to develop a code to quickly select all the cells that are in unlocked status. Let’s go ahead and do it. Before going to the coding, let’s discuss a bit on the logic we should follow.

1. First, we should run the macro in the sheet from where the code is run. We know that ActiveSheet object refers just that.
2. In the sheet, the code should run in only those cells which are used i.e. modified, locked or unlocked etc. Why to run the code in all cells in entire sheet there by wasting precious processing time, isn’t it? Alright, with bit of research in Excel Help or going through the Properties of ActiveSheet, you will know that there is a property of ActiveSheet object which is UsedRange that is a collection of all cells that are used.
3. Now we have a collection of all used cells, we need to loop through each of these cells and see if that particular cell is locked or not. We know that For Each Loop can do this work for us.
4. How to know if cell is locked? Let’s see what Excel records when we lock / unlock a cell by recording a macro with this action. By default all the cells are locked, I am going to record a macro while unlocking cell A1. Below is the code Excel gave me.

• Sub Macro1()
  '
  ' Macro1 Macro
  '
   '
      Selection.Locked = False
      Selection.FormulaHidden = False
  End Sub
5. Selection.Locked = False is the line we need. Cell A1 which I selected is called through Selection object and its property Locked is set to False, that means cell is now not locked. Excel Help is your friend again; each Cell has a property Locked. If this property is set to True that means that cell is locked otherwise it is unlocked. (Additional info: Notice the line Selection.FormulaHidden = False, this line is not needed for us now, this code is used if you want to hide the formulas from displaying in Formula Bar)
6. Alright, now we know how to see if a cell is locked or not. One last thing that is left out is what to do with this information. This one depends on your need, you may want to select all such cells or get the addresses of all those cells in a sheet etc. For this exercise, I am going to select all such cells.
• I recorded another macro while selecting cell A2. This is the code.
• Sub Macro2()
  '
  ' Macro2 Macro
  '
  
  
  '
      Range("A2").Select
  End Sub
7. Good, now we know that Range object has a method Select which selects the cell address given in Range object.
8. Now the tricky part, in Excel you can only select a cell or a combination of cells in one operation. If you try to select cell A1 and after that try to select A2, A1 selection will be gone (unless you keep pressing Ctrl button while selecting A2). Since our macro will loop through to identify an unlocked cell and select it, in the next looping, already selected cell selection will be gone. Let’s observe how Excel captures selecting various cells when you press down Ctrl. Below is the code recorded by Excel when I select so many random cells.
9. Sub Macro3()
   '
   ' Macro3 Macro
   '
   
   
   '
       Range("E9,C5,C9,B13,A8,A5,B3,D3,F2,H2,H5,J5,J8").Select
       Range("J8").Activate
   End Sub
10. See, Excel just combines all cells addresses with a comma and embed it in one Range object and use Select method to select them. We will also use the same thing, I am going to use a String to capture unlocked cell addresses and select all of them at the end of the code using above logic.

Let’s put all the above information we noted in code format. Here is my code.

Sub SelectUnlockedCells()
Dim Cl As Range
Dim MyStr As String
 For Each Cl In ActiveSheet.UsedRange 'Start looping, refer to point 1, 2 & 3
    If Cl.Locked = False Then MyStr = MyStr & Cl.Address & "," 'Refer point 4 & 5
Next Cl ‘End looping
If MyStr <> "" Then Range(Left(MyStr, Len(MyStr) - 1)).Select 'Point 6, 7 & 8
End Sub

I used MyStr to capture all the cells that are having Locked property False. I also used a comma (,) to combine different cells addresses. In the last line of code, I am checking if MyStr is empty (means no unlocked cells are found), if it is not empty then used Range object Select method to select all those cells.

One thing I have to explain is what Left(MyStr, Len(MyStr) - 1) does in the code. If you notice the MyStr just before executing this line of code, there will be a comma at the end of the string. This would result in failure of the code as Range object needs exact cell address with comma in-between but not at the ends. So I am removing the extra comma at the end of MyStr by using Left function of VBA. (This function is similar to Excel’s built-in Left function).

Just run the code now and all the unlocked cells will be selected. Now colour them, enter values in them or apply borders, it is up to you what do you want to do.

I hope the above post explains clearly on how you proceed to code your requirement in the start. Once you know VBA Code Structure, you don’t even need to record macros to see what Object to use. So remember the mantra, the more you experiment the more you learn. Happy Learning!!