Multiple Selection Drop Downs, Multiple Columns - Won't Work When Protected

[jhotz88]

Hello!

I'm new to the VBA world and am running into an issue. I used the below code to get multiple selections from drop downs in multiple columns, but when the sheet is protected I'm only able to select one selection from the drop down. I've tried combing through the posts and Google, but nothing seems to be quite right for this code I'm using or it just does not compute with my brain. Any and all help would be MASSIVELY appreciated!

Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from https://trumpexcel.com
' 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 = 9 Or Target.Column = 17 Or Target.Column = 18 Or Target.Column = 19 Or Target.Column = 20 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

 

[GlennUK]

You need to expand on what you mean by : "when the sheet is protected I'm only able to select one selection from the drop down." - describe in detail what is going on, what you expect, what is actually happening, and so on. Also, when you protect the sheet, do you format the cells being used for dropdowns as being unprotected?

 

[jhotz88]

You need to expand on what you mean by : "when the sheet is protected I'm only able to select one selection from the drop down." - describe in detail what is going on, what you expect, what is actually happening, and so on. Also, when you protect the sheet, do you format the cells being used for dropdowns as being unprotected?

Hi Glenn!

Apologies.

I'm using the code to be able to select multiple options from multiple drop down menus across multiple columns .
(i.e. Column I is "species" so can select "dog", "cat", or select both ["dog, cat"],
Column Q is "breed size" so can select "all sizes", "extra small & toy breeds", "small breeds", "medium breeds", "large breeds" or select multiples ["extra small & toy breeds, small breeds"]
Column R is "special diet" so can select from various options including "rawhide free", "gluten free", "grain free", "organic", "natural", etc. or any combination of the options ["rawhide free, grain free"]) .
etc etc
The code I'm using works and I am able to select multiple options from the drop downs with a , separator when the worksheet is not protected.

As soon as I protect the worksheet (as I need to to avoid other people from editing the cells), the code no longer functions as intended. I am only able to select one option from the drop downs. When I go to select a second option, it just switches to the second option (i.e. is not showing multiple options with a , separator - instead of "dog" -> "dog, cat", I get "dog" -> "cat".)

The cells for the drop downs are formatted as being unprotected when the sheet is protected.

Does that help clarify?

Thanks!

 

[GlennUK]

Hi Glenn!

Apologies.

I'm using the code to be able to select multiple options from multiple drop down menus across multiple columns .
(i.e. Column I is "species" so can select "dog", "cat", or select both ["dog, cat"],
Column Q is "breed size" so can select "all sizes", "extra small & toy breeds", "small breeds", "medium breeds", "large breeds" or select multiples ["extra small & toy breeds, small breeds"]
Column R is "special diet" so can select from various options including "rawhide free", "gluten free", "grain free", "organic", "natural", etc. or any combination of the options ["rawhide free, grain free"]) .
etc etc
The code I'm using works and I am able to select multiple options from the drop downs with a , separator when the worksheet is not protected.

As soon as I protect the worksheet (as I need to to avoid other people from editing the cells), the code no longer functions as intended. I am only able to select one option from the drop downs. When I go to select a second option, it just switches to the second option (i.e. is not showing multiple options with a , separator - instead of "dog" -> "dog, cat", I get "dog" -> "cat".)

The cells for the drop downs are formatted as being unprotected when the sheet is protected.

Does that help clarify?

Thanks!

Thanks for all details - I was able to test at my end - and it looks like the SpecialCells attribute is not working in Protected mode.
So, you could remove that testing (increases risk), and use this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from https://trumpexcel.com
' 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 = 9 Or Target.Column = 17 Or Target.Column = 18 Or Target.Column = 19 Or Target.Column = 20 Then
  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
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

 

[jhotz88]

Thanks for all details - I was able to test at my end - and it looks like the SpecialCells attribute is not working in Protected mode.
So, you could remove that testing (increases risk), and use this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from https://trumpexcel.com
' 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 = 9 Or Target.Column = 17 Or Target.Column = 18 Or Target.Column = 19 Or Target.Column = 20 Then
  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
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

Not to be dramatic, but DID YOU EVER KNOW THAT YOU'RE MY HERO?!

Thank you so much for the assistance!!!

If you have a moment, could you explain what you mean by it increases risk? Just want to make sure I'm fully understanding.

Thank you!

 

[GlennUK]

Not to be dramatic, but DID YOU EVER KNOW THAT YOU'RE MY HERO?!

Thank you so much for the assistance!!!

If you have a moment, could you explain what you mean by it increases risk? Just want to make sure I'm fully understanding.

Thank you!

Glad to be of help - as for increased risk, I just mean that code used to check that the cell had Data Validation, and that check isn't done any more.