Fill a ListView on a UserForm

[Ferreira1456]

The following code fills a ListView on a UserForm with data from an Excel worksheet.
I am trying to search in the ListView.
I would like to search the textbox that is in the 3rd column sheet1 (column C)."txtNomeCliente"


Below, is the code that I am trying to adapt for my specific needs.

Private Sub UserForm_Initialize()
        With Me.ListView1        .Gridlines = True
        .View = lvwReport
        .FullRowSelect = True 
        .HideColumnHeaders = False
        
    End With
Call LoadListView
End Sub
'-----------------------------------------------------------------------
       Private Sub LoadListView()


    Dim wksSource As Worksheet
    Dim rngData As Range
    Dim rngCell As Range
    Dim LstItem As listItem
    Dim RowCount As Long
    Dim ColCount As Long
    Dim i As Long
    Dim j As Long
    
     Set wksSource = Worksheets("Cliente")
    
     Set rngData = wksSource.Range("A2").CurrentRegion
   
    For Each rngCell In rngData.Rows(1).Cells
        Me.ListView1.ColumnHeaders.Add Text:=rngCell.Value, Width:=10
    Next rngCell
    
    'Count the number of rows in the source range
    RowCount = rngData.Rows.Count
    
    'Count the number of columns in the source range
    ColCount = rngData.Columns.Count
    
    'Fill the ListView
    For i = 3 To RowCount
        Set LstItem = Me.ListView1.ListItems.Add(Text:=rngData(i, 1).Value)
        For j = 2 To ColCount
            LstItem.ListSubItems.Add Text:=rngData(i, j).Value
        Next j
    Next i


End Sub
'-----------------------------------------------------------------------
Private Sub cmdSearch_Click()
Dim itmx As listItem
Dim myindex As Integer
Set itmx = ListView1.FindItem(txtNomeCliente.Text, lvwText, , lvwPartial)




If itmx Is Nothing Then
MsgBox "Not Record", vbCritical
Else
ListView1.ListItems(itmx.Index).Selected = True
End If




txtClienteID.Text = Me.ListView1.SelectedItem
myindex = Me.ListView1.SelectedItem.Index
    txtContaNumero.Text = Me.ListView1.ListItems.Item(myindex).SubItems(1)
    txtClienteID.Text = Me.ListView1.ListItems.Item(myindex).SubItems(2)
    txtEnderecoCliente.Text = Me.ListView1.ListItems.Item(myindex).SubItems(3)
    End Sub

Could you please show me what I am doing wrong? Any suggestions you have, are very welcome.

Thank you very much.

Carlos Ferreira

 

[Domenic]

Your question is unclear, but I do see that there's a problem with your code. If a match isn't found, a message box is displayed, but then it continues, and then it incorrectly returns data based on the index of the already selected item.

Therefore, I've amended your code so that when there's no match it clears the relevant textboxes, displays the message, and then exits the sub. You may want to change it, depending on your requirements. Also, when there's a match, I've made sure that the item/row in the listview gets highlighted, and that it scrolls to the item, if hidden.

Private Sub cmdSearch_Click()
    Dim itmx As ListItem
    
    Set itmx = ListView1.FindItem(txtNomeCliente.Text, lvwText, , lvwPartial)
    
    If itmx Is Nothing Then
        txtContaNumero.Text = ""
        txtClienteID.Text = ""
        txtEnderecoCliente.Text = ""
        MsgBox "Not Record", vbCritical
        Exit Sub
    End If
    
    ListView1.SetFocus 'must have focus to highlight item
    itmx.Selected = True
    itmx.EnsureVisible 'scrolls to item, if hidden
    
    txtContaNumero.Text = itmx.SubItems(1)
    txtClienteID.Text = itmx.SubItems(2)
    txtEnderecoCliente.Text = itmx.SubItems(3)
End Sub

Does this help?

 

[Ferreira1456]

Dear Domenic
No matter what letter I type in txtSearch, I always get the first row of the ListView.
and also txtId shows the txtclientname value and the txtclientname is empty.

[FONT=&quot]Thank you very much.[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Carlos Ferreira[/FONT]

 

[Domenic]

As it stands, the code searches for the client name entered in txtNomeCliente. Is this not what you want? Or do you in fact want to search for text entered in txtSearch? And, if so, do you want to search the listitems or the subitems?

 

[DanteAmor]

The lvwPartial argument is a partial search, if you want a full search then change to lvwWhole

[COLOR=#333333]Set itmx = ListView1.FindItem(txtNomeCliente.Text, lvwText, , [/COLOR][COLOR=#0000ff]lvwPartial[/COLOR][COLOR=#333333])[/COLOR]

By:

Set itmx = ListView1.FindItem(txtNomeCliente.Text, lvwText, , [COLOR=#0000ff]lvwWhole[/COLOR])

That way you will only find the exact text

 

[Ferreira1456]

Dear Dante Amor
I did this change and now I just get "MsgBox"

Thank you very much

 

[DanteAmor]

But if in the textbox you put an exact text of some item in the first column of the listview, select the record?
That works for me.

 

[Domenic]

Just in case you missed it, I did post a reply earlier on this thread on April 14th.

 

[Ferreira1456]

Hi Dante
You are correct however I need to search on 3th Column of ListView not on the first column. How can I do that?
thank you again

 

[DanteAmor]

That's exactly what [COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=4]#4[/URL] 9644E]Domenic [/COLOR]asked you in post #4 .
From what I found, you can not search in a specific column, or search in column 1 or all.
To search in all:

    'To deselect all records
    For i = 1 To ListView1.ListItems.Count
        ListView1.ListItems(i).Selected = False
    Next


    Set itmx = ListView1.FindItem(txtNomeCliente.Text, [B][COLOR=#0000ff]1[/COLOR][/B], , lvwWhole)