Selecting Locked/Unlocked Cells in Excel
Don came across an interesting problem in Excel recently:
I have been looking for a macro to identify unlocked cells, with
or without text. It seems to me that if someone was making a spreadsheet
or a form and wanted a quick way to identify unlocked cells without
manually checking them, a macro that turned the background red of
unlocked cells or identified them in some other manner would be useful.
I have done work in Excel and because I have had to add or delete cells
or rows or columns or simply by error, someone would come to me and say
they can't tab through the cells properly.
Thanks for the idea Don! I’ve also had a need to find all of the locked or
unlocked cells in a worksheet. In Excel’s VBA one of the properties of a
Range object is “Locked.” As its name suggests, it will return TRUE if the
cells is locked and FALSE if not. You can even use it to check a range of
cells. For example, if A1:G10 are all locked cells the statement
Range(“A1:G10”).Locked will return TRUE. If the range is mixed with locked
and unlocked cells it will return Null.
I’ve put together a couple of macros that will select all of the protected
or unprotected cells in a range. However, there are a couple of concepts in
them that haven’t been covered in this ezine yet, so let’s go over them
first.
It usually will take a long time to run a macro that checks each individual
cell if you select an entire worksheet. Excel can loop through cells fairly
quickly, but when you’re talking 16.8 million cells per page, the time adds
up. Usually we only need to check the parts of the worksheet with data on it
or the range from cell A1 to the last cell of a worksheet. The Last Cell is
found by pressing Ctrl+End (or using menus: Edit>Go To…, click on “Special”
and select “Last Cell”). There is no data or formatting below or to the
right of that cell. In these macros we don’t really need to check any cells
beyond the last cell. In VBA the last cell is a “special cell.” You can
reference it with: expression.SpecialCells(xlLastCell).
To make sure the selection we choose only contains cells above or to the
left of the last cell, we’ll use the Intersect() function. Intersect() takes
two or more ranges and returns a range that is common to all of them. For
example, these two rectangles represent two ranges. The shaded area is
common to both so that range will be returned from the Intersect() function.
Now let’s take a look at the macros:
Sub Select_Locked_Cells()
Dim c As Range
Dim sel As String
On Error GoTo exit_sub
If Intersect(Selection, Range("A1", _
Cells.SpecialCells(xlLastCell).Address)) Is Nothing Then _
MsgBox "The selected cells are outside the Used Range", _
vbOKOnly + vbInformation, "Error"
Intersect(Selection, Range("A1", _
Cells.SpecialCells(xlLastCell).Address)).Select
For Each c In Selection.Cells
If c.Locked Then sel = sel & "," & c.Address
Next
If Len(sel) > 1 Then
sel = Mid(sel, 2)
Range(sel).Select
Else
MsgBox "None of the selected cells are Locked", _
vbOKOnly + vbInformation, "Locked"
End If
exit_sub:
End Sub
Sub Select_Unlocked_Cells()
Dim c As Range
Dim sel As String
On Error GoTo exit_sub
If Intersect(Selection, Range("A1", _
Cells.SpecialCells(xlLastCell).Address)) Is Nothing Then _
MsgBox "The selected cells are outside the Used Range", _
vbOKOnly + vbInformation, "Error"
Intersect(Selection, Range("A1", _
Cells.SpecialCells(xlLastCell).Address)).Select
For Each c In Selection.Cells
If Not c.Locked Then sel = sel & "," & c.Address
Next
If Len(sel) > 1 Then
sel = Mid(sel, 2)
Range(sel).Select
Else
MsgBox "None of the selected cells are Unlocked", _
vbOKOnly + vbInformation, "Unlocked"
End If
exit_sub:
End Sub
After you’ve run the macro you can easily use the menus or toolbars to apply
formatting to all of the cells found.
Happy Coding! Remember, I’ll give $5.00 to anyone who suggests an idea that
I can use in this article. Just email me the idea at chade@welchkins.com and put “Idea for Macro Mania” in the subject line.
Chad K. Welch works as a technician/enabler in
Utah. He is available for
consulting or application programming with Microsoft Office and VBA.
Do you have a question or
tip you’d like to have Chad address in this column? Send an
email to linda@personal-computer-tutor.com
or contact him directly at chad@welchkins.com for more information.
|
,
