Error Performing Actions on Only Formula Cells

Question

I was attempting to loop through every worksheet in every workbook in a folder and make sure only the cells containing formulas were locked. I have already been using code to lock all cells in every worksheet, and code to lock every formula in a worksheet, successfully for a few months, so I basically mashed the two pieces of code together to get this:

Sub LockAllFormulas()

Dim myOldPassword As String
Dim myNewPassword As String
Dim ws As Worksheet
Dim FileName As String
Dim rng As Range

myOldPassword = InputBox(Prompt:="Please enter the previously used password.", Title:="Old password input")
myNewPassword = InputBox(Prompt:="Please enter the new password, if any.", Title:="New password input")

FileName = Dir(CurDir() & "\" & "*.xls")
Do While FileName <> ""
Application.DisplayAlerts = False
If FileName <> "ProtectionMacro.xlsm" Then
    MsgBox FileName
    Workbooks.Open (CurDir & "\" & FileName)
    For Each ws In ActiveWorkbook.Worksheets
        If Not Cells.SpecialCells(xlCellTypeFormulas) Is Nothing Then
            ActiveWorkbook.ActiveSheet.Unprotect Password:=myOldPassword
            ActiveWorkbook.ActiveSheet.Cells.Locked = False
            For Each rng In ws.Cells.SpecialCells(xlCellTypeFormulas)
                rng.Locked = True
            Next rng
            ActiveWorkbook.ActiveSheet.Protect Password:=myPassword
        End If
    Next ws
    ActiveWorkbook.Save
    ActiveWorkbook.Close
End If
FileName = Dir()
Loop
Application.DisplayAlerts = True


End Sub

Every time I run it it shows a 400 error. The error matches one I got whenever the code runs into a sheet that doesn't have any code in it, but I thought I fixed that problem when I added:

If Not Cells.SpecialCells(xlCellTypeFormulas) Is Nothing Then

Any ideas what else could be going wrong?

There's a workaround if you like: it's always the first worksheets that cause the problem (i.e. no code / formula in sheets2,3,... does not prompt error), add a simple formula on the worksheet itself will bypass the checking. Noted that your code will still proceed (changed password and etc). — Alex, Aug 06 '14 at 17:14
This is for using a variety of files in sometimes large folders. If it doesn't work in a variety of situations and isn't simple to use for the end user, it won't be any good. Whatever fix I have it has to be in the code. — jpford, Aug 06 '14 at 17:27
SpecialCells(xlCellTypeFormulas) will always assume you have formula available, so NOT will not negate the fact that no formula can be found. You could try throwing exception. Moreover, it's error 400 not 1004? — Alex, Aug 06 '14 at 17:32

score 1 · Accepted Answer · answered Aug 06 '14 at 18:15

1

When working with SpecialCells, you have to be very careful. What I do is I store them in a range sandwiched between OERN and then check of they are not nothing. Here is an example

Dim rng  As Range

On Error Resume Next
Set rng = ws.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0

If Not rng Is Nothing Then
    '
    '~~> Rest of the code
    '
End If

Applying that to your code will be like this (UNTESTED)

Dim LockedRange As Range

For Each ws In ActiveWorkbook.Worksheets
    With ws
        On Error Resume Next
        Set LockedRange = .Cells.SpecialCells(xlCellTypeFormulas)
        On Error GoTo 0

        If Not LockedRange Is Nothing Then
            .Unprotect Password:=myOldPassword

            .Cells.Locked = False
            LockedRange.Locked = True

            .Protect Password:=myPassword
        End If

        Set LockedRange = Nothing
    End With
Next ws

answered Aug 06 '14 at 18:15

Siddharth Rout

147,039
17
206
250

That seems to work, but for some reason with this version it results in an error if the sheet is already locked. Which is weird, since I unprotect it in the code. – jpford Aug 06 '14 at 18:44
Can you check if the passwords are correct? Also what error message do you get and on what line? – Siddharth Rout Aug 06 '14 at 18:50
I'm leaving blank passwords at the moment (protected, but not passworded). The error message I get is "object required." – jpford Aug 06 '14 at 19:09
Actually, I had an error in the Dim statement. I fixed it, and it works fine now. Thanks! – jpford Aug 06 '14 at 19:20

Error Performing Actions on Only Formula Cells

1 Answers1