Listbox - defining columns

[jeribeiro]

I have this listbox to pull the information and filter it.

However, I put information from 3 columns starting with column B, and defined that it would pull through the offset the information of a column before and a column after.

It's working... but I'd like to filter the information from column F and that the information from column A and column H also appear in the listbox.

So what I'm looking for is for my listbox to appear as follows:

Code (Column A) | City with filter option (Column F) | Country (Column H)

I really appreciate anyone who can help me :D

listboxtest.xlsm
	A	B	C	D	E	F	G	H
1	Codigo	Nome	Telefone	Endereco	Bairro	Cidade	UF	Pais
2	1	Joaquim	7654821455	Jardim Santana 12	Joaquim de fora	Nilopolis	AM	Brasil
3	2	Jessica	7654821465	Jardim Santana 55	Felicidade do Pai	Felicidade do Pai	PA	Mexico
4	3	Bernardo	7654821475	Jardim Santana 98	Majuçara	Cajueiro do Norte	RJ	USA
5	4	Julia	7654821485	Jardim Santana 141	Cajueiro do Norte	Cajueiro do Norte	RJ	Brasil
6	5	Caio	7654821495	Jardim Santana 184	Morango do Nordeste	Majuçara	SP	Africa
7	6	Dominique	7654821505	Jardim Santana 227	Brasil	Majuçara	MG	Franca
8	7	Karina	7654821515	Jardim Santana 270	Estados separados	Felicidade do Pai	ES	USA
9	8	Matheus	7654821525	Jardim Santana 313	Estados unidos	Morango do Nordeste	TO	Japao
10	9	Pedro	7654821535	Jardim Santana 356	Jardim Santana 270	Cajueiro do Norte	RS	Brasil
11	10	Raissa	7654821545	Jardim Santana 399	Brasil	Cajueiro do Norte	RE	Brasil
Teste1

code:

Option Explicit

Dim wPlan As Worksheet



Private Sub cmdfechar_click()
Unload Me

End Sub


Private Sub listaClientes_Dblclick(ByVal Cancel As MSForms.ReturnBoolean)
   
On Error Resume Next

Dim n As Long
Dim ncliente As String

n = Format(Me.listaClientes.List(listaClientes.ListIndex, 0), "0")
ncliente = Me.listaClientes.List(listaClientes.ListIndex, 1)


Cadastroclientes.txtcodigo.Value = n

Unload Me


End Sub





Private Sub UserForm_Activate()
Call CriaCabecalhoLb(Me.listaClientes, Me.lbCab, Array("Cód.", "Cliente", "Celular"))

End Sub

Private Sub UserForm_Initialize()

    Dim rg      As Range
    Dim linf    As Integer
    Dim Cor01   As Variant
    Dim Cor02   As Variant
    Dim Cor03   As Variant
    Dim vLin    As Integer

    Cor01 = RGB(35, 207, 222) 'RGB(253, 6, 100)
    Cor02 = RGB(43, 46, 51) 'RGB(231, 232, 237)

    Set wPlan = Planilha1
    Set wPlan = Application.Worksheets("Teste1")
   
   
     vLin = Application.WorksheetFunction.CountA(wPlan.Range("B:B"))
       
        Set rg = wPlan.Range("B2:B" & vLin)
       
  
    listaClientes.ForeColor = Cor01
    txtCliente.ForeColor = Cor01
    listaClientes.BackColor = Cor02
   
    For linf = 1 To rg.Rows.Count
   
        With Me.listaClientes
       
            .ColumnWidths = "60;190;100"
            .ColumnCount = 3
            .AddItem Format(rg.Cells(linf, 0), "00000")
            .List(listaClientes.ListCount - 1, 1) = rg.Cells(linf, 1)
            .List(listaClientes.ListCount - 1, 2) = rg.Cells(linf, 2)
           
           
        End With
       
    Next
   
    Me.Contalbl.Caption = Me.listaClientes.ListCount & " clientes"

End Sub

Private Sub txtCliente_Change()

    Dim lstCli  As Range
    Dim vProc   As Range
    Dim vCod    As Range
    Dim vCel    As Range
    Dim vInic   As Range
    Dim vLin    As Integer
    Dim vteste  As Range
    Dim vtest   As Range
   
   
    On Error Resume Next
    Me.listaClientes.Clear
    If Len(Me.txtCliente) = 0 Then
        Call UserForm_Initialize
        lblPesq.Visible = True
    Else

    vLin = Application.WorksheetFunction.CountA(wPlan.Range("S:S"))
       
        Set lstCli = wPlan.Range("B2:B" & vLin)
        Set vProc = lstCli.Find(Me.txtCliente, , , xlPart)
        Set vCod = vProc.Offset(0, -1) 'valores do codigo, mudar a coluna para esquerda (0 = coluna S, 1 = coluna S - 1)
        Set vCel = vProc.Offset(0, 1) 'valores do celular, mudar a coluna para direita (0 = coluna S, 1 = coluna S + 1)
       
        lblPesq.Visible = False
       
        If Not vProc Is Nothing Then
       
            Set vInic = vProc
           
                Do
                    With Me.listaClientes

                        .ColumnWidths = "60;190;100"
                        .ColumnCount = 3
                        .AddItem Format(vCod, "00000")
                        .List(listaClientes.ListCount - 1, 1) = vProc
                        .List(listaClientes.ListCount - 1, 2) = vCel
                       
                       
                    End With
                   
                    Set vProc = lstCli.FindNext(vProc)
                    Set vCod = vProc.Offset(0, -1)
                    Set vCel = vProc.Offset(0, 1)
                   
                Loop Until vProc.Address = vInic.Address
           
        End If
        Me.Contalbl.Caption = Me.listaClientes.ListCount & " clientes"
    End If
   
End Sub



Public Sub CriaCabecalhoLb(LbPrincipal As MSForms.ListBox, LbCabecalho As MSForms.ListBox, cabecalho As Variant)
   
    With LbCabecalho ' cabecalho do userform

        'Iguala o numeros de colunas do ListBox Cabeçalho ao do ListBox Principal
        .ColumnCount = LbPrincipal.ColumnCount
        .ColumnWidths = LbPrincipal.ColumnWidths
       
        'Adiciona os elementos dos cabeçalhos
        .Clear
        .AddItem
        Dim i As Integer
        For i = 0 To UBound(cabecalho)
            .List(0, i) = cabecalho(i)
        Next i
       
        'Formata o visual
        .ZOrder (0)
        .Font.Size = 9
        .Font.Bold = True
        .SpecialEffect = fmSpecialEffectFlat
        .BackColor = RGB(35, 207, 222) 'RGB(229, 13, 90)
        .Height = 13
       
        'Alinha a posição e dimensões do ListBox Cabeçalho ao ListBox Principal
        .Width = LbPrincipal.Width
        .Left = LbPrincipal.Left
        .Top = LbPrincipal.Top - (.Height - 1)
   
    End With
   
    LbPrincipal.ZOrder (1)

End Sub

 

[Domenic]

To add Columns A, F, and H to your ListBox, try...

    With Me.listaClientes
    
        .ColumnWidths = "60;190;100"
        .ColumnCount = 3
        
        For linf = 1 To rg.Rows.Count
            .AddItem Format(rg.Cells(linf, 1), "00000")
            .List(.ListCount - 1, 1) = rg.Cells(linf, 6)
            .List(.ListCount - 1, 2) = rg.Cells(linf, 8)
        Next linf
      
    End With

Hope this helps!