VBA Disable Paste on Cells with Data Validation

[chasingunicorns]

Hello,

I have this macro that basically checks whether the cells have data validation and whether the pasted value is one of the value in the validation list. My problem arises when the sheet is locked, a '1004' run time error pops up that states that the command cannot run because the sheet is locked. I have put a unprotect and protect command but the error still shows up. When debugging, the error is on the bold and underlined line. Any help or suggestion on why this is happening and how to solve it would be appreciated.

here is ithe code:

Private Sub worksheet_change(ByVal target As Range)
'
'

Dim Result As Variant
Dim UndoList As String
Dim Validate As Range
Dim Selection As Range
Dim InvalidMsg As String

Set Validate = Cells.SpecialCells(xlCellTypeAllValidation)

InvalidMsg = "Value is invalid. Please choose from the dropdown list."

For Each Selection In target.Cells

If Application.CommandBars("Standard").Controls("&Undo").Enabled = True Then
UndoList = Application.CommandBars("standard").Controls("&Undo").List(1)
If Not Intersect(Selection, Range("DatavalidationCat")) Is Nothing Then
If Left(UndoList, 5) = "Paste" Then
Result = Application.Match(Selection, Range("ValidationlistCat"), 0)
On Error Resume Next
If Result = False Then
MsgBox InvalidMsg
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Else
If Intersect(Validate, Selection) Is Nothing Then
ActiveSheet.Unprotect "Password"
With target.Validation
.Add Type:=xlValidateList, Formula1:="=ValidationlistCat"
ActiveSheet.Protect "Password", True, True
End With
Exit Sub
End If
End If
End If
End If
End If

Next

End Sub

 

[Fluff]

You need to put the unprotect line at the beginning of the sub & the protect line a the end.
I would also recommend you change the name of your variable "Selection" as that is a VBA keyword & could cause problems.

 

[chasingunicorns]

would this cause my excel to slow down as it needs to run the locking and unlocking of the sheet everytime something is changed in the sheet?
and thanks for the tip on the "Suggestion" variable

 

[Fluff]

It will, yes.
Another option, untested is

Private Sub worksheet_change(ByVal target As Range)
'
'

Dim Result As Variant
Dim UndoList As String
Dim Validate As Range
Dim Rng As Range
Dim InvalidMsg As String


InvalidMsg = "Value is invalid. Please choose from the dropdown list."

For Each Rng In target.Cells
   
   If Application.CommandBars("Standard").Controls("&Undo").Enabled = True Then
      UndoList = Application.CommandBars("standard").Controls("&Undo").List(1)
      If Not Intersect(Rng, Range("DatavalidationCat")) Is Nothing Then
         If Left(UndoList, 5) = "Paste" Then
            Result = Application.match(Rng, Range("ValidationlistCat"), 0)
            On Error Resume Next
            If Result = False Then
               MsgBox InvalidMsg
               Application.EnableEvents = False
               Application.Undo
               Application.EnableEvents = True
            Else
               ActiveSheet.Unprotect "Password"
               Set Validate = Cells.SpecialCells(xlCellTypeAllValidation)
               If Intersect(Validate, Rng) Is Nothing Then
                  With target.Validation
                     .Add Type:=xlValidateList, Formula1:="=ValidationlistCat"
                  End With
                  Exit Sub
               End If
               ActiveSheet.Protect "Password", True, True
            End If
         End If
      End If
   End If

Next

End Sub