How to build a Select Multiple Items from a Drop-Down List?

Question

Hi

As shown below, I designed A3:A12 are the drop-down list.

How can I build Multiple Selection features to select more than one items from a Drop-Down List? And also, automatically sort by a-z within the cell, cannot be selected repeatedly, remove the unwanted selection as well.

In order to optimize the process faster, how can I apply this feature to many columns and difference sheets? Such as:

Here is the file you might need.

https://1drv.ms/x/s!AibaAz5STOvFiA7mKmRawui2WMIz?e=liFDRe 

Many thanks

J.

Answer 1

I have uploaded a zipped file to the following link. Zipped files do not automatically open with On-Line Excel which has limited functionality and problems with VBA code so download the zipped file and extract the xlsm file.

This is an example that I previously made and instead of creating a separate cell with the multiple selections from the DropDown, the multiple selections are contained in the DropDown cell.

https://1drv.ms/u/s!ArAXPS2RpafCtTJ8h3fo5mWVE9OT?e=hxvhXP

To view the VBA code it is in the worksheets module, (Right click the worksheet tab name and select "View Code" ).

If you do not want the solution with the output in the DropDown cell then please get back to me and I will modify to insert into the separate cell.

Following is a copy of the code for reference of anyone else wanting similar code.

Private Sub Worksheet_Change(ByVal Target As Range) 

    Dim newVal As Variant 

    Dim oldVal As Variant 

    Dim lUsed As Long 

    Dim arrSort As Variant 

    Dim j As Long 

    Dim i As Long 

    Dim varTemp As Variant 

    If Target.Count > 1 Then GoTo ExitHandler       'Cannot handle multiple cells (like pasting to multiple cells) 

    If Not Intersect(Target, Range("A:A")) Is Nothing Then  'Can edit "A:A" to a single cell or range of cells 

        On Error Resume Next        'Following line errors if NOT a List Type validation cell. (Therefore handle error) 

        If Target.Validation.Type <> 3 Then     'If NOT a List Type Validation cell so exit process 

            Err.Number = 0      'Cancel the error number (Suppress message in exitHandler) 

            GoTo ExitHandler    'NOT List Type validation so Exit Process 

        End If 

        On Error GoTo ExitHandler           'If code errors then ensures that Events are turned on again. 

        Application.EnableEvents = False    'Suppress recursive calls to this process 

        newVal = Target.Value       'Save the new value selected 

        Application.Undo            'Revert to the previous (Old) value 

        oldVal = Target.Value       'Save the previous (Old) value 

        Target.Value = newVal       'Revert Target to the new value 

        If oldVal <> "" Then        'Only process if previous value existed in cell 

            If newVal <> "" Then    'Only process if new value selected (rather than complete value deleted) 

                lUsed = InStr(1, oldVal, newVal)    'Search for new selected value in the previous existing values 

                If lUsed > 0 Then                   'If selected value already exists 

                    Target.Value = oldVal           'Revert to old value without adding new value 

                Else 

                    Target.Value = oldVal & "," & newVal    'Concatenate Old value & New value 

                    arrSort = Split(Target.Value, ",") 

                    'Sort the contents of Array using bubble sort. 

                    For j = LBound(arrSort) To UBound(arrSort) - 1 

                        For i = LBound(arrSort) To UBound(arrSort) - 1 

                            If arrSort(i) > arrSort(i + 1) Then 

                                varTemp = arrSort(i) 

                                arrSort(i) = arrSort(i + 1) 

                                arrSort(i + 1) = varTemp 

                            End If 

                        Next i 

                    Next j 

                    Target.Value = Join(arrSort, ",")   'Re-Join the array into comma separated string 

                    'Target.WrapText = True          'Optional: Turn On Wrap Text if required 

                End If 

            End If 

          End If 

    End If 

ExitHandler: 

    If Err.Number <> 0 Then 'If code error caused it to be sent to here 

      MsgBox "An error occurred in Private Sub Worksheet_Change, Module " & Me.Name 

    End If 

    Application.EnableEvents = True 

End Sub

Answer 2

Forgot to include the following questions in my previous reply.

Currently for one worksheet only. Please advise if the method of insert in the DropDown cell is acceptable or not and then I will set up to work from any worksheet.

You have said "how can I apply this feature to many columns and difference sheets". Need to know if the columns the same on each sheet or different columns on each sheet?

Will the code be required for all sheets or just some sheets?

Answer 3

I decided not to wait on answers to my previous questions and made some assumptions and the file that I have uloaded works with Multiple sheets. Thought that it might save you some time waiting on me to reply again after answering my questions.

The code is in ThisWorkbook module so it can work with multiple sheets. Avoid other Change Event code in the individual worksheet modules and if any other change event code is required for the sheets being processed for this part of the project then it is best to integrate it with the code that I have supplied. This is a little more complex but it can be done.

You will see where I have used Select Case to determine which worksheet has called the code and you can edit the code to set the required columns for each worksheet. They can all be the same.

Feel free to get back to me if any problems. There is one issue and that is attempting to use "Retry" if the user types an entry that is not valid rather than select from the DropDown. The workaround here is to always select from the DropDown or, if an invalid entry is typed, then "Cancel" in the error dialog rather than "Retry".

As before, following link is to a zipped file to download.

https://1drv.ms/u/s!ArAXPS2RpafCtTPgRLawq5fqiL6L?e=pO7UEZ

Answer 4

Hi OssieMac

Thank you for your code, and sorry for my limited English

I have said "how can I apply this feature to many columns and difference sheets"，This means that the code can be supported all sheets to use it and can be run in difference columns, although there may be a few differences.

For example, the data validation list options  can be a range of uppercase, lowercase, or numbers, etc.

However, you code can only add Multiple Select, but cannot Remove Previous Selections ,in other words, when you select the selected item again, that item is removed from the cell.

Answer 5

The latest example workbook that I upoaded does work on mutiple sheets with differing columns. However, you will need to edit the following section of code. Add code for each additional sheet and also the required columns in the appropriate sheet section of code.

Select Case Sh.Name 

    Case "Sheet1" 

        Set rngIntersect = Sh.Range("A:A,E:E,G:G,I:I,K:K") 'Edit ranges to suit for each sheet 

    Case "Sheet2" 

        Set rngIntersect = Sh.Range("A:A,F:F,H:H,J:J,L:L") 'Edit ranges to suit for each sheet 

    Case "Sheet3" 

        Set rngIntersect = Sh.Range("A:A,M:M,O:O,Q:Q")     'Edit ranges to suit for each sheet 

    Case Else 

        Set rngIntersect = Nothing 

        Exit Sub    'Change not on one of nominated worksheets 

End Select 

I will have a think about how to delete previous selections. Currently, it does not permit multiple selections of the same value. eg. If "D" already exists then cannot add "D" again. The problem with deleting the "D" from the existing list of selections if "D" is selected again is how to determine if the user inadvertently selected "D" a second time of does the user want to delete the existing "D". Currently I am thinking that we will need to pop up a message to confirm a selection has been deleted in case that is not what the user intended and then the user will need to re-insert the selection. Do you have any thoughts on how you would like to handle the deletions but also prevent the user selecting the same value multiple times?