Listbox fails to return string value when multiselect enabled

Question

I have two ActiveX Listboxes set up with the multiselect setting set to 2-fmMultiselectextended. The first has the following code to take all the selected values and concatenate them into a string.

Private Sub ICPA_Change()

   Dim i As Long
   Dim arr(1 To 6) As String
   Dim Receiving As String

   Receiving = " "
'array for 32 variables. First 6 are displayed here.
    arr(1) = "e1"
    arr(2) = "e2"
    arr(3) = "e3"
    arr(4) = "e4"
    arr(5) = "e5"
    arr(6) = "e6"

   'selects each variable in array that is True and concatenates them into a list string

    For i = 0 To ICPAChange.ListCount - 1



       If ICPAChange.Selected(i) = True Then

          Receiving = Receiving & arr(i + 1) & "," & " "

       End If

    Next


    Module1.ICPE = Receiving

End Sub

From there the values stored in Module1.ICPE are used by another routine using data from both Listboxes to write to cells in other workbooks.

wbSAR.Worksheets("COC form").Range("A56").Value = Module1.ICPElements
st.Cells(2 + irow, 4).Offset(, 4).Value = Module1.ICPElements

The first Listbox works perfectly fine . However the second one, with the exact same code and property settings as the first except for a different number of array variables, gives a blank string in both output cells when multiselect is set to 2. This happens whether I select multiple values or just one. Further examination in debug mode shows that the corresponding variable Module1.ICE, has the correct values stored in it but for some reason it is not being written to the cells which will display them. Switching the multiselect option value to 0 results in the listbox returning a string when a value is selected.

Searching through past questions related to multiselect with a Listbox did not turn up this kind of problem.

The properties for these two boxes is initialized as follows:

'For the first Listbox. Only first 6 out of 32 items are displayed in this code.
With ws.OLEObjects.Add("Forms.Listbox.1")
        .Left = 65
        .Top = 821
        .Height = 110
        .Width = 317
        .Name = "ICPA"
        With .Object
            .AddItem "Item1"
            .AddItem "Item2"
            .AddItem "Item3"
            .AddItem "Item4"
            .AddItem "Item5"
            .AddItem "Item6"
            .MultiSelect = 2

        End With
    End With


'For the second Listbox
    With ws.OLEObjects.Add("Forms.Listbox.1")

        .Left = 750
        .Top = 1124
        .Width = 143
        .Height = 101
        .Name = "ICA"
        With .Object
            .AddItem "item1"
            .AddItem "item2"
            .AddItem "item3"
            .AddItem "item4"
            .AddItem "item5"
            .AddItem "item6"
            .AddItem "item7"
            .AddItem "item8"
            .MultiSelect = 2

        End With
    End With

Attached are images of the properties settings of the two listboxes.

I'm afraid nobody is able to help in the way you describe your problem... Two list Boxes with 'the exact same code and property settings identic' will work identic if they will process the same data type! They look identic but not seeing both of them and what data do they handle, we cannot deduce where the mistake can be. — FaneDuru
Do you happen to have a Application.displayalerts = false or an onerror resume next hiding somewhere in the routine that writes to your cell? It looks like the issue is not with the code you posted above, but in your other routine. — Plutian

KeithK KeithK · Accepted Answer · 2019-12-05T20:24:00

I finally figured it out.

The event handler procedure for the problem Private sub was _click. I Changed it to _change and now it works.

Listbox fails to return string value when multiselect enabled

1 Answers