Multiple Selection Drop Down List code that works on Protected Worksheet

[jeff106]

Hi Everyone,
Quick question regarding multiple selections on a drop down list on a protected worksheet. Here is the background info..... I have a worksheet that is protected and only cells where I want users inputting data are unlocked. In Column H rows 7:72 I have a data validation set up with a drop down list with the options A, B, C, D. Currently, users can only select one of the options to appear. I need them to be able to select multiple options so the cell could display A,B or A,C,D or A,D for example based on the user input. It was very easy to find VBA code to make a drop down list have multiple selection functionality. However, I have found that the code does not work on a protected sheet. I need the sheet to be protected in order to protect other areas of the worksheet where I have tons of formulas in cells.
I came across this code which was posted on another forum about a year ago that claims to do what I want it to do, but for some reason I can't get it to work . He named the respective cell "menu" so the code wouldn't be impacted if rows were added or deleted. But that won't be the case in my sheet. so I changed - Set rngDV = Range("menu") to Set rngDV = Range("H7:H72"). But still, the multiple selection functionality does not work. I also changed - Private Sub Worksheet_Change(ByVal Target As Range) to Private Sub Worksheet_Albert(ByVal Target As Range)
to reflect the name of my sheet "Albert" However I can't seem to get it to work. Anyone have any idea what I'm missing or know of another code out there that can do what I'm looking for?

Private Sub Worksheet_Change(ByVal Target As Range) ' "Target" is the changed range (cell area)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String

If Target.Count > 1 Then Exit Sub ' if more than one cell changed then exit

On Error GoTo exitHandler
' Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation) ' find dropdown menu cell
(this command fails on a protected sheet)
' use this for protected sheet
Set rngDV = Range("menu") ' "menu" is the range name of the menu cell

If Not Intersect(Target, rngDV) Is Nothing Then ' does the changed cell and dropdown menu cell overlap?

' the cell that changed is the dropdown menu cell
Application.EnableEvents = False ' stop all event based code from starting

newVal = Target.Value ' save new menu selection (Target is the menu cell)

Application.Undo ' bring back previous selection
oldVal = Target.Value ' save the old selection

Target.Value = newVal ' put back the new selection

If (Not oldVal = "") And (Not newVal = "") Then ' makes sure that the menu cell was not previously
' and that it is not now blank

If InStr(1, oldVal, newVal) > 0 Then ' check if new selection is a part of the old selection
' yes - find it and delete it

If Right(oldVal, Len(newVal)) = newVal Then ' checks to see if the new selection is on end of old selection

Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 2) ' yes - delete it from end
Else
Target.Value = Replace(oldVal, newVal & ", ", "") ' no - delete it from middle
End If
Else
Target.Value = oldVal & ", " & newVal ' no - add new value to end of selection
End If
End If
End If

exitHandler:
Application.EnableEvents = True
End Sub