如何使用 vba 禁用单元格中的更改？

How to disable changes in a cell using vba?

提问by Ysanki

I am working with the bellow code: This code do for Example: If I input any value in cell A1, cell B1 display a time stamp.

我正在使用以下代码：此代码用于示例：如果我在单元格 A1 中输入任何值，单元格 B1 将显示时间戳。

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    With Target
       If .Count > 1 Then Exit Sub
       If Not Intersect(Range("B1:B10"), .Cells) Is Nothing Then
           Application.EnableEvents = False
           If IsEmpty(.Value) Then
               .Offset(0, 1).ClearContents
           Else
               With .Offset(0, 1)
                   .NumberFormat = "hh:mm AM/PM"
                   .Value = Now
               End With
           End If
           Application.EnableEvents = True
       End If
    End With
    End Sub

What I am trying to do now is to protect/not editable from the user the cell "B1:B10" once time stamp has made by the macro. I google on how to protect but I am having hard time to insert those code I found. Can anyone help me how I construct/insert this code to my original code?

我现在要做的是在宏制作时间戳后保护/不可编辑单元格“B1：B10”。我用谷歌搜索如何保护，但我很难插入我找到的那些代码。任何人都可以帮助我如何将此代码构造/插入到我的原始代码中？

    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

Or this code:

或者这个代码：

    'select the cell you want to be editable
    Worksheets("Sheet1").Range("B2:C3").Locked = False
    'then protect the entire sheet but still vba program can modify instead.
    Worksheets("Sheet1").Protect UserInterfaceOnly:=True

Thanks to Kazjaw. Here is the final code.

感谢卡兹乔。这是最终的代码。

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    'Protect cell "B1:B10"
    Worksheets("Sheet1").Cells.Locked = False
    Worksheets("Sheet1").Range("B1:b10").Locked = True
    Worksheets("Sheet1").Protect Password:="pass", UserInterfaceOnly:=Tru

    With Target
       If .Count > 1 Then Exit Sub
       If Not Intersect(Range("B1:B10"), .Cells) Is Nothing Then
           Application.EnableEvents = False
           If IsEmpty(.Value) Then
               .Offset(0, 1).ClearContents
           Else
               With .Offset(0, 1)
                   .NumberFormat = "hh:mm AM/PM"
                   .Value = Now
               End With
           End If
           Application.EnableEvents = True
       End If
    End With
    End Sub

采纳答案by Kazimierz Jawor

If you want to protect only Range B1:B10 then you need to run this sub only once:

如果您只想保护范围 B1:B10，那么您只需要运行此子程序一次：

Sub ProtectCellsInB()

    Worksheets("Sheet1").Cells.Locked = False
    Worksheets("Sheet1").Range("B1:b10").Locked = True
    Worksheets("Sheet1").Protect Password:="pass", UserInterfaceOnly:=True

End Sub

I made a modification- I added a password to protection which you can delete.

我做了一个修改 - 我添加了一个密码来保护你可以删除。

If you are not sure how to run it oncethen you could add the whole internal code at the end of your Private Sub Worksheet_Change(ByVal Target As Excel.Range)

如果您不确定如何运行一次，那么您可以在最后添加整个内部代码Private Sub Worksheet_Change(ByVal Target As Excel.Range)