Excel VBA code to select all cells with data sometimes working

Question

I once built a VBA button to automatically lock all cells with data in them. And it was working perfectly. Now I wanted to copy that button to another worksheet. So I created another button, copy and pasted the whole VBA over, then edited the worksheet names and range. And, it's only working like 5% of the time, the rest of the time, I'm getting an "Run-Time error '1004': No cells were found." I've tried a few fixed, changing Sheets to Worksheets, or adding a ", 23" to the specialcells argument. However, nothing is working right now. When I try stepping in, it sometimes say both rng and lckrng as empty, and sometimes only show lockrng as empty and not show rng at all. Problem is this used to be a working code, and now, it still works around 5% of time. Any idea why? Thank you very much!

Private Sub CommandButton1_Click()

Dim rng As Range
Dim lockrng As Range

Sheets("Uploading Checklist (M)").Unprotect Password:="signature"
Set rng = Range("A1:M14")

'Selecting hardcoded data and formulas
  Set lockrng = Union(rng.SpecialCells(xlCellTypeConstants), rng.SpecialCells(xlCellTypeFormulas))

lockrng.Locked = True
Sheets("Uploading Checklist (M)").Protect Password:="signature"

End Sub

I'd like to test this out. Before I get started, I'm guessing you've ruled out stuff like formatting inconsistencies (although if you leave the second argument in SpecialCells blank it should pick all cells irrespective of formatting) and anything about the ranges being dis-contiguous? — neophlegm
And have you checked that you actually have both constants and formulas within that range (i.e. it isn't all constants or all formulas)? — YowE3K
Also as another thought: I don't know if you'd get an error if you had more than 30 discontinuous regions as arguments in Union: https://msdn.microsoft.com/VBA/Excel-VBA/articles/application-union-method-excel — neophlegm
P.S. I would certainly be changing Range("A1:M14") to be Sheets("Uploading Checklist (M)").Range("A1:M14") to avoid one potential source of problems. — YowE3K
@neophlegm The 30 in that documentation would refer to the number of parameters, not what shape each of the parameters is (i.e. not how many Areas were in each parameter). (Note: I just tested and could create a Union of 600 discrete, discontinuous, cells, just adding one at a time to the previous union within a loop. I stopped at 600 - that wasn't a limit.) — YowE3K

Tony M Tony M · Accepted Answer · 2017-09-06T05:27:14

Maybe this is too simplistic, but it seems to do what you want. The animated .gif shows it working to "lock all cells with data in them". (I made the second button just for convenience). If nothing else it might be good to start from something like this that works and modify to suit your needs.

Dim cell As Range, sh As Worksheet

Sub Button4_Click()
Set sh = Worksheets("Sheet1")
sh.Unprotect Password:="s"
For Each cell In sh.UsedRange
  If cell <> "" Then cell.Locked = True Else cell.Locked = False
Next
sh.Protect Password:="s"
End Sub

Sub Button5_Click()
Set sh = Worksheets("Sheet1")
sh.Unprotect Password:="s"
End Sub

Excel VBA code to select all cells with data sometimes working

2 Answers