VBA自学应用（10）——给组合框或列表框添加元素

一、RowSource属性添加元素

Private Sub UserForm_Initialize()
    Dim i As Long
    i = Sheet5.Cells(Rows.Count, 1).End(3).Row
    ListBox1.RowSource = "产品表!a1:a" & i
End Sub
	ListBox1.ColumnHeads = True

或者是

Private Sub UserForm_Initialize()
    Dim i As Long
    i = Sheet5.Cells(Rows.Count, 1).End(3).Row
    ListBox1.RowSource = Sheet5.Range("a1:a" & i).Address(External:=True)
    ListBox1.ColumnHeads = True
End Sub

• 如果rowsource属性指定的工作表区域不是活动工作表，那么address属性的external参数是必须的，表示外部引用。
• 只有rowsource才能显示标题，其他additem、list或column赋值的listbox无法显示标题

二、为工作表中的ActiveX控件中的列表框添加元素

Private Sub ListBox1_GotFocus()
    Dim i As Long
    i = Sheet5.Cells(Rows.Count, 1).End(3).Row
    Me.ListBox1.ListFillRange = "产品表!a1:a" & i
End Sub

三、使用list属性添加元素

Private Sub UserForm_Initialize()
	Dim i As Long, arr
    i = Sheet5.Cells(Rows.Count, 1).End(3).Row
    arr = Sheet5.Range("a1:b" & i)
    ListBox1.List = arr
End Sub

• list用法——list(row,column),row是从0开始到listbox1.listcount-1,column是从0开始到listbox1.columncount-1
• ListBox1.List = arr也可以写成ListBox1.List = Range(“a1”).Value

四、使用additem方法添加列表项

Private Sub UserForm_Initialize()
	Dim i As Long
	For i = 1 To Sheet5.Cells(Rows.Count, 1).End(3).Row
        ListBox1.AddItem Sheet5.Cells(i, 1)
    Next
End Sub

五、列表框的其他属性

Private Sub UserForm_Initialize()
    Dim i As Long
    i = Sheet5.Cells(Rows.Count, 1).End(3).Row - 1
    With ListBox1
        .BackColor = RGB(0, 255, 0)
        .BorderColor = 255
        .BorderStyle = fmBorderStyleNone
        .ColumnCount = 2
        .ColumnWidths = .Width / 2
        .ColumnHeads = True
        .RowSource = "产品表!a1:a" & i
        .ControlTipText = "欢迎录入"
        .Enabled = True
        With .Font
            .Size = 10
            .Bold = True
            .Italic = False
            .Underline = True
        End With
        .ForeColor = 38
        .Height = 500
        .Width = 500
        .Left = 0
        .Top = 0
        .ListStyle = fmListStyleOption
        .ListIndex = 0
        .MultiSelect = fmMultiSelectExtended
        .TextAlign = fmTextAlignCenter
    End With
End Sub

ListBox基本属性和方法详情