Selecting All Unlocked Cells

Category: General VBA | [Item URL]

As you probably know, when a worksheet is protected, only the unlocked cells can be changed. You may want to apply different formatting to the unlocked cells, or simply ensure that the correct cells are indeed unlocked.

Oddly, there is no direct way to quickly identify which cells are locked or unlocked. When the sheet is protected, you can use the Tab key to move among the unlocked cells, but it you would like to select them all you'll need a macro.

The macro listed below selects all unlocked cells on the active worksheet.

Sub SelectUnlockedCells()
    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


Search for Tips


All Tips

Browse Tips by Category

Tip Books

Needs tips? Here are two books, with nothing but tips:

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks

© Copyright 2016, J-Walk & Associates, Inc.
Privacy Policy