时间戳

首页 > 解决方案 > Excel 2013 Workbook_BeforeSave 在 Excel 2016 中生成错误

excel - Excel 2013 Workbook_BeforeSave 在 Excel 2016 中生成错误

问题描述

此宏在 Excel 2013 中有效，但现在我已更新到 2016，它不再有效。如果已填写，它旨在锁定工作簿中多个工作表中的单元格。

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

当On Error Resume Next被删除时，它在Cell.Locked = True.

标签： excelvbaexcel-2016

解决方案

在 Excel 2016 中，workbook_BeforeSave 方法需要额外的非可选参数

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

即使您的代码忽略了它们，您也需要将它们包含在您的方法声明中。

推荐阅读