CODEWITHSUNDEEP
excelvbaexcel-2016
Matt
Matt

Reputation: 11

Excel 2013 Workbook_BeforeSave generates error in Excel 2016

This macro worked in Excel 2013, but now that I have updated to 2016 it is no longer working. It is meant to lock cells across multiple worksheets in a workbook if they have been filled in. 

Private Sub Workbook_BeforeSave()

    'Resume to next line if any error occurs
    On Error Resume Next

    Dim WS_Count As Integer
    Dim I As Integer
    Dim Cell As Range

    'Set WS_Count equal to the number of worksheets in the active workbook.
    WS_Count = ActiveWorkbook.Worksheets.Count

    'loop through all of the Worksheets
    For I = 1 To WS_Count
        With ActiveWorkbook.Worksheets(I)
             'first of all unprotect the entire sheet and unlock all cells
            .Unprotect Password:="open"
            .Cells.Locked = False
             'Now search for non blank cells and lock them
             'unlock blank cells
            For Each Cell In ActiveWorkbook.Worksheets(I).UsedRange
                If Cell.Value > "" Then
                    Cell.Locked = True
                Else
                    Cell.Locked = False
                End If
            Next Cell
             'Now protect the entire sheet
            .Protect Password:="open"
        End With
    Next I

    Exit Sub
End Sub

When On Error Resume Next is removed it is erroring out on Cell.Locked = True.

Upvotes: 0

Views: 70

Answers (2)

Matt
Matt

Reputation: 11

I solved the issue Noob Mistake I had the macro in the module rather than the workbook.

Upvotes: 1

Steve Bull
Steve Bull

Reputation: 97

In Excel 2016, the workbook_BeforeSave method requires additional, non-optional parameters

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

You need to include these in your method's declaration, even if your code ignores them.

Upvotes: 1

Related Questions