How to show 2 rows in ListBox

Question

How to show 2 rows in ListBox

Navigation

#1 by (2 votes)
#2 by (0 votes)

4

It's a simple question, but I researched and could not find something specific to my case. The code below corresponds to searching for data in a table, but when there is more than 1 corresponding value, it replaces the last found value. What I would like is for it to add a line as in the example below:

** The example has no relation to the code

Table

AAA 111 222 333

AAA 111 555 777

Search: AAA

Current result:

AAA 111 555 777 (replaced by AAA 222 333)

What I would like:

AAA 111 222 333

AAA 111 555 777

Private Sub Pesquisar_Click()
    Dim nApol As String
    Dim nApolBanco As String
    Dim nlin As Long
    Dim Linha As Range


    Application.ScreenUpdating = False

    If Len(Me.TextBox1.Value) > 3 Then

        nApol = TextBox1.Value + TextBox2.Value + TextBox3.Value

        For nlin = 2 To Sheets("Banco de Dados").Cells(Rows.Count, "B").End(xlUp).Row

            nApolBanco = Cells(nlin, "B")

            If UCase(nApolBanco) Like nApol Then
                MsgBox ("Found")
                ListBox1.ColumnCount = 14

                ListBox1.RowSource = Range(Cells(nlin, "A"), Cells(nlin, "T")).Address

            End If

        Next
    Else
        MsgBox ("Não encontrado")
    End If

End Sub

vba listbox

asked by anonymous 17.10.2018 / 21:06

2 answers

2

Try this:

Private Sub Pesquisar_Click()
    Dim nApol As String
    Dim nApolBanco As String
    Dim nlin As Long
    Dim Linha As Range
    Dim arrayItems()

    Application.ScreenUpdating = False

    If Len(Me.TextBox1.Value) > 3 Then

        nApol = TextBox1.Value + TextBox2.Value + TextBox3.Value

        ListBox1.ColumnCount = Range("T:T").Column

        RowCount = Sheets("Banco de Dados").Cells(Rows.Count, "B").End(xlUp).Row

        ReDim arrayItems(1 To RowCount, 1 To Range("T:T").Column)

        lin = 1

        For nlin = 2 To RowCount

            nApolBanco = Cells(nlin, "B")

            If UCase(nApolBanco) Like nApol Then

                ListBox1.AddItem
                For coluna = 1 To Range("T:T").Column
                    arrayItems(lin, coluna) = Cells(nlin, coluna).Value
                Next coluna
                lin = lin + 1

            End If
        Next

        ListBox1.List = arrayItems()

    Else
        MsgBox ("Não encontrado")
    End If

End Sub

You create an array with the data and at the same time add lines in the ListBox with ListBox1.AddItem and then add the array data.

18.10.2018 / 15:32

How to create an optional parameter in ADVPL? How do the visual components of Delphi 2010 follow the visual pattern of Windows?

score 0 · Accepted Answer

Solution

A noncontiguous Range needs to be created with each Range desired, and in the end populates the ListBox with this data. Because with ListBox.RowSource , the listbox is filled with the desired interval. Zeroing every time this function is called.

And arrays were used to search for the nApol value, because it decreases interaction with the Excel Spreadsheet and is faster.

Code

Dim nApol As String
Dim nApolBanco As String
Dim nlin As Long
Dim Linha As Range
Dim ListaApol As Variant
Dim rng As Range, nova_range As Range
Dim BD As Worksheet
Set BD = ThisWorkbook.Worksheets("Banco de Dados")

Application.ScreenUpdating = False
Me.ListBox1.ColumnCount = 14
If Len(Me.TextBox1.Value) > 3 Then
    nApol = TextBox1.Value + TextBox2.Value + TextBox3.Value
    With BD
        ListaApol = .Range("B2:B" & .Range("B" & .Rows.Count).End(xlUp).Row).Value2
        For nlin = LBound(ListaApol) To UBound(ListaApol)
            nApolBanco = ListaApol(nlin, 1)
            If UCase(nApolBanco) Like nApol Then
                MsgBox ("Found")
                If rng Is Nothing Then
                    Set rng = .Range(.Cells(nlin + 1, "A"), .Cells(nlin + 1, "T"))
                Else
                    Set nova_rng = .Range(.Cells(nlin + 1, "A"), .Cells(nlin + 1, "T"))
                    Set rng = Union(rng, nova_rng)
                End If
            End If
        Next nlin
        Me.ListBox1.RowSource = rng.Address
    End With
Else
    MsgBox ("Não encontrado")
End If
Application.ScreenUpdating = True

Steps

Creates the ListApol array with the values in column B

Performs the loop from the lowest index value of ListApol (Lbound) to the largest value (Ubound) with the variable nlin

Find the corresponding values

If it is the first time a value is found, that is, rng is Nothing, rng is equal to the desired range. Otherwise, create a non-contiguous range with all Ranges that have the desired value

Populate the ListBox with the noncontiguous interval values rng

The ListBox.AddItem function has a limit of 10 columns, as 14 columns are used, the non-contiguous range solution is the most recommended.