Click here to Skip to main content
15,878,230 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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

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

Thanks.
Posted
Updated 21-Nov-11 9:46am
v2

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

VB
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


[EDIT]Tags of "code" was added - Losmac[/EDIT]
 
Share this answer
 
v2
Comments
YuDi Sri 22-Nov-11 10:41am    
hey santoshkvijayan,

hope you remember my previous question , this is a continuos part of first question. hope ur solution works :).

ny suggestion please provide
santhoshkvijayan 22-Nov-11 12:46pm    
Let me know if u get success
santhoshkvijayan 22-Nov-11 12:46pm    
Let me know if u get success
Maciej Los 22-Nov-11 14:30pm    
The lines below:
Worksheets("Sheet1").Activate
Worksheets("Sheet1").Cells(Rowi, 2)
belongs to bad practice of programming.
Where is the part of code to catch errors? -> ErrorHandler.

Ad. 1) Use Activate() method only if you really want to activate sheet.
Ad. 2) Use object variables rather then reference: Object.Object.
Take a look at my solution.
As you wish...
VB
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
 
Share this answer
 
v2
Comments
YuDi Sri 21-Nov-11 15:11pm    
request you to read question one more time... where i have mentioned that if i will select data from combo box 1. data has to be shown related to combobox 1 which is available on sheet 1 shld be shown in a tabular form or in grid.
Maciej Los 21-Nov-11 15:42pm    
OK, i have made changes. Place on the form: ComboBox1, CmdFilter (Button) and ListBox1.
How it works?
Select value from Combobox and push the Filter button. ListBox will be filled with the data corresponded to ComboBox1 value.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900