CODEWITHSUNDEEP
vbaexcel
DWReyes
DWReyes

Reputation: 83

Excel VBA code to select all cells with data sometimes working

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

Upvotes: 1

Views: 637

Answers (2)

YowE3K
YowE3K

Reputation: 23994

The Union you are attempting will not work if either of the parameters is Nothing (i.e. you either have no constants in the range, or you have no formulas in the range).

Prior to doing the Union, you should check the parameters aren't Nothing but, once you start changing your code to do that, it would be just as simple to do the locking in two parts - so I recommend you rewrite the code as follows:

Private Sub CommandButton1_Click()
    With Sheets("Uploading Checklist (M)")
        .Unprotect Password:="signature"
        With .Range("A1:M14")
            'Lock any constants
            If Not .SpecialCells(xlCellTypeConstants) Is Nothing Then
                .SpecialCells(xlCellTypeConstants).Locked = True
            End If
            'Lock any formulas
            If Not .SpecialCells(xlCellTypeFormulas) Is Nothing Then
                .SpecialCells(xlCellTypeFormulas).Locked = True
            End If
        End With
        .Protect Password:="signature"
    End With
End Sub

Upvotes: 2

Tony M
Tony M

Reputation: 1762

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.

enter image description here

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

Upvotes: 3

Related Questions