从列标题填充列表框的标题 [英] Populating header of Listbox from column header

问题描述

1.背景与目的
我正在创建新的用户窗体，以显示Excel表("DATA")中的数据，其中包含多列表格("Tab1")，如下图所示.

1. Background & purpose
I'm creating new userform to display data from the Excel sheet("DATA") with table ("Tab1") of multi-columns like below picture.

在我的表单("TaskMngUserForm")中，单击任务列表"按钮后，来自Tab1的所有数据将显示在Listbox1上，如下所示:

In my form ("TaskMngUserForm"), after clicking on "Task List" button, all data from Tab1 will be displayed on Listbox1 as follows:

• Tab1中的列标题将作为标题显示在Listbox1上.
• Tab1中从第二行到末尾的数据将在与每个列对应的Listbox1上显示.

我还要添加一个事件"Listbox1_Click()"，该事件将返回与所选索引相对应的数据"工作表行，该行是从所选ListBox1行的第二列获取的.

Also I'm adding an event for action "Listbox1_Click()" that:returns "Data" sheet row corresponding to the selected Index, which is got from the 2nd column of the selected ListBox1 row.

2.使用代码
这是我使用的代码.

2. Using code
Here is my using code.

'4. Event for "Tasks List" button
Private Sub Button_TaskList_Click()

ListBox1.ColumnWidths = "20;100;80;100;60;100;80;80;80;200;200;200"
ListBox1.ColumnCount = 12

    With ListBox1

            '.ColumnHeads = True
            .List = Sheets("DATA").Range("B2").CurrentRegion.Value
            .RemoveItem (0)
            .ColumnCount = Sheets("DATA").Cells(2, 2).CurrentRegion.Columns.Count


    End With

Application.ScreenUpdating = True
Label25.Caption = "Total Tasks: " & (Worksheets("DATA").UsedRange.Rows.Count - 1)

End Sub

'6. Event for "Click Listbox" Action
Private Sub ListBox1_Click()

Dim strAddress As String
Dim dataSht As Worksheet

With Me
        If .ListBox1.ListIndex <> -1 Then
        Set dataSht = Sheets("DATA")

        If IsNull(Me.ListBox1.Value) Then
            Call MsgBox("You are selecting on blank row item" & vbNewLine & "Be careful!", vbInformation, "Notification")
            Button_TaskList_Click

        Else
           strAddress = GetIndexRow(.ListBox1.List(.ListBox1.ListIndex, 0), dataSht.Columns("A"))

        '<~~  GetIndexRow returns "Data" sheet row corresponding to the selected Index, which is got from the 2nd column of the selected ListBox row


        TaskMngUserForm.txtIndex.Value = dataSht.Range("A" & strAddress).Value
        TaskMngUserForm.cmbSource.Value = dataSht.Range("B" & strAddress).Value
        TaskMngUserForm.cmbType.Value = dataSht.Range("C" & strAddress).Value
        TaskMngUserForm.cmbCategory.Value = dataSht.Range("D" & strAddress).Value
        TaskMngUserForm.cmbPriority.Value = dataSht.Range("E" & strAddress).Value
        TaskMngUserForm.cmbTaskOwner.Value = dataSht.Range("F" & strAddress).Value
        TaskMngUserForm.cmbStatus.Value = dataSht.Range("G" & strAddress).Value
        TaskMngUserForm.txtOpenDate.Value = dataSht.Range("H" & strAddress).Value
        TaskMngUserForm.txtCloseDate.Value = dataSht.Range("I" & strAddress).Value
        TaskMngUserForm.txtSubject.Value = dataSht.Range("J" & strAddress).Value
        TaskMngUserForm.txtDescription.Value = dataSht.Range("K" & strAddress).Value
        TaskMngUserForm.txtSolution.Value = dataSht.Range("L" & strAddress).Value


        End If
'       TaskMngUserForm.Show
        End If
 End With

Application.ScreenUpdating = True
Label25.Caption = "Check in Task.No:  " & txtIndex.Text

End Sub

3.问题与疑问

我可以将数据从Tab1加载到Listbox1，但不能将列标题从Tab1填充到Listbox1中的Header.

I can load data from Tab1 to Listbox1 but I cannot populate column header from Tab1 to Header in Listbox1.

我的代码有什么问题吗? 任何帮助或建议，将不胜感激. 非常感谢您的关注.

Is there anything wrong in my code? Any helps or advice would be highly appreciated. Thank you so much for your attention.

推荐答案

我最近编写了一个包含标题的UserForm，我可以为您解答.

I recently coded a UserForm to include headers and I can answer this for you.

只有一种方法可以填充ListBox上的标题，即使用ListBox1.RowSource属性时.在RowSource属性中，您必须分配一个范围，这是一个示例:

There is only 1 way to populate the headers on a ListBox and that is when you use the ListBox1.RowSource property. In the RowSource property you must assign a Range, this is one example:

UserForm1.ListBox1.RowSource = "Sheet1!A2:H20"

这将填充ListBox1上从A2到H20的数据，如果ListBox1 ColumnHeaders属性设置为True，则Sheet1！A1:H1上的所有内容都将成为标题.这是唯一的方法.

This will populate the data from A2 to H20 on ListBox1 and if the ListBox1 ColumnHeaders property is set to True then anything on Sheet1!A1:H1 will become the headers. This is the only way.

许多用户告诉您只在ListBox顶部添加文本标签以使其更容易的原因是，当您使用RowSource进行列表操作时，必须始终找出Range上使用的最后一个Row是什么.您分配范围，以避免ListBox上的空行.这意味着如果您有20行数据，并且分配的范围包含50行，则列表框将填充50行，最后30行将为空.

The reason that many users will tell you to just add text labels on top of the ListBox to make it easier is because when you do your list using RowSource, you must always find out what is the last Row used on your Range before you assign the Range to avoid Empty lines on your ListBox. What this means is that if you have 20 rows of data and you assign a range that contains 50 rows, the listbox will populate 50 rows, the last 30 will be empty.

这篇关于从列标题填充列表框的标题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！