虽然工作在Excel中的工作表事件vba输入框是不工作和其余的细胞以外的相交我不能更新颜色也

atmip9wb 于 2023-04-07 发布在其他

关注(0)|答案(1)|浏览(104)

我得到的错误“的细胞或聊天你试图改变是在一个受保护的工作表。要进行更改，取消保护工作表”，但我给输入框获取密码从用户这样，自动细胞将被解锁，但为什么我得到这个错误

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim X As Range
    Dim Pass As String
    Pass = "Kals"
    Set X = Union(Range("I3:I9"), Range("K3:K9"))
    If Not Application.Intersect(Target, X) Is Nothing Then
        ActiveSheet.Unprotect Password:=LCase(Pass)
        If Target = "" Then    
        ElseIf Target <> "" Then
            Pass = InputBox("Enter value", "Enter password")
            Target.Locked = True
            ActiveSheet.Protect Password:=LCase(Pass)
        End If
    End If
End Sub

excel

来源：https://stackoverflow.com/questions/75841143/while-working-on-worksheet-events-in-excel-vba-inputbox-is-not-working-and-remai

1条答案

按热度按时间

5jdjgkvh1#

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Const Pass = "Kals"
    Dim v
    With Application
        If .Intersect(Range("I3:I9,K3:K9"), Target) Is Nothing Then
           Exit Sub
        End If
        
        ' new value
        v = Target.Value
       
        ' undo change to get old value
        .EnableEvents = False
        .Undo
        ' check if change allowed
        If Target.Cells.Count = 1 Then
            ' was it blank
            If Target.Value = "" Then
                ' replace "" without pasword
                Target.Value = v
            ElseIf Pass = InputBox("Enter password", "Enter password") Then
                ' password correct enter new value in cell
                Target.Value = v
            Else
                ' incorrect password
                MsgBox "Password incorrect, no change made", vbExclamation
            End If
        Else
            MsgBox "Change 1 cell only", vbExclamation
        End If
        .EnableEvents = True
    End With

End Sub

赞(0）回复(0）举报 2023-04-07

我来回答

虽然工作在Excel中的工作表事件vba输入框是不工作和其余的细胞以外的相交我不能更新颜色也

1条答案

相关问题

热门标签

最新问答