vba 如何使用vba锁定excel单元格中的数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3037400/
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
How to Lock the data in a cell in excel using vba
提问by raam
I want to stop others from editing the cell contents in my excel sheet using VBA. Is it possible to do this?
我想阻止其他人使用 VBA 编辑我的 Excel 工作表中的单元格内容。是否有可能做到这一点？
回答by Lance Roberts
You can first choose which cells you don't want to be protected (to be user-editable) by setting the Locked status of them to False:
您可以首先通过将它们的锁定状态设置为 False 来选择您不想保护的单元格（用户可编辑）：
Worksheets("Sheet1").Range("B2:C3").Locked = False
Then, you can protect the sheet, and all the other cells will be protected. The code to do this, and still allow your VBA code to modify the cells is:
然后，您可以保护工作表，所有其他单元格都将受到保护。执行此操作并仍然允许您的 VBA 代码修改单元格的代码是：
Worksheets("Sheet1").Protect UserInterfaceOnly:=True
or
或者
Call Worksheets("Sheet1").Protect(UserInterfaceOnly:=True)
回答by Ben McCormack
Try using the Worksheet.Protectmethod, like so:
尝试使用该Worksheet.Protect方法，如下所示：
Sub ProtectActiveSheet()
Dim ws As Worksheet
Set ws = ActiveSheet
ws.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True, Password="SamplePassword"
End Sub
You should, however, be concerned about including the password in your VBA code. You don't necessarily need a password if you're only trying to put up a simple barrier that keeps a user from making small mistakes like deleting formulas, etc.
但是，您应该担心在 VBA 代码中包含密码。如果您只是想设置一个简单的障碍来防止用户犯诸如删除公式等小错误，那么您不一定需要密码。
Also, if you want to see how to do certain things in VBA in Excel, try recording a Macro and looking at the code it generates. That's a good way to get started in VBA.
此外，如果您想了解如何在 Excel 中的 VBA 中执行某些操作，请尝试录制一个宏并查看它生成的代码。这是开始使用 VBA 的好方法。
回答by Milan Sheth
Let's say for example in one case, if you want to locked cells from range A1 to I50 then below is the code:
例如，在一种情况下，如果您想锁定范围 A1 到 I50 的单元格，那么下面是代码：
Worksheets("Enter your sheet name").Range("A1:I50").Locked = True
ActiveSheet.Protect Password:="Enter your Password"
In another case if you already have a protected sheet then follow below code:
在另一种情况下，如果您已经有一个受保护的工作表，请按照以下代码操作：
ActiveSheet.Unprotect Password:="Enter your Password"
Worksheets("Enter your sheet name").Range("A1:I50").Locked = True
ActiveSheet.Protect Password:="Enter your Password"
回答by krusaint
Sub LockCells()
Range("A1:A1").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= False, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
End Sub
回答by Fink
You can also do it on the worksheet level captured in the worksheet's change event. If that suites your needs better. Allows for dynamic locking based on values, criteria, ect...
您还可以在工作表的更改事件中捕获的工作表级别上执行此操作。如果这更适合您的需求。允许基于值、标准等进行动态锁定...
Private Sub Worksheet_Change(ByVal Target As Range)
'set your criteria here
If Target.Column = 1 Then
'must disable events if you change the sheet as it will
'continually trigger the change event
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
MsgBox "You cannot do that!"
End If
End Sub
