Elegir la columna de búsqueda en un Formulario de búsqueda inteligente en Excel vba
En un tutorial anterior veíamos la manera en que funcionaba un Formulario de búsqueda inteligente en Excel usando VBA. Dicho formulario ha sido bien recibido entre los visitantes de este Blog y de nuestro canal de Youtube. El Formulario se ejecutaba al momento de elegir una celda de Excel y nos permitía escribir texto y se mostraban los valores que coincidían con dicho texto, sin importar si el texto buscado estaba al inicio o al final de los resultados.
Entre otros comentarios, dos de ellos son los que han sido recurrentes, y es por eso que decido retomar el formulario y aplicarle ambas sugerencias:
- Que la búsqueda de valores permita ingresar texto en minúscula y mayúscula.
- Tener la posibilidad de elegir la columna donde se hará la búsqueda.
Buscar valores sin importar mayúsculas o minúsculas
Tenemos una lista de Productos la cual deseamos utilizar como simulación de una Lista de Validación de datos, con la diferencia de que tendremos un Cuadro de texto (TextBox) para escribir los datos y los resultados se enlistarán en un Cuadro de lista (Listbox).
Figura 1. Al escribir texto se filtran los productos que coinciden con el texto.
Para lograr que se filtren los resultados sin importar si el texto de búsqueda es en minúsculas o mayúsculas, usamos la función UCase para convertir internamente el texto de búsqueda y cada resultado. La modificación la hacemos en el ciclo For each el cual recorrerá cada elemento de nuestra lista y hará la comparación con el texto buscado. Si hay una coincidencia agrega el valor al ListBox1 y así sucesivamente.
Figura 2. Lista de productos.
Ver Video Columna dinámica en búsqueda inteligente
Suscríbete al canal de EXCELeINFO en YouTube para aprender más de Excel y macros.
A continuacón ciclo For each con la función UCase.
For Each i In Lista.Value If (UCase(i) <> "") * (UCase(i) Like "*" & UCase(.TextBox1.Value) & "*") Then .ListBox1.AddItem i End If Next i
Elegir la lista donde se hará la búsqueda
Para lograr que podamos seleccionar la lista en la cual hacer la búsqueda, tuvimos que modificar el formulario agregando un Cuadro combinado (ComboBox) para que se enlisten los encabezados de las listas que tengamos disponibles para buscar.
Para este ejemplo tendremos sólo dos listas de búsqueda, la cuales serán PRODUCTOS y PAISES. En la hoja Lista tendremos la lista de productos en la columna A y la lista de países en la columna B. El nuevo ComboBox se cargará con los encabezados que tengamos en la fila 1 de la hoja Lista. Tal como lo vimos aquí: Cargar encabezados de tabla en Combobox de Excel con vba.
Figura 3. Usamos un Cuadro combinado para mostrar los encabezados de las lista de búsqueda.
Nota: Prueba con más listas y verás como todos lo encabezados se agregan al ComboBox.
La siguiente instrucción nos ayudará a pasar los valores de los encabezados al ComboBox.
Me.ComboBox1.List = Application.Transpose(Hoja1.Range("A1").CurrentRegion.Resize(1).Value)
Ahora que ya vimos que el ComboBox se llena con los encabezados de ambas listas, estamos listos para probar la búsqueda inteligente:
- Selecciona una celda del rango B10:B15.
- En el Formulario que se muestra elegimos la columna de búsqueda.
- Ingresamos un texto y confirmamos que el resultado es en base a la columna elegida.
- Seleccionamos un valor de los mostrados.
- Presionamos Enter y vemos cómo el valor elegido se inserta en la celda.
Figura 4. Columna dinámica en búsqueda inteligente en un formulario de Excel vba.
Código de la macro
Te muestro el código VBA referente al evento Change del TextBox1, en el cual usamos la variable Columna para guardar el número de columna elegida en el ComboBox, además de usar la variable UltimaFila para definir la última celda del rango de la lista elegida y se usen esos valores para la búsqueda. Por último la variable Lista se usará para definir el rango de la lista elegida.
La siguiente macro va en el código de la hoja EXCELeINFO y sirve para lanzar el formulario al elegir una celda.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'Si la celda elegida es B13:B18 se muestra el Formulario. If Not Intersect(Target, Range("B10:B15")) Is Nothing Then UserForm1.Show 'En todo caso no se muestra. Else End If End Sub
El siguiente es el código completo del Formulario.
'EXCELeINFO 'MVP Sergio Alejandro Campos 'http://www.exceleinfo.com 'https://www.youtube.com/user/sergioacamposh 'http://blogs.itpro.es/exceleinfo ' '1)Al iniciar el formulario Private Sub UserForm_Initialize() Me.Height = 101.25 Me.ComboBox1.List = Application.Transpose(Hoja1.Range("A1").CurrentRegion.Resize(1).Value) End Sub '2)Al escribir texto en el TextBox Private Sub TextBox1_Change() If Me.TextBox1.Value = "" Or Me.TextBox1.Value = " " Then Me.Height = 101.25 Else ''''''''''''' 'Definimos la columna de búsqueda y después definimos el rango de la lista elegida. Columna = Me.ComboBox1.ListIndex + 1 If Me.ComboBox1.Value = "" Then MsgBox "Falta elegir columna.": Exit Sub UltimaFila = Application.WorksheetFunction.CountA(Hoja1.Columns(Columna).EntireColumn) Set Lista = Hoja1.Range(Hoja1.Cells(2, Columna), Hoja1.Cells(UltimaFila, Columna)) ''''''''''''' Me.Height = 177.75 Dim rng As Range, e 'Set Lista = Range("lstProductos") With Me .ListBox1.Clear For Each i In Lista.Value If (UCase(i) <> "") * (UCase(i) Like "*" & UCase(.TextBox1.Value) & "*") Then .ListBox1.AddItem i End If Next i End With End If End Sub '3)Aceptar el valor elegido y capturarlo en la celda activa Private Sub CommandButton2_Click() Cuenta = Me.ListBox1.ListCount For i = 0 To Cuenta - 1 If Me.ListBox1.Selected(i) = True Then ActiveCell.Value = Me.ListBox1.List(i) End If Next i Unload Me End Sub '4)Cerrar el formulario Private Sub CommandButton1_Click() Unload Me End Sub
Descarga el archivo de ejemplo
Búsqueda inteligente en un formulario de Excel vba – COLUMNA DINÁMICA – EXCELeINFO.zip
Si te gustó este tutorial por favor anótate en nuestra Lista de correo y Suscríbete a nuestro canal de YouTube para que estés siempre enterado de lo nuevo que publicamos.