function to return all checked items from list box into cell

Question

I would like to get all checked items in a active x list box into a cell on the worksheet. I have this code that exactly does what I want as a sub:

Private Sub CommandButton1_Click()
Dim lItem As Long
Dim outstr As String

   For lItem = 0 To ListBox1.ListCount - 1

      If ListBox1.Selected(lItem) = True Then

        outstr = outstr & ListBox1.List(lItem) & "/"

      End If

   Next
   Range("A1") = Left(outstr, Len(outstr) - 1)
End Sub

This takes all checked items into cell a1.

I convert this into a function like this:

Function CopySelected(lb As MSForms.ListBox) As String

Dim lItem As Long
Dim outstr As String

For lItem = 0 To lb.ListCount - 1

    If lb.Selected(lItem) = True Then

        outstr = outstr & lb.List(lItem) & "/"

    End If

Next
CopySelected = Left(outstr, Len(outstr) - 1)

End Function

But I cannot give the right argument to the function to return the same as the sub. What do I need to put as an argument please?

I tried the following:

=Copyselected(Sheet1.ListBox1)                           
=Copyselected(Sheet1.ListBoxes("ListBox1"))         
=Copyselected(Sheet1.Shapes("ListBox1").OLEFormat.Object)
=Copyselected(Worksheets("Sheet1").ListBox1)

Nothing seem to work. Is the function incorrect or the passing?

Ralph · Accepted Answer · 2016-06-04T16:31:55.970

3

Use the following code:

Public Function GetAllSelected(strListBox As String)

Dim lItem As Long
Dim outstr As String

With Worksheets(1).OLEObjects(strListBox).Object
    For lItem = 0 To .ListCount - 1
       If .Selected(lItem) = True Then
         outstr = outstr & .List(lItem) & "/"
       End If
    Next lItem
End With

GetAllSelected = Left(outstr, Len(outstr) - 1)

End Function

Then call it passing the name of the ListBox as a string:

=GetAllSelected("ListBox1")

_{Note, that the sheet must be know at this time. If you need to pass the sheet name (as well) to the function then you will have to adjust the code accordingly.}

Update:

For the proactive approach as outlined in the comments below using the ListBox1_Change() event the result would be something like this:

Yet, in this case you would have one procedure for each ListBox and the result of the sub would be always written into the same cell (hard-coded).

edited Jun 04 '16 at 16:31

answered Jun 04 '16 at 13:59

Ralph

9,284
4
32
42

Ralph: thanks, I have another issue though, it doesnt recalc when I change the selection. Can I force this? – nik Jun 04 '16 at 16:05
1

That's another question and the answer is here: http://stackoverflow.com/questions/5719650/user-defined-functions-not-recalculating or here http://stackoverflow.com/questions/11045/refresh-excel-vba-function-results or here http://stackoverflow.com/search?q=%5Bexcel-vba%5D+UDF+calculate – Ralph Jun 04 '16 at 16:06
Ralph sorry but these are all things that trigger a recalc out of the function. My (our) function works on shift+f9 but the issue is that if a user forgets to hit shift f9 after changing selection in listbox it wont recalc. Listbox would have to trigger, no? – nik Jun 04 '16 at 16:15
You can trigger it using the `Private Sub ListBox1_Change()` event. Yet, in this case I would actually remove the UDF (they are always a bad choice and should be avoided) and rather put the entire above code into the `Private Sub ListBox1_Change()` event and directly put the result somewhere on the sheet. *Careful: if you are doing this then you'll have to move the code to the worksheet again and adjust the code.* – Ralph Jun 04 '16 at 16:23
Here is a sample: https://www.dropbox.com/s/f5i5qbihujxxpwt/StackOverflow%20question%2037630786.xlsm?dl=0 The file and this comment will be removed within the next few days. – Ralph Jun 04 '16 at 16:47

score 1 · Answer 2 · answered Jun 04 '16 at 15:12

1

Using code from @Ralph, you should also be able to use your function as is...

Try...

=Copyselected(Sheet1.OLEObjects("ListBox1").Object)

answered Jun 04 '16 at 15:12

Nicholas Patton

786
3
8

sorry but this doesnt work. I m getting a formula error on this – nik Jun 04 '16 at 15:45

function to return all checked items from list box into cell

2 Answers2

Update:

Linked