Excel VBA: use of specialcells doesn't work if worksheet is protected

Question

I have a piece of Excel VBA code that works OK if the worksheet is unprotected but does not when protected. I have read the previous similar queries but do not think they apply in this case - the suggested fixes were not applicable or required repeated unprotecting/reprotecting the worksheet which I would like to avoid if possible.

My code looks like this:

Dim NonEmptyCells As Range
Dim Cell As Range

On Error Goto Fin
Set NonEmptyCells = Range("D3:H6").SpecialCells(xlCellTypeBlanks)

For Each Cell In NonEmptyCells 

    '   do stuff with cell

Next Cell

Fin:
On Error GoTo 0

The use of 'On Error' is to avoid the loop when there are zero empty cells found.

The Range(D3:H6) has cell formatting set so the cells are not locked and not hidden.

When the worksheet is not protected the specialcells line correctly returns the range of blank cells, however when protected special cells always errors as if no empty cells are found.

All help appreciated.

Why not just skip the SpecialCells and use If Not cell.Value = vbNullString Then at the start of your loop? — cybernetic.nomad
Have you tried unprotecting the sheet, then re-protecting it when you're done? Or simply iterating the Range you want and inspecting cells' Value (given there aren't that many cells to iterate)? I have read the previous similar queries but do not think they apply in this case - why is that? Please edit your post. — Mathieu Guindon
Ultimately the application will have only a few empty cells in a large area to be searched, so for performance reasons I wanted to use 'specialcells' rather than loop through the whole area in my code. Even if 'specialcells' itself iterates this way internally I expect it to be faster than doing so explicitly in my code. — Geoff
Pragmatically I may end up unprotecting/reprotecting the worksheet as suggested, but it isn't ideal and I would remain unclear why the approach I have taken of using unlocked/unhidden cell properties does not work as I expect. — Geoff

EvR EvR · Accepted Answer · 2018-11-23T08:51:09

You could use Userinterfaceonly when protection is set. Macro's will work, sheet will be protected against changes done by user him/her self.

In the ThisWorkbook-module-section:

Private Sub Workbook_Open()
Worksheets("Sheet1").Protect Password:="yourPassword", UserInterFaceOnly:=True
End Sub

adjust your password and sheetname, (make sure the workbook-open is event has run when running your macro i.e. reopening it)

Excel VBA: use of specialcells doesn't work if worksheet is protected

1 Answers