如何使用VBA禁用单元格中的更改? [英] How to disable changes in a cell using vba?

问题描述

我正在使用以下代码: 该代码例如:如果我在单元格A1中输入任何值，则单元格B1将显示一个时间戳.

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.

    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

我现在要执行的操作是一旦宏创建了时间戳，就保护用户"B1:B10"单元格，或者对其进行编辑.我用谷歌搜索了如何保护，但是我很难插入我发现的那些代码.谁能帮我将这段代码构造/插入到我的原始代码中?

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?

    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

或此代码:

    '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

感谢卡兹乔(Kazjaw).这是最终代码.

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

推荐答案

如果只想保护范围B1:B10，则只需要运行一次此子 :

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

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.

如果不确定如何一次运行，则可以在Private Sub Worksheet_Change(ByVal Target As Excel.Range)

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

这篇关于如何使用VBA禁用单元格中的更改?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！