vba 无法取消保护 Workbook_BeforeSave 中的 Excel 工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14404567/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can't unprotect an excel worksheet in Workbook_BeforeSave
提问by Dave Thunes
I am using Workbook_BeforeSave to update some cells on a locked sheet in excel 2010. The subroutine works as desired when using ctrl-s to save, but will not unlock the sheet when using .Save in vba.
我正在使用 Workbook_BeforeSave 更新 excel 2010 中锁定工作表上的某些单元格。使用 ctrl-s 保存时，子例程按需要工作，但在 vba 中使用 .Save 时不会解锁工作表。
ThisWorkbook(code)
本工作簿（代码）
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim MyPassword As String
MyPassword = "password"
ActiveWorkbook.Worksheets("Sheet1").Unprotect (MyPassword)
ActiveWorkbook.Worksheets("Sheet1").Range("A1").Value = Now
ActiveWorkbook.Worksheets("Sheet1").Range("A2").Value = ThisWorkbook.BuiltinDocumentProperties("Author")
ActiveWorkbook.Worksheets("Sheet1").Protect (MyPassword)
End Sub
Module1(Code)
模块1（代码）
Sub SaveMe()
ActiveWorkbook.Save
End Sub
I have a button that calls SaveMe(). SaveMe() saves the document, activating Workbook_BeforeSave. The Worsheet fails to unprotect, causing an error when writing to A1.
我有一个调用 SaveMe() 的按钮。SaveMe() 保存文档，激活 Workbook_BeforeSave。工作表无法取消保护，导致写入 A1 时出错。
The error states:
错误指出：
Run-time error '1004':
Application-defined or object-defined error
回答by user1917229
This worked for me, but it is not very elegant. I did not find a separate module for both unprotecting and writing worked.
这对我有用，但不是很优雅。我没有找到一个单独的模块用于取消保护和写入工作。
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
UnlockWorksheets
With ThisWorkbook.Worksheets("Sheet1")
.Range("A1").Value = Now
.Range("A2").Value = ThisWorkbook.BuiltinDocumentProperties("Author")
End With
LockWorksheets
End Sub
Sub SaveMe()
UnlockWorksheets
ThisWorkbook.Save
LockWorksheets
End Sub
Sub UnlockWorksheets()
ThisWorkbook.Worksheets("Sheet1").Unprotect Password:="password"
End Sub
Sub LockWorksheets()
ThisWorkbook.Worksheets("Sheet1").Protect Password:="password"
End Sub
回答by Maudise
why not put the 'before save' module into a new sub routine and have the before close call that
为什么不将“保存前”模块放入一个新的子例程中，并在关闭之前调用
EG.
例如。
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Call SomeSub
End Sub
sub someSub()
'Code here
end sub
sub Button()
call SomeCub
activeworkbook.save
end sub
