如何使用vba在excel中锁定单元格中的数据

I want to stop others from editing the cell contents in my excel sheet using VBA. Is it possible to do this?

我想阻止其他人使用VBA在我的excel表格中编辑单元格内容。有可能这样做吗?

5 个解决方案

#1

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

或

Call Worksheets("Sheet1").Protect(UserInterfaceOnly:=True)

#2

Try using the Worksheet.Protect method, like so:

尝试使用工作表。保护方法,如下所示:

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中入门的好方法。

#3

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"

#4

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

#5

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

#1