English 中文(简体)
如何从一个区域(多行和列)向 VBA 列表框中填入数据
原标题:How to populate data from a range (multiple rows and columns) to listbox with VBA
  • 时间:2012-05-26 03:10:38
  •  标签:
  • excel
  • vba

我对如何将数据从带多个列和行的数据放入列表框有困难。

Assume I have a range rng which multiple columns and rows I tried:

""https://i.sstatic.net/8g0tw.jpg" alt="此处输入图像描述"/ >

如果我尝试添加项目rng(i,j),那么一切都将列在一栏。

我也尝试了.清单,但也没有用。

最佳回答

这就是你在尝试的吗?

Option Explicit

Private Sub CommandButton1_Click()
    Dim ws As Worksheet
    Dim rng As Range
    Dim i As Long, j As Long, rw As Long
    Dim Myarray() As String

     ~~> Change your sheetname here
    Set ws = Sheets("Sheet1")

     ~~> Set you relevant range here
    Set rng = ws.Range("A1:E5")

    With Me.ListBox1
        .Clear
        .ColumnHeads = False
        .ColumnCount = rng.Columns.Count

        ReDim Myarray(rng.Rows.Count, rng.Columns.Count)

        rw = 0

        For i = 1 To rng.Rows.Count
            For j = 0 To rng.Columns.Count
                Myarray(rw, j) = rng.Cells(i, j + 1)
            Next
            rw = rw + 1
        Next

        .List = Myarray

         ~~> Set the widths of the column here. Ex: For 5 Columns
         ~~> Change as Applicable        
        .ColumnWidths = "50;50;50;50;50"
        .TopIndex = 0
    End With
End Sub

< 强势 > SNAPSHOT

""https://i.sstatic.net/adJsm.png" alt="此处的内置图像描述"/ >

问题回答

我想你应该想把3个柱子写出来

Dim currRange As Range
Dim i As Integer
With Selection
For Each currRange In Range("yourRange")
        i = i + 1
        If i = 1 Then .AddItem cell.Value
        If i = 2 Then .List(.ListCount - 1, 1) = "1"
        If i = 3 Then
            .List(.ListCount - 1, 2) = cell.Offset(0, 2).Value
            i = 0
        End If
Next
End With

我猜你们有3个纵队

您可以在列表框中添加项目

ActiveSheet.Shapes("lstSample").Select

Dim currRange As Range
With Selection
    For Each currRange In Range("yourRange")
        .AddItem currRange.Value
    Next
End With

每一 行 列 、 各 在 你 范围 之 内 、 每 行 、 各 列 、 各 相 迭 。





相关问题
import of excel in SQL imports NULL lines

I have a stored procedure that imports differently formatted workbooks into a database table, does work on them then drops the table. Here is the populating query. SELECT IDENTITY(INT,1,1) AS ID ...

Connecting to Oracle 10g with ODBC from Excel VBA

The following code works. the connection opens fine but recordset.recordCount always returns -1 when there is data in the table. ANd If I try to call any methods/properties on recordset it crashes ...

Excel date to Unix timestamp

Does anyone know how to convert an Excel date to a correct Unix timestamp?

C# GemBox Excel Import Error

I am trying to import an excel file into a data table using GemBox and I keep getting this error: Invalid data value when extracting to DataTable at SourceRowIndex: 1, and SourceColumnIndex: 1. As ...

Importing from excel "applications" using SSIS

I am looking for any tips or resources on importing from excel into a SQL database, but specifically when the information is NOT in column and row format. I am currently doing some pre-development ...

热门标签