Error message when the work sheet is protected with password

Hello Forum,
I have a workbook with 2 VBA codes. The workbook and the codes work fine until I protect the sheet with password. It stills give me the correct result but as the same time it gives the error:

Run-time error '1004:
Application-defined or object-defined error

How can I remove this error message when the work sheet is protected with password?
Thank you so much for your help
Thup_98

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim sPrompt As String, iReply As Integer, i As Integer

If Intersect(Target, Me.Range("A2:A9")) Is Nothing Or Target.Count > 1 Then
     Exit Sub
     End If

If Target.Row = 9 Then
          sPrompt = "Are you sure you want to reset all data validations?"
          iReply = MsgBox(sPrompt, vbYesNo)
         If iReply = 6 Then Me.Range("C2:C8,F2:F8,I2:I8,N2:N8") = "NA"
         If iReply = 6 Then Me.Range("D2:D8,G2:G8,J2:J8,O2:O8") = 0
     Else
          i = Target.Row
          sPrompt = "Are you sure you want to reset all data validations in row " & i & "?"
          iReply = MsgBox(sPrompt, vbYesNo)
         If iReply = 6 Then Me.Range("C" & i & ",F" & i & ",I" & i & ",N" & i) = "NA"
         If iReply = 6 Then Me.Range("D" & i & ",G" & i & ",J" & i & ",O" & i) = 0
     End If
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim part As String
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    
    
     With Target
     If .Column = 3 Or .Column = 6 Or .Column = 9 Or .Column = 14 Then
If .Value = "NA" Then .Offset(0, 1).Value = 0
End If
     End With
If Target.Column = 14 And Target.Value = "_9000_0001CT" Then
        Target.Offset(0, 1).Value = 1
    End If
    If Target.Column = 14 And Target.Value = "_9000_0001" Then
        Target.Offset(0, 1).Value = 1
    End If
    If Target.Column = 14 And Target.Value = "_9000_0008_220V" Then
        Target.Offset(0, 1).Value = 1
    End If
    If Target.Column = 14 And Target.Value = "_9000_0001CT" Then
        Target.Offset(0, 1).Value = 1
    End If
    If Target.Column = 14 And Target.Value = "_9000_0002" Then
        Target.Offset(0, 1).Value = 1
    End If
    If Target.Column = 4 And Target.Offset(0, -1).Value <> "NA" Then
        part = Target.Offset(0, -1).Value
        Target.Offset(0, -1).Value = Right(Replace(part, "_", "-"), Len(part) - 1)
    End If
    If Target.Column = 7 And Target.Offset(0, -1).Value <> "NA" Then
        part = Target.Offset(0, -1).Value
        Target.Offset(0, -1).Value = Right(Replace(part, "_", "-"), Len(part) - 1)
    End If
    If Target.Column = 10 And Target.Offset(0, -1).Value <> "NA" Then
        part = Target.Offset(0, -1).Value
        Target.Offset(0, -1).Value = Right(Replace(part, "_", "-"), Len(part) - 1)
    End If
       If Target.Column = 15 And Target.Offset(0, -1).Value <> "NA" Then
        part = Target.Offset(0, -1).Value
        Target.Offset(0, -1).Value = Right(Replace(part, "_", "-"), Len(part) - 1)
    End If
    End Sub

Not tested solution, but you are making changes within a Change event handler. You should switch off event handling to avoid looping.

Application.EnableEvents = False
' your code
Application.EnableEvents = True

Hi Invisibleman
Thank you for your advice but I cannot make it works. Could you please give more detail?
For temp. solution I format the “NA” & “0”cells to unlocked when worksheet password protected then everything is OK in this case, because the cells value are pulled from data validation functions which store in another password protected tab so the user cannot change the value of the cells, but I think we still can solve this problem through VBA anybody has any idea?
Your help is greatly appreciated.
Thup_98

'

 
'NOTE:
'Workbook Class Module MUST HAVE EXISTING CODE REMOVED
'Remove this:
'Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
 
' Sheet1: Part Number & QTY
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
Dim part As String
 
If Target.Cells.Count > 1 _
Or IsEmpty(Target) Then Exit Sub
 
Const pw As String = ""     ' change this to your worksheet password
 
Application.ScreenUpdating = False
Application.EnableEvents = False
 
On Error GoTo lblError
 
' A change might be made to the worksheet
' So, unprotect it with the password
Me.Unprotect Password:=pw
 
With Target
    If .Column = 3 _
    Or .Column = 6 _
    Or .Column = 9 _
    Or .Column = 14 Then
        If .Value = "NA" Then .Offset(0, 1).Value = 0
    End If
End With
   
If Target.Column = 14 And Target.Value = "_9000_0001CT" _
Or Target.Column = 14 And Target.Value = "_9000_0001" _
Or Target.Column = 14 And Target.Value = "_9000_0008_220V" _
Or Target.Column = 14 And Target.Value = "_9000_0001CT" _
Or Target.Column = 14 And Target.Value = "_9000_0002" _
Then
    Target.Offset(0, 1).Value = 1
End If
 
If Target.Column = 4 And Target.Offset(0, -1).Value <> "NA" _
Or Target.Column = 7 And Target.Offset(0, -1).Value <> "NA" _
Or Target.Column = 10 And Target.Offset(0, -1).Value <> "NA" _
Or Target.Column = 15 And Target.Offset(0, -1).Value <> "NA" _
Then
   part = Target.Offset(0, -1).Value
    Target.Offset(0, -1).Value = Right(Replace(part, "_", "-"), Len(part) - 1)
End If
 
' single exit point
lblExit:
Dim rInsert As Range
Set rInsert = Me.Range("C2:D8,F2:G8,I2:J8,N2:O8")
 
' allow multiple changes in Data Validation cells
If Not Intersect(Target, rInsert) Is Nothing Then
    ' Data Entry Area
    ' Unprotect Worksheet (again)
    Me.Unprotect Password:=pw
End If
 
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub
 
' (basic) error handler
lblError:
MsgBox "an error has occurred"
GoTo lblExit
 
End Sub
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
Dim sPrompt As String, iReply As Integer, i As Integer
Const pw As String = ""     ' change this to your worksheet password
 
Dim rInsert As Range
Set rInsert = Me.Range("C2:D8,F2:G8,I2:J8,N2:O8")
 
If Not Intersect(Target, rInsert) Is Nothing Then
    ' Data Entry Area
    ' Unprotect Worksheet
    Me.Unprotect Password:=pw
    GoTo lblExit    ' single point of exit !
Else
    ' NOT Data Entry Area
    ' Protect Worksheet
    Me.Protect Password:=pw
End If
 
Application.ScreenUpdating = False
Application.EnableEvents = False
 
On Error GoTo lblError
 
' Clear Data Validation Checks
If Intersect(Target, Me.Range("A2:A9")) Is Nothing _
Or Target.Count > 1 Then GoTo lblExit    ' single point of exit !
 
If Target.Row = 9 Then
     sPrompt = "Are you sure you want to reset all data validations?"
     iReply = MsgBox(sPrompt, vbYesNo)
    If iReply = 6 Then
        Me.Unprotect Password:=pw
        Me.Range("C2:C8,F2:F8,I2:I8,N2:N8") = "NA"
        Me.Range("D2:D8,G2:G8,J2:J8,O2:O8") = 0
        Me.Protect Password:=pw
    End If
Else
     i = Target.Row
     sPrompt = "Are you sure you want to reset all data validations in row " & i & "?"
     iReply = MsgBox(sPrompt, vbYesNo)
    If iReply = 6 Then
        Me.Unprotect Password:=pw
        Me.Range("C" & i & ",F" & i & ",I" & i & ",N" & i) = "NA"
        Me.Range("D" & i & ",G" & i & ",J" & i & ",O" & i) = 0
        Me.Protect Password:=pw
    End If
End If
 
' single exit point
lblExit:
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub
 
' (basic) error handler
lblError:
MsgBox "an error has occurred"
GoTo lblExit
 
End Sub
 
'

Brilliant! It works perfectly.
Thank you InvisibleMan
Thup_98

You're welcome. Thanks for the rep.