0

When i protect my sheet the code to allow multiple selections in a cell from a drop down list doesnt work. Where in the code and what do I put in would allow this to work?

Private Sub Worksheet_Change(ByVal Target As Range)
' To allow multiple selections in a Drop Down List in Excel (without repetition)
    Dim Oldvalue As String
    Dim Newvalue As String
    Application.EnableEvents = True
    On Error GoTo Exitsub
      If Target.Column = 13 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 & ", " & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True

End Sub
Spiff
  • 1
  • 2
  • 1
    Just unprotect your sheet at the start of your code, then protect it again at the end. – dwirony Jan 24 '19 at 15:27
  • 2
    @dwirony instead of that protect it with `UserInterfaceOnly:=True` so VBA can make changes but the user cannot. Also refer to [VBA Excel: Sheet protection: UserInterFaceOnly gone](https://stackoverflow.com/questions/38353751/vba-excel-sheet-protection-userinterfaceonly-gone) – Pᴇʜ Jan 24 '19 at 15:27
  • @Pᴇʜ I can't recall now, but I had tried using that solution in the past and I had to toss it for some reason - I'll try to dig up why that was... – dwirony Jan 24 '19 at 15:28
  • @Pᴇʜ - I have tried this option in the past and have found it very faulty; though to be fair, I may have been missing something. But I had a large project and it failed miserably to do what I asked. – Scott Holtzman Jan 24 '19 at 15:28
  • 1
    @ScottHoltzman Coincidental huh? – dwirony Jan 24 '19 at 15:29
  • 1
    @ScottHoltzman Hm, good to know. I used it several times and it worked well for me. The thing is that the option get lost after re-opening the file. So you must re-protect your sheets on `workbook_open` to make it work again. – Pᴇʜ Jan 24 '19 at 15:30
  • @Pᴇʜ - are you saying the interface only option gets lost when the file is closed and re-opened? what a silly thing – Scott Holtzman Jan 24 '19 at 15:32
  • @ScottHoltzman Yes, exactly. Also see the link I posted which describes that too. I agree: silly! – Pᴇʜ Jan 24 '19 at 15:33
  • Not using SpecialCells and unlocking the protection of the validation cells will work also + With working on Listobjects with VBA and Userinterfaceonly I run into problems – EvR Jan 24 '19 at 15:59
  • 1
    @Peh - good to know that limitation and how to work around it – Scott Holtzman Jan 24 '19 at 16:12
  • I am new to vba so could show me the code and where to put it. Thank you! – Spiff Jan 24 '19 at 17:14
  • I figured it out thanks to all! – Spiff Jan 24 '19 at 17:24

0 Answers0