列表框填充特定行 [英] Listbox populate with specific rows
本文介绍了列表框填充特定行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我只想从数据库中填充特定行的vba列表框. 这就是我所得到的.
I want to populate a vba listbox from a database only with specific rows. This is what I got.
Private Sub UserForm_Initialize()
Hoja2.Activate
ListBox1.ColumnCount = 5
ListBox1.ColumnWidths = "70;90;90;90;70"
ListBox1.AddItem "FIRST NAME"
ListBox1.List(0, 1) = "LAST NAME"
ListBox1.List(0, 2) = "LAST NAME 2"
ListBox1.List(0, 3) = "BORN DATE"
ListBox1.List(0, 4) = "AGE"
Dim seguimiento As Integer
Dim i As Integer
seguimiento = Application.WorksheetFunction.CountA(Range("b:b"))
For i = 1 To seguimiento
If Cells(i, 20) = "" Then
ListBox1.AddItem Cells(i, 3)
Else
End If
Next i
End Sub`
推荐答案
实际上很难从您的问题中了解列表框填充条件
it's actually hard to understand listbox filling criteria from your question
假设您要用单元格填充列表框:
assuming you want to fill listbox with cells:
- 在列"T"单元格不为空的行中
- 扫描第2行到"B"列中最后一个非空行的行
- 从"C"列到"G"列获取值
尝试以下代码:
Option Explicit
Private Sub UserForm_Initialize()
Dim seguimiento As Long, i As Long
Dim Data() As Variant '<--| use an array to store data to eventually fill ListBox list
Dim cell As Range
With Me.ListBox1
.ColumnCount = 5
.ColumnWidths = "70;90;90;90;70"
End With
i = 1
With Hoja2 '<--| refer to your worksheet
With .Range("T1:T" & .Cells(.Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeConstants) '<--| refer to non blank cells in its column "T" (i.e. with column index 20) from row 1 to last non blank one in column "B"
seguimiento = .Count '<--| count those non empty cells
ReDim Data(1 To seguimiento + 1, 1 To Me.ListBox1.ColumnCount) '<--| redim data array rows accordingly (while setting columns to 5)
'fill data array first row with "headers"
Data(1, 1) = "FIRST NAME"
Data(1, 2) = "LAST NAME"
Data(1, 3) = "LAST NAME 2"
Data(1, 4) = "BORN DATE"
Data(1, 5) = "AGE"
' loop through referred non blank cells and fill subsequent data array rows from corresponding cells in columns "C" through "G"
For Each cell In .Cells
i = i + 1
With cell
Data(i, 1) = .Offset(, -17) '<--| this refers to a cell in the same row as the current cell and in column "C", being offseted 17 columns back from current cell
Data(i, 2) = .Offset(, -16) '<--| this refers to a cell in the same row as the current cell and in column "D", being offseted 16 columns back from current cell
Data(i, 3) = .Offset(, -15) ' etc...
Data(i, 4) = .Offset(, -14) ' etc...
Data(i, 5) = .Offset(, -13) ' etc...
End With
Next cell
End With
End With
ListBox1.List = Data '<---| finally, fill listbox list with data array
End Sub
这篇关于列表框填充特定行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文