时间戳

首页 > 解决方案 > 将列表框多选保存到单个单元格

excel - 将列表框多选保存到单个单元格

问题描述

我正在开发一个用户表单，用户可以在其中编辑列表选择中的条目并将它们保存到特定单元格。

Private Sub CommandButton3_Click()
   Dim lZeile As Long

     If ListBox1.ListIndex = -1 Then Exit Sub

     If Trim(CStr(TextBox_Name.Text)) = "" Then
         MsgBox "Sie müssen mindestens einen Namen eingeben!", vbCritical + vbOKOnly, "FEHLER!"
         Exit Sub
     End If
     lZeile = 2 
     Do While Trim(CStr(Tabelle4.Cells(lZeile, 1).Value)) <> ""
         If ListBox1.Text = Trim(CStr(Tabelle4.Cells(lZeile, 1).Value)) Then

             Tabelle4.Cells(lZeile, 1).Value = Trim(CStr(TextBox_Name.Text))
             Tabelle4.Cells(lZeile, 11).Value = ListBox_Problem.Text
             Tabelle4.Cells(lZeile, 12).Value = TextBox_Problem2.Text
             Tabelle4.Cells(lZeile, 4).Value = ComboBox1.Text
             Tabelle4.Cells(lZeile, 3).Value = ComboBox2.Text
             Tabelle4.Cells(lZeile, 13).Value = TextBox3.Text
             Tabelle4.Cells(lZeile, 14).Value = TextBox4.Text
             If ListBox1.Text <> Trim(CStr(TextBox_Name.Text)) Then
                 Call UserForm_Initialize
                 If ListBox1.ListCount > 0 Then ListBox1.ListIndex = 0
             End If
             Exit Do
         End If
         lZeile = lZeile + 1
     Loop

End Sub

现在我有一个可以选择多个条目的 ListBox。我想用“，”作为分隔符将选定的条目写入一个单元格。

我为此找到了以下代码：

Dim i As Long

    sText = ""
    Range("B34").Value = ""
    With Me.ListBox1
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                sText = sText & ", " & .List(i)
            End If

Next i
   End With

    sText = Mid(sText, 3)

    If Len(sText) > 0 Then
        Range("B34").Value = sText
    Else
        MsgBox "No selection made.", vbInformation
   End If

我试图将它合并到我的中，但无法让它工作。我对 VBA 不是很流利，想知道是否有人可以帮助我？

标签： excelvba

解决方案

最简单的方法是将您的代码（所做的微小更改）放入一个函数中：

Public Function GetCommaSeparatedListBoxItems(ByVal FromListBox As MSForms.ListBox) As String
    Dim sText As String

    With FromListBox
        Dim i As Long
        For i = 0 To .ListCount - 1 'loop through all items in the ListBox
            If .Selected(i) Then 'chech each item if it is selected and if so …
                sText = sText & ", " & .List(i) '… append this item comma delimited to `sText`
            End If
        Next i
    End With

    'since `sText` is empty in the beginning, data will always start with a comma, so removo that
    'example data before:        ", Item 1, Item 2, Item 3"
    sText = Mid$(sText, 3)
    'example data afterwards:    "Item 1, Item 2, Item 3"

    'return the comma separated items in the function
    GetCommaSeparatedListBoxItems = sText

    'Alternatively instead of the lise above you can …
    If Len(sText) > 0 Then 'check if something was selected in the listbox and  …
        GetCommaSeparatedListBoxItems = sText '… return the value
    Else
        MsgBox "No selection made.", vbInformation '… or a error message
    End If
End Function

您可以轻松使用，例如：

Debug.Print GetCommaSeparatedListBoxItems(FromListBox:=Me.ListBox1)

将其写入单元格的示例，您可以使用以下函数（在您的第一个代码中）：

Tabelle4.Cells(lZeile, 14).Value = GetCommaSeparatedListBoxItems(FromListBox:=Me.ListBox1)

您只需将代码调整Me.ListBox1为您的多选列表框。

推荐阅读