Hey,
I have a sheet with several multiple select dropdown lists and need to have the worksheet protected. I've been using the below code in ThisWorkbook:
Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:="xxxx", UserInterFaceOnly:=True
Next ws
End Sub
But when I protect the worksheet the below code stops working, but it was working before I locked it.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Range
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Not Intersect(Target, Columns(8)) Is Nothing Then
If Target.Value Like "*Done*" Then
Cells(Target.Row, 1).Copy Sheets("Post Campaign Tracker").Range("A" & Rows.Count).End(3)(2)
Cells(Target.Row, 3).Copy Sheets("Post Campaign Tracker").Range("B" & Rows.Count).End(3)(2)
Cells(Target.Row, 4).Copy Sheets("Post Campaign Tracker").Range("C" & Rows.Count).End(3)(2)
Cells(Target.Row, 7).Copy Sheets("Post Campaign Tracker").Range("D" & Rows.Count).End(3)(2)
Cells(Target.Row, 11).Copy Sheets("Post Campaign Tracker").Range("E" & Rows.Count).End(3)(2)
Else
Set x = Sheets("Post Campaign Tracker").Columns(1).Find(Cells(Target.Row, 1), LookIn:=xlValues, lookat:=xlWhole)
If Not x Is Nothing Then
Sheets("Post Campaign Tracker").Rows(x.Row).Delete
End If
Set x = Nothing
End If
Exit Sub
End If
If Target.Count > 1 Then GoTo exitHandler
On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler
If rngDV Is Nothing Then GoTo exitHandler
If Intersect(Target, rngDV) Is Nothing Then
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 5 _
Or Target.Column = 7 _
Or Target.Column = 13 _
Or Target.Column = 14 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
lUsed = InStr(1, oldVal, newVal)
If lUsed > 0 Then
If Right(oldVal, Len(newVal)) = newVal Then
Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 2)
Else
Target.Value = Replace(oldVal, newVal & ", ", "")
End If
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
End If
End If
exitHandler:
Application.EnableEvents = True
End Sub
Any help would be much appreciated!
Thanks
Oscar
Bookmarks