Listbox Multiselect values to one string/cell. 2

[RP1America]

I have a multiselect listbox that I would like to insert each selection into one cell, say "A1".

With lstTEST
    .AddItem "A"
    .AddItem "B"
    .AddItem "C"
    .AddItem "D"
    .AddItem "E"
End With

Therefore if all are selected, when the command button is clicked, I would like the cell to read "A, B, C, D, E" or if only every other one is selected, "A, C, E".

Thoughts?

 

[SkipVought]

hi,

Are you asking how to determine which items are selected in a multi-select ListBox, or how to get those selections into one string?

What code do you have so far?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhookom]

I use a generic function to build a WHERE clause based on a multi-select list box:

Public Function GetListBoxValues(strField As String, strlbo As String, strDelim As String) As String
    Dim intIndex As Integer
    Dim strReturn As String
    For intIndex = 0 To Me(strlbo).ListCount - 1
        If Me(strlbo).Selected(intIndex) Then
            strReturn = strReturn & strDelim & Me(strlbo).List(intIndex) & strDelim & ","
        End If
    Next
    If Len(strReturn) > 0 Then
        strReturn = " " & strField & " IN (" & Left(strReturn, Len(strReturn) - Len(",")) & ") "
    End If
    GetListBoxValues = strReturn
End Function

I then use another function to extract what is between the ()s

Public Function GetBetween(strText As String, strStart As String, strEnd As String) As String
    Dim intI As Integer
    Dim intFoundStart As Integer
    Dim intFoundEnd As Integer
    Dim strOut As String
    intFoundStart = InStr(1, strText, strStart)
    If intFoundStart > 0 Then
        intFoundEnd = InStr(intFoundStart, strText, strEnd)
        If intFoundEnd > 0 Then
            strOut = Mid(strText, intFoundStart + 1, intFoundEnd - (intFoundStart + 1))
        End If
    End If
    GetBetween = strOut
End Function

I typically want to store the results in a cell for initializing the list box selection when my form opens again:

    strWhereCDE = GetListBoxValues("klrKPI", "lboKPI", "'")
[green]    'set the range to the values[/green]
    If Len(strWhereCDE) > 0 Then
        Range("lboKPI") = "'" & GetBetween(strWhereCDE, "(", ")")
     Else
        Range("lboKPI") = ""
    End If

Duane
Hook'D on Access
MS Access MVP

 

[RP1America]

Skip, I have no code at this point. I have never worked with a multiselect listbox so not sure where to start.

So, I'm assuming that I need to both determine what items are selected and get those selections into a string.

I'm unfortunately not following too well dhookom's response (thanks dhookom!) and am hoping for a simpler solution.

 

[SkipVought]

Use Duane's

Public Function GetListBoxValues(strField As String, strlbo As String, strDelim As String) As String
    Dim intIndex As Integer
    Dim strReturn As String
    For intIndex = 0 To Me(strlbo).ListCount - 1
        If Me(strlbo).Selected(intIndex) Then
            strReturn = strReturn & strDelim & Me(strlbo).List(intIndex) & strDelim & ","
        End If
    Next

'[b][highlight]comment this If...End If block to just return the list of selected Listbox Items[/highlight][/b]
    If Len(strReturn) > 0 Then
        strReturn = " " & strField & " IN (" & Left(strReturn, Len(strReturn) - Len(",")) & ") "
    End If
'[b][highlight]comment this If...End If block to just return the list of selected Listbox Items[/highlight][/b]

    GetListBoxValues = strReturn
End Function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

...or even better yet, make that a function option...

Public Function GetListBoxValues(strField As String, strlbo As String, strDelim As String[b], optional bListOnly as boolean=TRUE[/b]) As String
    Dim intIndex As Integer
    Dim strReturn As String
    For intIndex = 0 To Me(strlbo).ListCount - 1
        If Me(strlbo).Selected(intIndex) Then
            strReturn = strReturn & strDelim & Me(strlbo).List(intIndex) & strDelim & ","
        End If
    Next
'[b]
    if bListOnly then
      If Len(strReturn) > 0 Then
          strReturn = " " & strField & " IN (" & Left(strReturn, Len(strReturn) - Len(",")) & ") "
      End If
    end if
'[/b]
    GetListBoxValues = strReturn
End Function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhookom]

I've modified my code a bit so that all you send it is the name of the listbox and the delimiter:

Public Function GetListBoxValues(strlbo As String, strDelim As String) As String
    Dim intIndex As Integer
    Dim strReturn As String
    For intIndex = 0 To Me(strlbo).ListCount - 1
        If Me(strlbo).Selected(intIndex) Then
            strReturn = strReturn & strDelim & Me(strlbo).List(intIndex) & strDelim & ","
        End If
    Next
    If Len(strReturn) > 0 Then
        strReturn = Left(strReturn, Len(strReturn) - Len(","))
    End If
    GetListBoxValues = strReturn
End Function

You would save this to a module in your file. Call it in code with a line like:

   Worksheets(1).Cells(1,1) = GetListBoxValues("lstTEST", "")

Duane
Hook'D on Access
MS Access MVP

 

[RP1America]

I am using Duane's suggestion:

Public Function GetListBoxValuesSys(strlbo As String, strDelim As String) As String
    Dim intIndex As Integer
    Dim strReturn As String
    For intIndex = 0 To frmLast(strlbo).ListCount - 1
        If frmLast(strlbo).Selected(intIndex) Then
            strReturn = strReturn & strDelim & frmLast(strlbo).List(intIndex) & strDelim & ","
        End If
    Next
    If Len(strReturn) > 0 Then
        strReturn = Left(strReturn, Len(strReturn) - Len(","))
    End If
    GetListBoxValuesSys = strReturn
End Function

However, if no items are selected from the listbox, I would like for the function to return the string "None".

Any thoughts on how to modify the code above to accomplish this?

Thanks!

 

[Andrzejek]

Try:

Public Function GetListBoxValuesSys(strlbo As String, strDelim As String) As String
    Dim intIndex As Integer
    Dim strReturn As String
    For intIndex = 0 To frmLast(strlbo).ListCount - 1
        If frmLast(strlbo).Selected(intIndex) Then
            strReturn = strReturn & strDelim & frmLast(strlbo).List(intIndex) & strDelim & ","
        End If
    Next
    If Len(strReturn) > 0 Then
        strReturn = Left(strReturn, Len(strReturn) - Len(","))
    End If[blue]
    If Len(strReturn) = 0 Then
        GetListBoxValuesSys = "None"
    Else[/blue]
        GetListBoxValuesSys = strReturn[blue]
    End If[/blue]
End Function

Have fun.

---- Andy

 

[RP1America]

Thanks, Andy! Worked great.

 

[Andrzejek]

Undeserved star, but I’ll take it

Have fun.

---- Andy