从组合框中选择数据时如何显示信息列表或表格 [英] How to show a list of information or a table when selecting data from combo box

问题描述

如何以表格形式显示信息列表，该列表必须处于启用模式，以便用户可以从表格中复制信息.

例如:如果我从combobox1中选择条目A，则与sheet1中的A相关的信息将显示在表格中.

Sheet1基本数据如下:

How to show a list of information in form of table which has to be in enable mode so that a user can copy information from table.

for example: If I select entry A from combobox1 then information related to A from sheet1 will show in a table.

Sheet1 base data is as follows:

a   xxx-12    pink    xxx-13    yellow
b   xxx-25    black   xxx-01    white
c   xxx-95    red     xxx-58    green
d   xxx-11    cyan    xxx-77    brown
e   xxx-78    blue    xxx-54    orange

如果我从combobox1中选择数据a，b，c，d和e，则在表的情况下应显示以上相关信息.

谢谢.

If I select data a, b,c,d and e from combobox1 then above related information should show in case of table.

Thanks.

推荐答案

YiDi Sri，
希望对您有帮助，使用
组合框(CBox)和
列表视图(ListView)

Hi YuDi Sri,
hope this helps you, used a
Combo box(CBox) and
List View(ListView)

Private Sub UserForm_Initialize()
    Worksheets("Sheet1").Activate
    If WorksheetFunction.CountA(Cells) > 0 Then
    ' to fetch the total used row's in sheet 1
        sv_Sheet1RowCount = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End If
    If sv_Sheet1RowCount > 1 Then
        CBox1.Clear
        For Rowi = 1 To sv_Sheet1RowCount
            CBox1.AddItem Worksheets("Sheet1").Cells(Rowi, 1).Value
        Next
    End If
End Sub

Private Sub CBox1_Change()
    Worksheets("Sheet1").Activate
    If WorksheetFunction.CountA(Cells) > 0 Then
    ' to fetch the total used row's in sheet 1
        sv_Sheet1RowCount = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End If
    With ListView
        ' Clear every time a new item is selected
        .ListItems.Clear
        ' Select the item and subitems when selection is made.
        .FullRowSelect = True
        ' Display grid lines.
        .Gridlines = True
        'Display Column Header
        .ColumnHeaders.Add Text:="a"
        .ColumnHeaders.Add Text:="b"
        .ColumnHeaders.Add Text:="c"
        .ColumnHeaders.Add Text:="d"
        .ColumnHeaders.Add Text:="e"
        .View = lvwReport
    End With
    If sv_Sheet1RowCount > 1 Then
        For Rowi = 1 To sv_Sheet1RowCount
            If CBox1.Text = Worksheets("Sheet1").Cells(Rowi, 1).Value Then
                With ListView
                    .ListItems.Add , , Worksheets("Sheet1").Cells(Rowi, 1).Value
                    .ListItems(1).SubItems(1) = Worksheets("Sheet1").Cells(Rowi, 2).Value
                    .ListItems(1).SubItems(2) = Worksheets("Sheet1").Cells(Rowi, 3).Value
                    .ListItems(1).SubItems(3) = Worksheets("Sheet1").Cells(Rowi, 4).Value
                    .ListItems(1).SubItems(4) = Worksheets("Sheet1").Cells(Rowi, 5).Value
                End With
                Exit For
            End If
        Next
    End If
End Sub

添加了代码"标签-Losmac [/EDIT]

Tags of "code" was added - Losmac[/EDIT]

如你所愿...

As you wish...

Option Explicit 'you must declare variables!

Private Sub CmdFilter_Click()
Dim wsh As Worksheet 'declare object variable of worksheet
Dim i As Integer 'declare integer variable
Dim cmbval As String

On Error GoTo Err_CmdFilter_Click 'on error goto error-handler

Set wsh = ThisWorkbook.Worksheets(1) 'set variable

cmbval = Me.ComboBox1.Value
Me.ListBox1.ColumnCount = 5 'set the count of columns

i = 1 'start from row no. 1
Do While wsh.Range("A" & i) <> ""
    If wsh.Range("A" & i) = cmbval Then
        With Me.ListBox1
            .AddItem "" 'add empty string
            .Column(0, .ListCount - 1) = wsh.Range("A" & i) 'column 1, row i
            .Column(1, .ListCount - 1) = wsh.Range("B" & i) 'column 2, row i
            .Column(2, .ListCount - 1) = wsh.Range("C" & i) 'column 3, row i
            .Column(3, .ListCount - 1) = wsh.Range("D" & i) 'column 4, row i
            .Column(4, .ListCount - 1) = wsh.Range("E" & i) 'column 5, row i
        End With
        Exit Do
    End If
    i = i + 1
Loop
 
Exit_CmdFilter_Click:
    On Error Resume Next 'ignore errors
    Set wsh = Nothing 'destroy variable
    Exit Sub
    
Err_CmdFilter_Click:
    MsgBox Err.Description, vbExclamation, Err.Number
    Resume Exit_CmdFilter_Click
End Sub

Private Sub UserForm_Initialize()
Dim wsh As Worksheet 'declare object variable of worksheet
Dim i As Integer 'declare integer variable

On Error GoTo Err_UserForm_Initialize 'on error goto error-handler

Set wsh = ThisWorkbook.Worksheets(1) 'set variable

i = 1 'start from row no. 1
Do While wsh.Range("A" & i) <> ""
    Me.ComboBox1.AddItem wsh.Range("A" & i)
    i = i + 1
Loop
 
Exit_UserForm_Initialize:
    On Error Resume Next 'ignore errors
    Set wsh = Nothing 'destroy variable
    Exit Sub
    
Err_UserForm_Initialize:
    MsgBox Err.Description, vbExclamation, Err.Number
    Resume Exit_UserForm_Initialize
End Sub

这篇关于从组合框中选择数据时如何显示信息列表或表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！