?
Solved

ComboBox/ListBox with multi select Feature?

Posted on 2014-04-12
4
Medium Priority
?
1,463 Views
Last Modified: 2014-04-12
Hello All,

i have been spending hours but not able to make this work. How do i or what attributes do i change on a control of list-box or combobox so that users are able to multi select  - multiple values from that control and store it in a global Var?

thank you
0
Comment
Question by:Rayne
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 

Author Comment

by:Rayne
ID: 39996808
can someone explain
0
 
LVL 48

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 39996817
You cannot make a combo box multi-select.

To make a listbox multi-select, set the Multi-Select property of the listbox to Simple or Extended.

Once you have done this, the list will no longer have a Value, so you cannot refer to the selected item with code that simply says:

Me.lst_ListName.Value

Instead, you will have to loop through the ItemsSelected collection to identify which items were selected.  
Dim varItem as Variant
Dim strItems as string

For each varItem in me.lst_ListName.ItemsSelected
    strItems = strItems & ", " & me.lst_ListName.column(0, varItem)
Next

Open in new window

However, I'm not sure how you would use that set of values in a global variable.  I use the following function to allow me to use a multi-select listbox as a filter parameter for various reports.  I might call this like:

strFilter = "[SomeField] " & fnMultiList(me.lst_ListName)

This would return the values in the bound column, separate by commas.  If the number of Items selected is zero, the function returns " IS NOT NULL".  If the bound column, or the column provided in the argument list is not numeric, it wraps the values in quotes or in the character(s) provided in the Delimeter argument.
Public Function fnMultiList(lst As ListBox, Optional SelectAll As Boolean = False, _
                            Optional UseColumn As Integer = -1, _
                            Optional Delimiter As Variant = Null) As Variant

    Dim varItem As Variant
    Dim lngItem As Long
    Dim strDelimiter As String
    
    fnMultiList = Null
    
    'Determine how to delimit the list items
    If UseColumn = -1 Then UseColumn = lst.BoundColumn - 1
    If IsNull(Delimiter) = False Then
        strDelimiter = Delimiter
    ElseIf IsNumeric(lst.Column(UseColumn, Abs(lst.ColumnHeads))) Then
        strDelimiter = ""
    Else
        strDelimiter = Chr$(34)
    End If
    
    'loop through the selected items in the list
    If lst.MultiSelect = 0 And SelectAll = False Then
        fnMultiList = lst.Value
    Else
        For lngItem = 0 To lst.ListCount
            If lst.Selected(lngItem) = True Or SelectAll Then
                fnMultiList = (fnMultiList + ",") _
                            & strDelimiter & lst.Column(UseColumn, lngItem) _
                            & strDelimiter
            End If
        Next lngItem
    End If
    
    'Strip trailing "," if there is one
    If Right(fnMultiList, 1) = "," Then fnMultiList = Left(fnMultiList, Len(fnMultiList) - 1)
    
    'Depending on the number of items selected, determine how the "list values" are returned
    If Len(fnMultiList & "") = 0 Then
        fnMultiList = " IS NOT NULL"
    Else
        Select Case Len(fnMultiList) - Len(Replace(fnMultiList, ",", ""))
            Case 0
                fnMultiList = " = " & fnMultiList
            Case Else
                fnMultiList = " IN (" & fnMultiList & ")"
        End Select
    End If
    
End Function

Open in new window

Hope this gives you a starting point for whatever it is you are trying to do.
0
 

Author Comment

by:Rayne
ID: 39996838
thank you Dale, thank you
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 39996842
Glad to help.  Post back if you need some more help with that.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question