This 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 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 this link to Personal Macros SelectAllFormulas got more on this.
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…
[vb]
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
[/vb]
To see Unlocked cells in the Used Range….
[vb]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
[/vb]