Excel VBA导出2列列表框以分离工作表 [英] Excel VBA Exporting 2 Column Listbox to Separate Worksheet

问题描述

我正在尝试将2列列表框导出到新的工作表。我想在新的工作表中显示每一列。这需要重复两次，因为有3个列表框。用户将在列表框中选择所需的行，然后按一个单独的确认命令按钮。

I'm trying to export a 2 column list box to a new worksheet. I want to display each column in the new worksheet. This needs repeating twice as there are 3 listboxes. The user will select the desired row in the listbox and then press a separate 'confirm' command button.

我写了以下代码，只会导出每个列表框的第一列。我使用RowSource来使列表框双列。

I have written the following code that will export only the first column of each listbox. I have used the RowSource to make the listbox double columned.

任何帮助都非常感谢。

 Private Sub ConfirmBtn_Click()
 Dim emptyRow As Long

   Sheet2.Activate

   emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

   Cells(emptyRow, 1).Value = SRCLstBox.Value
   Cells(emptyRow, 3).Value = BERLstBox.Value
   Cells(emptyRow, 5).Value = SNKLstBox.Value
End Sub

推荐答案

您可以使用列表属性：

With SRCLstBox
    Cells(emptyRow, 1).Value = .Value
    Cells(emptyRow, 2).Value = .List(.ListIndex, 1)
End With
With BERLstBox
    Cells(emptyRow, 3).Value = .Value
    Cells(emptyRow, 4).Value = .List(.ListIndex, 1)
End With
With SNKLstBox
    Cells(emptyRow, 5).Value = .Value
    Cells(emptyRow, 6).Value = .List(.ListIndex, 1)
End With

这篇关于Excel VBA导出2列列表框以分离工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！