函数将列表框中的所有选中项返回到单元格

问题描述

我希望将活动x列表框中的所有选中项放入工作表的单元格中.我有这段代码可以完全满足我的要求:

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

这会将所有选中的项目放入单元格a1.

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

但是我不能给函数正确的参数以返回与sub相同的参数.请问我需要提出什么?

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?

我尝试了以下操作:

=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?

推荐答案

使用以下代码:

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

然后调用它，将ListBox的名称作为字符串传递:

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

=GetAllSelected("ListBox1")

对于使用ListBox1_Change()事件的以下注释中概述的主动方法，结果将是这样的:

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

但是，在这种情况下，每个ListBox都有一个过程，并且子结果始终会写入相同的单元格中(硬编码).

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).

这篇关于函数将列表框中的所有选中项返回到单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！