VBA 如何“锁定”Excel 工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7435603/
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
VBA How to "Lock" Excel Sheet
提问by Bruno
Is there a way to lock the excel sheet from being altered by the user while my vba code runs and modifies the excel sheet?
有没有办法在我的 vba 代码运行和修改 excel 表时锁定 excel 表不被用户更改？
回答by brettdj
If your code is active then I'm not sure why the user has the ability to modify data at the same time - the macro hourglass should be displayed rendering interaction impossible
如果您的代码处于活动状态，那么我不确定为什么用户能够同时修改数据 - 宏沙漏应该显示为不可能呈现交互
Can you pls post your code?
你可以发布你的代码吗？
Options you may consider include
您可能考虑的选项包括
- adding a progress bar to let users know something is happening (ie don't play while code is running), and how long there is to go This example from Walkenbach is useful http://spreadsheetpage.com/index.php/tip/displaying_a_progress_indicator/
making the Excel Application invisible while the work is occurring
Application.Visible = False 'code Application.Visible = True
- 添加一个进度条让用户知道正在发生的事情（即在代码运行时不要播放），以及还有多长时间来自 Walkenbach 的这个例子很有用 http://spreadsheetpage.com/index.php/tip/ display_a_progress_indicator/
在工作进行时使 Excel 应用程序不可见
Application.Visible = False 'code Application.Visible = True
回答by MikeD
Coming from Excel 2003 ... protecting a sheet without additional actions will prevent user entry andVBA manipulations. In order to prevent user interaction with locked cells but still allow VBA you may use the UserInterfaceOnlyproperty. This can only be done via VBA, there is no way to "manually" protect a sheet in this way. If you manually unprotect a sheet this property is removed. Therefore it's a good idea to set this property on workbook/sheet open/activate events, e.g.
来自 Excel 2003 ... 无需额外操作即可保护工作表将阻止用户输入和VBA 操作。为了防止用户与锁定的单元格交互但仍允许 VBA，您可以使用该UserInterfaceOnly属性。这只能通过 VBA 完成，无法以这种方式“手动”保护工作表。如果您手动取消保护工作表，则会删除此属性。因此，在工作簿/工作表打开/激活事件上设置此属性是个好主意，例如
Private Sub Workbook_Open()
Dim SH As Worksheet
' allow some non-destructive activities
For Each SH In ActiveWorkbook.Worksheets
SH.Protect DrawingObjects:=False, Contents:=True, UserInterfaceOnly:=True, _
AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True
Next SH
End Sub
