Excel 2013 Workbook_BeforeSave在 Excel 2016 中生成错误

Question

此宏在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上出错。

Answer 1

在 Excel 2016 中，workbook_BeforeSave 方法需要其他非可选参数

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

您需要在方法的声明中包含这些内容，即使代码忽略它们也是如此。

Answer 2

我解决了菜鸟错误的问题，我将宏放在模块中而不是工作簿中。