My original spreadsheet had two columns that imported values based on VLookup functions. Both columns were locked for editing and protected to prevent users from changing data in those two columns. This works fine and as intended. I now need to add new columns that can contain one or more values, so I wrote some code to allow multiple entries for cells in the new column. That works too. However, when I protect the sheet, the locked columns work like before, but the multiple selections for the new column stops working. When I unprotect the sheet, everything works but I really need to protect the two original cells. I tried adding subs for unprotect and protect and call them before and after the script, but I get a compile error, Member already exists in an object module from which this object module derives. I am trying to solve this on my own and watched countless videos to no avail. I am hoping someone can point me in the right direction and either tell me what I am doing wrong and why, and what I need to research to resolve. Any help is greatly appreciated.
Original Code - Works fine when the sheet is unprotected.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Column = 13 Or Target.Column = 8 Or Target.Column = 14 Or Target.Column = 15 Or Target.Column = 21 Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & vbNewLine & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub
Same Code using Protect/Unprotect Calls - This gives me the Compile error on Unprotect
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
unprotect
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Column = 13 Or Target.Column = 8 Or Target.Column = 14 Or Target.Column = 15 Or Target.Column = 21 Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & vbNewLine & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
protect
End Sub
Sub unprotect()
End Sub
Sub protect()
End Sub
This works based on guidance received.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
ActiveSheet.unprotect
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Column = 13 Or Target.Column = 8 Or Target.Column = 14 Or Target.Column = 15 Or Target.Column = 21 Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & vbNewLine & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
ActiveSheet.protect
End Sub
Bookmarks