SOLVED

Selecting multiple items from a dropdown list and returning different values

Copper Contributor

I've seen VBA code that will allow one to select multiple items from a drop-down list.  I've seen VBA code that allow you to create a drop down list and return a different value.  What I need is a combination of the two.  I want to present a user with a logical list of choices I.e. Living Room, Sun Room, Dining Room, etc. and allow them to select more than one.  The list would show the full name but return the abbreviation(s) of the list items selected.   

 

Drop Down List

AllALL
Living RoomLR
Sun RoomSR
Dining RoomDR
KitchenK
Kitchen PantryKP
Breakfast RoomKB
FoyerF

 

Returned Values:

LR, DR, K, KB

K, F

 

Has anyone come across code that would accomplish this or have any suggestions.  As I mentioned I've found VBA code that does one or the other but not the combination.

 

Thanks. 

8 Replies

I hope it helps you,  so I kindly ask you it this is your solution,please hit the like button and mark it as solved.

Sub OutputFromMultiSelectListBox()
   Dim LoopIndex As Integer
   Dim OutputList As String
   For LoopIndex = 0 To ListBox1.ListCount - 1
      If ListBox1.Selected(LoopIndex) Then
         OutputList = OutputList & ListBox1.List(LoopIndex) & vbCrLf
      End If
   Next LoopIndex
   MsgBox "Selected items are:" & vbCrLf & OutputList
End Sub

@DDBDS 

@Juliano-Petrukio thanks for the reply.  I added your suggested code but it didn't appear to change anything.  Currently I have a piece of code that was supplied by ExtendOffice that I found while researching.  This code provides the code utilizing vlookup.  If I could get it to allow multiple selections, that would be the end goal. 

 

Private Sub Worksheet_Change(ByVal Target As Range)
      selectedNa = Target.Value
      If Target.Column = 3 Then
           selectedNum = Application.VLookup(selectedNa, ActiveSheet.Range("dropdown"), 2, False)
           If Not IsError(selectedNum) Then
                  Target.Value = selectedNum
           End If
     End If
End Sub

I thought it was a userform control.
Anyway, read the following article.
Surely it will help you.

https://trumpexcel.com/select-multiple-items-drop-down-list-excel/

Yep, that is the other half of the code but I can't seem to successfully combine the two in order to achieve the desired outcome.
Share your spreadsheet or a sample File só we can try to help you more.
best response confirmed by DDBDS (Copper Contributor)
Solution

 

Find attached and the code below as well.

 

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Oldvalue As String
    Dim Newvalue As String
    Dim selectedNum As Variant
    Application.EnableEvents = True
    On Error GoTo Exitsub
    If Target.Column = 3 Then
        If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
            GoTo Exitsub
        Else: If Target.Value = "" Then GoTo Exitsub Else
            Application.EnableEvents = False
                     
            
            Newvalue = Target.Value
            selectedNum = Application.VLookup(Newvalue, ActiveSheet.Range("dropdown"), 2, False)
            Newvalue = selectedNum
            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

@DDBDS 

1 best response

Accepted Solutions
best response confirmed by DDBDS (Copper Contributor)
Solution

 

Find attached and the code below as well.

 

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Oldvalue As String
    Dim Newvalue As String
    Dim selectedNum As Variant
    Application.EnableEvents = True
    On Error GoTo Exitsub
    If Target.Column = 3 Then
        If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
            GoTo Exitsub
        Else: If Target.Value = "" Then GoTo Exitsub Else
            Application.EnableEvents = False
                     
            
            Newvalue = Target.Value
            selectedNum = Application.VLookup(Newvalue, ActiveSheet.Range("dropdown"), 2, False)
            Newvalue = selectedNum
            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

@DDBDS 

View solution in original post