Personal Macros – Locked & Unlocked cells

If you like this post please share it!
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

print
Looking for Locked Unlocked-CellsThis is another handy personal macro I use in my day to day spreadsheeting and application building.  It sits in my Add-In which has it’s own tab on my custom ribbon, but you might just use the ribbon QAT or a personal.xlsb file for these kind of personal efficiency macros.   I did a post on the issue of Personal.xlsb vs Add-Ins here if it is of interest.  By the way the first blog I did on one of these what I call Personal Macros was for selecting all formulas in the currrent region so you can quickly check there are formulas where there should be – click here to see.

So the next one is looking for Locked or Unlocked cells.   Worksheet protection only works the way you want it if you format cells as Locked or Unlocked.  I use this all the time when I review a spreadsheet I want to hand over that has sheet protection.   I need to quickly review if protected cells on all sheets are where they should be in the used range.  So from my custom ribbon I can access this macro and sheet by sheet eye ball see which are the locked or unlocked cells in the used range of that sheet.  Note to use this as a stand alone and not part of a customised ribbon, you don’t want the ‘control As IRibbonControl’ bit so I have edited this out.  Maybe you are more focussed on what’s unlocked, so code for this is further below.

To see locked cells in the Used Range….

Sub SelectWSLockedCells()
'Sub SelectWSLockedCells(control As IRibbonControl)
 Dim WorkRange As Range
 Dim FoundCells As Range
 Dim cell As Range
 Set WorkRange = ActiveSheet.UsedRange
 For Each cell In WorkRange
 If cell.Locked = True Then
 If FoundCells Is Nothing Then
 Set FoundCells = cell
 Else
 Set FoundCells = Union(FoundCells, cell)
 End If
 End If
 Next cell
 If FoundCells Is Nothing Then
 MsgBox "All cells are UNLOCKED."

Else
 FoundCells.Select
 End If
End Sub

To see Unlocked cells in the Used Range….

Sub SelectWSUnlockedCells(control As IRibbonControl)
 Dim WorkRange As Range
 Dim FoundCells As Range
 Dim cell As Range
 Set WorkRange = ActiveSheet.UsedRange
 For Each cell In WorkRange
 If cell.Locked = False Then
 If FoundCells Is Nothing Then
 Set FoundCells = cell
 Else
 Set FoundCells = Union(FoundCells, cell)
 End If
 End If
 Next cell
 If FoundCells Is Nothing Then
 MsgBox "All cells are LOCKED."

Else
 FoundCells.Select
 End If
End Sub

 

 

 

 

Tagged with: ,