jueves, 21 de diciembre de 2017

VBA: Filtrar en un ListBox

Veremos una manera rápida y ágil de mostrar elementos coincidentes similares en un ListBox a partir de un valor introducido en un TextBox.
Ya vimos algo similar en este post, pero en esta ocasión emplearemos las Array en nuestro código para agilizar el traspaso de información...

Nuestros datos en una hoja llamada 'Datos' y un rango A1:C11:

VBA: Filtrar en un ListBox



Nuestro formulario, reducido a lo necesario, tendrá:
1- un TextBox llamado 'TxtFiltro'
2- un ListBox llamdao 'ListFiltro'


Así pues añadimos la siguiente macro dentro de la ventana de código de nuestro UserForm en nuestro proyecto de VB:

Private Sub TxtFiltro_Change()
Dim myList() As Variant
Dim X As Long, Y As Long
Dim coincidencia As Boolean
coincidencia = False
Y = 0
'replicamos un filtro a visualizar sobre el ListBox
For X = 2 To Sheets("Datos").Range("A" & Rows.Count).End(xlUp).Row
    If InStr(1, UCase(Sheets("Datos").Range("B" & X).Value), UCase(Me.TxtFiltro.Value)) > 0 Then
        coincidencia = True
        ReDim Preserve myList(2, Y)
        'cargamos nuestra matriz....
        myList(0, Y) = Sheets("Datos").Range("A" & X).Text
        myList(1, Y) = Sheets("Datos").Range("B" & X).Text
        myList(2, Y) = Sheets("Datos").Range("C" & X).Text
        Y = Y + 1
    End If
Next
'si hemos localizado alguna coincidencia...
If coincidencia = True Then
    'mostramos datos coincidentes cargados en nuestra Array
    Me.ListFiltro.List = Application.Transpose(myList)
Else
    Me.ListFiltro.Clear
End If
End Sub
 
Private Sub UserForm_Activate()
Dim var As Variant
'cargamos en una Array el rango de datos...
With Sheets("Datos")
    var = .Range("A2:C" & .Range("A" & Rows.Count).End(xlUp).Row).Value
End With
With Me.ListFiltro
    .ColumnCount = 3 'indicamos número columnas
    .ColumnWidths = "50;50;50" 'determinamos ancho...
    If Not IsEmpty(var) Then
        'si hay datos vaciamos el ListBox
        .Clear
        .List = var
    End If
End With
End Sub



Además del uso de Array, y para compensar el desconocimiento a priori del número de registros coincidentes, hacemos de nuevo uso de la definición de tamaño de una Array, empleando ReDim Preserve, que nos habilita la opcíon de incorporar nuevos registros de manera 'ilimitada' manteniendo los datos cargados.
Esta técnica evita los conteos previos de coincidencias...


Al ejecutar el UserForm comprobaremos cómo a medida escribimos en el TextBox, el filtro se muestra directamente sobre los elementos del campo 'Concepto', tal como buscábamos.

23 comentarios:

  1. porque me pinta en vertical el resultado?

    ResponderEliminar
  2. cuando cambio para que filtre por la columna A no funcion, la columna B y C si funciona. Porque pasa eso

    ResponderEliminar
    Respuestas
    1. Hola,
      ¿qué tal estás?, un placer saludarte igualmente

      Disculpa pero no comprendo tus preguntas

      Saludos cordiales y quedo atento a tus aclaraciones

      Eliminar
    2. gracias por tu tiempo, o que pasa es que el cuando modifico el codigo para que filtre por la columna A, la informacion la muestra en vertical, caso contrario pasa cuando filtro por la columna B o C

      Eliminar
    3. A B C
      nombre cargo aplicación
      pedro G.I. MCSS
      pedro 1 G.A. CRM
      pedro 2 G.P. MCSS
      pedro G.I. MCSS
      pedro 3 G.A. CRM
      pedro 4 G.P. MCSS
      pedro G.I. MCSS
      pedro 5 G.A. CRM
      pedro 6 G.P. MCSS
      pedro G.I. MCSS

      si buscas Pedro 1
      te muestra asi :

      Pedro 1
      G.A.
      CRM
      cuando lo perfecto es
      Pedro 1 G.A. CRM

      Eliminar
    4. Hola,
      a veces pasa que si solo existe un registro aparece, como indicas, en 'vertical', tienes que gestionar para identificar primero el número de registros que existe y en caso que sea 1 ,aplicar un trasponse

      Saludos

      Eliminar
    5. ReDim Preserve myList(2, Y) ponle un 2

      Eliminar
  3. Este comentario ha sido eliminado por un administrador del blog.

    ResponderEliminar
  4. BUENAS TARDES,
    GRACIAS POR ESTOS APORTES QUE NOS DAS. TENGO UNA DUDA HICE UN LISTBOX QUE SE LLENA DE UNA HOJA DE EXCEL Y LE HICE UN BUSCAR CON UN TEXTBOX EN E EVENTO CHANCE, MI PROBLEMA SE ENCUENTRA QUE CUANDO LE ESCRIBO EN LA BÚSQUEDA ME BORRA LOS ENCABEZADOS.

    TE DEJO MI CODIGO

    numdatos = Hoja4.Range("B" & Rows.Count).End(xlUp).Row

    Hoja4.AutoFilterMode = False
    Me.Detalle = Clear
    Me.Detalle.RowSource = Clear

    y = 0

    For fila = 3 To numdatos
    descrip = Hoja4.Cells(fila, 2).Value

    If UCase(descrip) Like "*" & UCase(Me.txtNProyecto.Value) & "*" Then

    With Me.Detalle
    .ColumnHeads = True
    .AddItem
    .List(y, 0) = Hoja4.Cells(fila, 2).Value
    .List(y, 1) = Hoja4.Cells(fila, 3).Value
    .List(y, 2) = Hoja4.Cells(fila, 7).Value
    .List(y, 3) = Hoja4.Cells(fila, 9).Value
    .List(y, 4) = Hoja4.Cells(fila, 10).Value
    .List(y, 5) = Hoja4.Cells(fila, 11).Value
    .List(y, 6) = Hoja4.Cells(fila, 12).Value
    .List(y, 7) = Hoja4.Cells(fila, 15).Value



    End With

    Me.Detalle.AddItem
    Me.Detalle.List(y, 0) = Hoja4.Cells(fila, 2).Value
    Me.Detalle.List(y, 1) = Hoja4.Cells(fila, 3).Value
    Me.Detalle.List(y, 2) = Hoja4.Cells(fila, 7).Value
    Me.Detalle.List(y, 3) = Hoja4.Cells(fila, 9).Value
    Me.Detalle.List(y, 4) = Hoja4.Cells(fila, 10).Value
    Me.Detalle.List(y, 5) = Hoja4.Cells(fila, 11).Value
    Me.Detalle.List(y, 6) = Hoja4.Cells(fila, 12).Value
    Me.Detalle.List(y, 7) = Hoja4.Cells(fila, 15).Value

    y = y + 1

    Detalle.ColumnHeads = True
    End If

    Next

    AGRADECERIA QUE PUDIERAS AYUDARME

    ResponderEliminar
    Respuestas
    1. Hola,
      los encabezados solo aparecen cuando se carga datos desde un rango o tabla.. si procedes a cargar el listbox desde datos individuales (un array o celdas una a una o similar), dará igual que pongas True a mostrar encabezados... no lo cogerá.
      Lo normal es poner unas etiquetas por encima del listbox para mostrarlos... no muy fino, pero poco más se puede hacer

      Saludos

      Eliminar
  5. Buenas tarde, megustaria que tambien pudiera calcular subtotales gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      podrías acumular los resultados del campo en cuestión y añadirlo a un textbox por ejemplo...

      Saludos cordiales

      Eliminar
  6. Buenas.. tengo data de escolares en excel con mas de 1000 registros y he llegado a filtar por secciones (inicial, primaria y secundaria)la cual se elije cada seccion con optionbutton; al momento de elejir este se genera en un listbox1 los nombres de cada salon tanto de inicial, primaria y secundaria (porque lo tengo en ya clasificado en la hoja aparte de excel, al momento de elejir la seccion no me aparece los alumnos de las secciones solicitadas. como puedo hacer para que en listbox2 aparezca los alumnos de cada seccion, mucho le agradeceria me pudiese ayudar.. Saludos a la distancia.. y gracias de antemano..

    ResponderEliminar
    Respuestas
    1. Hola José Luis,
      el asunto clave es cargar el ListBox con el resultado del filtro

      Hay varias formas empleando .AddItem, otro es el método explicado en este post, etc...

      Según tengas los datos es más fácil uno u otro. En tu caso, si he entendido bien, emplearía lo explicado en este post, cargando el ListBox con la array resultante del filtro

      Saludos

      Eliminar
  7. Muy bien explicado, una consulta yo estoy trabando con un arrys en un lisbox de 11 columnas y las columna 10 va el promedio de los alumnos como lo puedo hacer con el mismo arrays me arroje la nota mayor aprobada, nota menor aprobada y nota máxima desaprobada he estado buscando pero encuentro un ejemplo de hacerlo con arrys en lisbox ojalá me pueda ayudar.

    ResponderEliminar
    Respuestas
    1. Hola!
      habría que ver esos datos y la array que has creado... pero lo mejor sería emplear las funciones de hoja de cálculo
      worksheetfunction.maxifs
      y
      worksheetfunction.minifs

      Espero te oriente la idea

      Saludos

      Eliminar
    2. Gracias Ismael ya logre que me salga esos cálculos, ahora una ultima consulta como se puede hacer que una vez que filtre por concepto después pueda filtrar por importe osea un filtro dentro de otro filtro, intente hacerlo con el codigo de filtro pero me reinicio todo. Ojala me pueda ayuda

      Eliminar
    3. Y parte otro detalle cuando se busca un solo registro, a la hora de mostrar los datos del filtro aparece en forma de columna y no como fila y cuando ya son mas de 2 registros alli si aparecen como filas todo normal a que se debe eso.

      Eliminar
    4. Hola,
      para aplicar diferentes criterios de filtro se suele recargar una y otra vez recorriendo toda la base de datos y aplicando condiciones simultáneas con el operador AND
      El segundo problema se resuelve contando previamente el número de registros devueltos y transponiendo la array en caso de que haya uno solo

      Saludos

      Eliminar
  8. Estimado, tengo una lista de productos y precios de casi 20.000 artículos, quisiera saber si se puede hacer una Macro como lo detallo a continuación:
    En la lista figura en una sola columna así GUARDAFANGO DELANTERO VOLKSVAGEN u otra marca, me gustaría que al ingresar abreviado de esta manera GUAR DEL VOLKS o GUAR VOL, y me listara todas filas con lo relacionado con GUARDAFANGO de esta marca de automóviles o carros.

    Tengo 2 macros pero solo buscan palabras idénticas y no palabras abreviadas o similares acompañadas con otras.
    En el detalle de búsqueda, que aparece mas arriba al ingresar GUARD solo lista todos los guardafangos o guardabarros, pero al ingresar GUARD DEL o GUARD VOLK no lista nada.

    Tengo muy poco conocimiento de programación en Visual Basic, hace mas de 25 años
    realice programas en Basic, GW Basic y pascal 5.5, te puedes imaginar hoy cuantos años tengo (68), pero aun me gusta seguir haciéndolo.

    Unas de la macros que estoy utilizando es la siguiente:

    Sub Buscador()
    ‘Esta macro la hallé en internet, desconozco su autor
    ‘solo realicé unas pequeñas y mínimas reformas para mi comodidad

    rango = "A1:B19500"
    'Busca desde fila A1 hasta A19500

    menSup = "Busqueda EL TANO (c) Albert Thomas 2019 Version 1.03"
    'Menu superior en caja inputBox

    MarcLine = "Ingresar dato a buscar"
    Dato = InputBox("LISTA DE REPUESTOS - Ingresar dato a buscar - código, marca o palabra-.", menSup, MarcLine)
    If Dato = "" Then Exit Sub

    Set DatoFinal = Range(rango).Find(Dato)
    If Not DatoFinal Is Nothing Then
    Range(DatoFinal.Address).Select
    Else
    MsgBox "El dato buscado no existe en la lista o quizás se escribió mal al ingresarlo.", vbCritical, menSup

    End If

    If Dato <> "" Then SendKeys "^b"
    ‘Activa Ctrl+b

    End Sub


    Y esta otra macro que la copie de YouTube de un video el cual me inspiro para comenzar a programar en VBA


    Private Sub TextBox1_Change()

    Dim Busqueda As String

    If Hoja1.TextBox1.Value <> "" Then
    Busqueda = "*" & Hoja1.TextBox1.Value & "*"
    Range("A6").CurrentRegion.AutoFilter Field:=2, Criteria1:=Busqueda
    ‘Busca y lista desde A6 en adelante
    Else
    Busqueda = ""
    Range("A6").CurrentRegion.AutoFilter
    End If

    End Sub

    Pero ninguna de las dos macros busca o filtra por aproximación o similitud.
    Disculpa que ocupe mas tu tiempo, ¿que reformas debería hacer para lograr
    lo detallado anteriormente?.

    Utilizo Windows 7, Excel 2003 y 2010.

    Desde ya muchas gracias, quedo a la espera vuestra respuesta.

    Atte. Albert Thomas.

    eltanoauto66@yahoo.com.ar
    eltanoautopartes@gmail.com

    ResponderEliminar
    Respuestas
    1. Hola Albert,
      se suele emplear el operador LIKE
      Echa un vistazo a esta entrada
      https://excelforo.blogspot.com/2013/09/vba-el-operador-de-comparacion-like.html

      Espero te sirva

      Saludos

      Eliminar
    2. Muy agradecido por tu pronta respuesta, con mis minimos conocimientos trataré de hacerlo en VBA.

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.