Search code examples
excelvbaexcel-formulaexcel-2010

While working on worksheet events in Excel vba Inputbox is not working and remaining cells other than intersect I could not update color also


I am getting error of "the cell or chat you are trying to change is on a protected sheet. To make a change, unprotect the sheet" but I gave input box for getting password from user such that automatically cells will be unlocked but why I am getting this error

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

Solution

  • 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