Búsqueda Inteligente en Excel sin formulario ActiveX (versión simplificada)

Es esta entrada  veremos un método alternativo para lograr “Búsquedas inteligentes en Excel”. En la entrada “Búsqueda Inteligente con ComboBox” habíamos utilizado un formulario ActiveX. En esta oportunidad vamos a utilizar solo TextBox y macros.

El código a utilizar sigue el mismo razonamiento que en la entrada de referencia,  solo que hacemos una simplificación utilizando las propiedades de las tablas dinámicas, para ahorrarnos código complicado. Quizás no sea el método más elegante  pero es muy simple y se llegan a tener buenos resultados.



De la otra forma tendríamos que programar el equivalente a lo que hace la tabla dinámica al actualizarse. Así que con este método se olvidan de eso.

 

Proceso:

1.       En una hoja tenemos los datos, que es la misma base de datos que utilizamos en el ejemplo de  “Búsqueda Inteligente con ComboBox”.

Como ven, se agrego una imagen con una lupa, que vinculada a una macro simple nos llevará a la parte de búsqueda. También se puede hacer un hipervínculo sobre la imagen hacía la hoja de “buscar”, que es mas simple aún.

2.       En otra hoja tenemos lo que sería el “formulario de búsqueda”.

El formulario de búsqueda lo creamos de acuerdo  a nuestra imaginación, a nuestra necesidad o a nuestra comodidad. Debe contener todos los campos que nos interesan.

o   Campo ID: en este campo es donde introduciremos el código de empleado correspondiente.

o   Campo Ubicación: en este campo introduciremos una formula “BuscarV” para que de acuerdo al código que figura en “ID” me muestre la ubicación para ese empleado.

o   Campo Teléfono: utilizamos un “BuscarV” para ver el teléfono para el empleado con ese ID.

o   Campo Puesto: de acuerdo al valor introducido en ID con un “BuscarV” vemos el puesto que ocupa el empleado.

o   El campo dónde dice nombre es una celda en blanco donde se introducirá el campo para la búsqueda. En la imagen no se ve pero en ese lugar colocaremos un Control ActiveX,  un “TextBox” al que se le quitaran los colores de borde y relleno.

o   Imagen Home: esta imagen se utilizara que para vinculada a una macro simple nos lleve a la base de datos de nuevamente. También se puede hacer un hipervínculo sobre la imagen hacía la hoja de “Empleados”, que es mas simple aún.

Lo anterior es acerca de los elementos que se ven, los elementos que hacen al panel de control.

3.       El control “TextBox” nos sirve para utilizar el método “Texbox_Change”. Es decir que cuando cambia algo dentro del control ejecute una acción determinada.

4.       Otro elemento que utilizaremos es una tabla dinámica, esta tabla estará ubicada en la parte inferior del campo “nombre”. Esta estará vinculada a los datos de los empleados pero utilizaremos en el campo “fila” solo el campo “nombre de empleado”.

El rotulo de la fila se ocultara y cuando no haya dato dentro del “TextBox” no veremos nada, ya que equivale a poner en el filtro de la tabla dinámica el valor “vacío” o doble comillas.

5.       La parte complicada es la macro, por decirlo de alguna manera, ya que debemos hacer algunas ediciones de un código básico.

Debemos grabar una macro en donde usemos un filtro sobre la tabla dinámica con “contiene”. Luego editaremos esta macro vinculando el texto de filtro con el texto que figura en el “TextBox” del campo Nombre. El objetivo de esto es que cuando varié el contenido del Textbox actualice el filtro de la tabla dinámica.

El código es el siguiente:

-------------------------------------------------------------------------------------------------------------------------

Private Sub TextBox1_Change()

‘el código siguiente es para ocultar los refrescos de pantalla

Application.ScreenUpdating = False

‘utilizamos el condicional if para que de acuerdo al contenido del texbox ejecute una acción,

If TextBox1.Text = "" Then

‘si el texto de la caja se vuelve vació ejecuta lo siguiente

‘el código siguiente es el que se uso como base, que es la macro grabado en primera instancia.

‘se cambio la variable “Value1” a vacío.

 ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("NOMBRE COMPLETO").ClearAllFilters

ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("NOMBRE COMPLETO").PivotFilters.Add Type:=xlCaptionEquals, Value1:=""

‘el código siguiente es para restablecer el ancho de columna cuando actualicemos la tabla dinámica.

 Columns(4).ColumnWidth = 30.29

‘este código se utiliza para vaciar todos los campos del formulario.

Range("D4").FormulaR1C1 = ""

‘si la caja contiene algún texto ejecuta las siguientes acciones.

Else

‘este código lo obtenemos con la macro básica pero cambiamos en “Value1” por el contenido del  TexBox como ven.

 ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("NOMBRE COMPLETO").ClearAllFilters

ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("NOMBRE COMPLETO").PivotFilters.Add Type:=xlCaptionContains, Value1:=TextBox1.Text

 Columns(4).ColumnWidth = 30.29

End If

End Sub

-------------------------------------------------------------------------------------------------------------------------

El código anterior es el corazón de la mini aplicación pero falta un par de consideraciones.

6.       Seleccionar un elemento de la lista desplegable.

Cuando utilicemos el formulario para realizar una búsqueda podría mostrarnos varios elementos, debemos seleccionar uno de ellos. Para eso utilizamos un método doble clic.

El código es el siguiente:

-------------------------------------------------------------------------------------------------------------------------

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Application.ScreenUpdating = False

‘el código siguiente es para evitar que al hacer doble clic me genere el ruido molesto de error

Cancel = True

                ‘utilizamos un condicional if que funciona solamente cuando coincide la posición de la celda en la que hacemos doble clic con el rango determinado, en este caso como es un ejemplo “D12:D100”

If Not Intersect(Target, Range("D12:D100")) Is Nothing Then

‘seteamos el texbox von el valor de la celda seleccionada en la tabla dinámica

TextBox1.Text = ActiveCell.Text

‘usamos la función coincidir “match” de Excel para buscar el valor del ID para el empleado seleccionado.

Range("D4").Value = WorksheetFunction.Match(TextBox1.Text, Sheets("empleados").Range("C7:C26"), 0)

‘luego dejo vacía la tabla dinámica ya que encontramos nuestro valor buscado.

ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("NOMBRE COMPLETO").ClearAllFilters

ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("NOMBRE COMPLETO").PivotFilters.Add Type:=xlCaptionEquals, Value1:=""

Columns(4).ColumnWidth = 30.29

‘hacemos foco sobre el textbox

TextBox1.Activate

End If

End Sub

-------------------------------------------------------------------------------------------------------------------------

7.       Falta una ultima parte, hay que prever que pasa cuando introducimos un código manualmente en “ID”. Utilizaremos el método “worksheet_change”

-------------------------------------------------------------------------------------------------------------------------

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False

‘se usa este código para evitar el error cuando introducimos un código que no esta en la BD.

On Error Resume Next

If Not Intersect(Target, Range("D4")) Is Nothing Then

‘utilizo la función buscarv para ubicar el valor en el textbox correspondiente al código introducido.

TextBox1.Text = WorksheetFunction.VLookup(Range("D4"), Sheets("empleados").Range("B7:F26"), 2, False)

‘limpio la lista

ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("NOMBRE COMPLETO").ClearAllFilters

ActiveSheet.PivotTables("Tabla dinámica1").PivotFields("NOMBRE COMPLETO").PivotFilters.Add Type:=xlCaptionEquals, Value1:=""

Columns(4).ColumnWidth = 30.29

Range("D4").Select

End If

End Sub

-------------------------------------------------------------------------------------------------------------------------

Nota: los códigos anteriores deben estar en la hoja de código de la hoja “buscar”.

Espero que les guste la idea, lo pueden poner en práctica. En las próximas entradas veremos como podemos utilizar un formulario ActiveX nuevamente.

Para recibir las actualizaciones se pueden inscribir por alguno de los medios. Además pueden revisar la página de Descargas dónde iremos colgando algunos ejemplos.

Éxitos!

Keys:

·         Búsqueda inteligente en Excel

·         Búsqueda inteligente con Textbox en Excel

·         Search Engine in Excel

·         Búsqueda incremental en Excel