Curso Macros y VBA Excel Udemy

lunes, 24 de noviembre de 2014

Filtrar Datos en Excel usando un ComboBox de la Cinta de Opciones con VBA y XML.

Curso: Personalizar la UI de
Excel con XML y VBA.
Formato:
Artículo/Tutorial.
Archivos necesarios:Personal v0.02.xlsm.
Anterior Artículo/Tutorial:Crear ComboBox...
Siguiente Artículo/Tutorial:Próximamente.
Versión Vídeo/Tutorial:Próximamente.

Vamos a crear un comboBox con imágenes en la Ribbon.
Aplicamos Fltros con un ComboBox.

Seguimos con la creación de un comboBox en la Cinta de Opciones de Excel, en la Ribbon. En el artículo anterior os explique cómo crear un cuadro combinado usando XML y también os expliqué los atributos o propiedades más importantes que tiene un comboBox para controlar su apariencia. En este artículo nos vamos a dedicar a su funcionamiento, os voy a explicar cómo utilizar la opción seleccionada, vamos a ver cómo acceder a ella desde VBA. Como ejemplo, se me ha ocurrido que cuando seleccionemos una opción se aplicará un filtro, por lo tanto eso es lo que vamos hacer. Vamos a ir dando todos los pasos necesarios para conseguir aplicar filtros con el comboBox y así descubriremos de qué manera podemos comunicar el comboBox con VBA, con Visual Basic. Sorprendentemente, no es tan difícil como seguro que estáis pensando. Después de todo lo que hicimos para conseguir desactivar los botones de la Ribbon en un artículo anterior, os va sorprender lo fácil que es hacer lo que os acabo de proponer.

Vamos con el trabajo. Para realizar los mismos pasos que yo, os he dejado para descargar el documento que utilizo en el artículo. Si habéis realizado los pasos del artículo anterior podéis usar ese, de lo contrario, podéis realizar la descarga a continuación o al inicio del artículo en su ficha descriptiva.
Como casi siempre, el primer paso lo tenemos que dar en el Custom UI Editor. Debemos añadir algo más al código XML que desarrollamos en el artículo anterior. Accedemos a él y abrimos el documento con el que vamos a trabajar. Lo que tenemos que añadir en esta ocasión es bastante evidente. Tal y como tenemos el comboBox no hace nada, debemos añadir algo para poder conectarlo con el código VBA que se encargará de filtrar los datos de los trabajadores. Los comboBox tienen un atributo llamado onChange que es el que se encarga de esa tarea. En él debemos indicar que macro o procedimiento de VBA se ejecutará cuando cambie la opción seleccionada en el cuadro combinado. Añadimos ese atributo a la definición del comboBox con el valor que podéis ver a continuación.

<comboBox id="comboPoblacion" label="Selecciona una población: " sizeString="XXXXXXXXXXXXXXXXXXXXXXXXX" 
imageMso="GroupSmartArtQuickStyles" screentip="Selecciona una población..." supertip="Al seleccionar una..." onChange="ModuloEmpleados.FiltrarPorPoblacion">


He acortado los valores de screentip y supertip para que no molesten, pero vosotros mantener los que establecimos en el artículo anterior. En cuanto a onChange hay poco que comentar. Le he indicado que cuando se cambie la opción seleccionada, debe llamar a un procedimiento de VBA llamado FiltrarPorPoblacion, que está guardado en un módulo llamado ModuloEmpleados. Ya está, no hay nada más que hacer en el código XML, todo el trabajo que nos queda es de programación con VBA. Por lo tanto cerramos el Custom UI Editor guardando los cambios y a continuación lo abrimos en Excel.

Cuando abráis el documento no notaréis nada diferente, pero si seleccionáis una de las opciones del cuadro combinado, sí que pasará algo.

Al seleccionar una opción se produce un error, todavía no hemos creado la macro que se llama.
Error por no encontrar la macro de onChange.

Se produce un error porque no encuentra la macro que hemos indicado en onChange, evidentemente porque todavía no la hemos creado. De todas las maneras, el error nos permite hacernos una idea de cómo funciona lo que hemos hecho en el código XML. En cuanto hemos cambiado la opción seleccionada ha llamado a la macro que le hemos indicado en onChange. Ahora enseguida vamos a crearla, pero antes vamos hacer algo que nos facilitará posteriormente aplicar los filtros desde VBA.

Datos que vamos a convertir en una Tabla.
Datos que vamos a convertir en una Tabla.

Los datos que tenemos en la primera hoja del documento, los datos de los empleados, los vamos a convertir en una Tabla a la que llamaremos Trabajadores. No es necesario hacer esto, pero si les damos un nombre a los datos, luego será más fácil acceder a ellos desde código. Para crear una Tabla, el primer paso es seleccionar una celda cualquiera de los datos que queremos convertir en Tabla... Y a continuación en la ficha Insertar, pulsaremos el botón Tabla.

Botón para crear Tablas en Excel.
Botón para crear Tablas en Excel.

Si alguno no tiene activado el botón Tabla, si no lo puede pulsar y ha hecho bien la selección de una celda de los datos, es porque para el artículo anterior, sin darme cuenta, subí un documento en el que ya estaba creada la Tabla y por lo tanto no os la deja crear de nuevo. Ya he actualizado ese documento para que no la tenga, pero seguramente más de uno tendréis la versión antigua y tendréis creada la Tabla. Para comprobarlo, no tenéis más que seleccionar una celda de los datos y fijaros si en la Cinta de Opciones os aparece una ficha llama Diseño. Si es así, significa que está creada y lo primero que os aparecerá dentro de esa ficha es el nombre de la Tabla.

Podemos ver el nombre de la Tabla en la ficha Inicio.
Podemos ver el nombre de la Tabla en la ficha Inicio.

Para los que no la tenemos creada, pulsamos en el botón y aparecerá la ventana para crear una Tabla, en la que debemos indicar dos cosas.

Ventana para crear Tablas en Excel.
Ventana para crear Tablas en Excel.

Lo primero que debemos indicar son las celdas que formará la tabla. Si habéis seleccionado previamente una celda de los datos que deben formar la tabla, Excel ya habrá seleccionado todas las celdas por nosotros. En este caso indica que usará las celdas que van de la A1 a la G13, que es correcto. Si no lo fuera deberíais indicar vosotros cuales son las celdas qué queréis usar. Lo siguiente que debemos indicar es si la tabla tiene encabezados. Con esto se refiere a si los datos que estamos indicando para crear la tabla, tienen una primera fila, que sirva de rótulos de columnas, de títulos. Como en este caso, sí que es así, dejamos marcada la opción. Si no los tendría habría que desmarcar la opción y Excel crearía los títulos de las columnas. Por lo tanto, tal y como está la ventana, todo es correcto, luego pulsamos en Aceptar.

Datos convertidos en Tabla.
Datos convertidos en Tabla.

Al hacerlo la tabla cogerá un formato diferente y aparecerá la ficha que os he comentado antes, la ficha Diseño. En ella le vamos a cambiar el nombre a la tabla, ahora mismo tienen un nombre genérico que no queda muy bien, le llamamos Trabajadores.

La tabla se crea con el nombre de Tabla1.

Cambiamos el nombre de la Tabla.

La tabla se crea con el nombre de Tabla1 hay que cambiarlo a Trabajadores.

En esa ficha también podéis cambiar el formato que tiene la tabla, por si no os gusta el que ha cogido. En estilos de tabla podéis seleccionar el primer estilo, el estilo ninguno... Y así se quitará.

Podemos quitar el estilo aplicado a la Tabla.
Podemos quitar el estilo aplicado a la Tabla.




Bueno, una vez tenemos creada la tabla vamos con el código, vamos a crear el procedimiento que se llamará desde onChange, debemos acceder al Editor de Visual Basic. Como ya os he comentado en artículos anteriores podéis acceder a él, pulsando las teclas Alt + F11 o en la ficha Desarrollador podéis pulsar el botón Visual Basic.

Accedemos al Editor de Visual Basic para crear la macro de onChange.
Accedemos al Editor de Visual Basic para crear la macro de onChange.

Una vez estemos dentro del Editor de Visual Basic, debemos crear un módulo. En el código XML hemos indicado que la macro que se llamará desde onChange está en un módulo llamado ModuloEmpleados, por lo tanto ese debe ser nuestro primer paso. Para hacerlo debemos acceder al menú Insertar y seleccionamos la opción de Módulo.

Insertamos el módulo que contendrá el procedimiento de onChange.
Insertamos el módulo que contendrá el procedimiento de onChange.

El siguiente paso es cambiarle el nombre, ahora mismo se llama Módulo1 y se debe llamar ModuloEmpleados. Lo seleccionamos y en la Ventana de Propiedades se lo cambiamos. Si no veis la Ventana de Propiedades la podéis mostrar en el menú Ver.

Podemos mostrar la ventana Propiedades en el menú Ver.
Podemos mostrar la ventana Propiedades en el menú Ver.

Cambiamos el nombre al Módulo.
Cambiamos el nombre al Módulo.

Una vez tenemos el módulo, ahora sí que vamos a crear el procedimiento o macro al que llama onChange. Hacéis un doble clic en el módulo para asegurarnos que estamos dentro de él y escribimos el siguiente código VB para definir el procedimiento FiltrarPorPoblacion.

Public Sub FiltrarPorPoblacion(Control As IRibbonControl, TextoSeleccionado As String)
End Sub

En estas dos líneas definimos el procedimiento que es llamado desde el onChange del comboBox. Como ya os he comentado varias veces con public indicamos que el procedimiento es público, que puede ser llamado desde cualquier parte del proyecto y con Sub indicamos que se inicia la definición de un procedimiento. Después de Sub indicamos el nombre que tendrá, que evidentemente debe coincidir con el que hemos indicado en el código XML. A continuación,  dentro de los  paréntesis hay declarados dos argumentos. Todos los procedimientos relacionados con la Ribbon deben cumplir unos requisitos en cuanto a los argumentos que reciben entre los paréntesis y esos dos son los que deben cumplir los procedimientos llamados desde onChange. Recibe un primer argumento de tipo IRibbonControl, que por ejemplo nos podría servir para saber desde que control se ha llamado al procedimiento. En nuestro caso es evidente que se le llama desde el comboBox que estamos creando... Pero en ocasiones, si hay más controles, se suele optar por crear un solo procedimiento para todos ellos y en su interior se decide que hacer dependiendo del control que ha llamado al procedimiento. Con ese argumento podríamos saber cuál ha sido el control que ha llamado al procedimiento. Pero como os digo, en nuestro caso nos da igual.

El argumento realmente importante para nosotros es el siguiente, es un argumento de tipo String, tipo texto. En el recibimos el texto que se ha quedado seleccionado en el cuadro combinado cuando se ha cambiado la opción seleccionada, que es justo lo que necesitamos. Luego ya veis que fácil va ser acceder a lo que queríamos, ese argumento de tipo String llamado TextoSeleccionado nos lo va decir. El nombre TextoSeleccionado lo he elegido yo porque he querido, se podría llamar como vosotros quisierais. Los nombres de los argumentos no son realmente importantes, su tipo sí. Se suele optar por nombres que nos indiquen lo que pueden contener, pero les podéis llamar como queráis.

Bueno, pues ahora sólo nos queda escribir el código necesario para aplicar los filtros, escribimos lo siguiente entre las dos líneas anteriores y os las explico.

If TextoSeleccionado = "*--Todas las Poblaciones--*" Then
   Worksheets("Lista Personal").ListObjects("Trabajadores").Range.AutoFilter Field:=4
Else
   Worksheets("Lista Personal").ListObjects("Trabajadores").Range.AutoFilter Field:=4, Criteria1:=TextoSeleccionado
End If

Lo que tenemos aquí es un condicional, un If. En el empezamos controlando si la opción seleccionada es igual a la primera, al texto que tiene la primera opción. Esto lo hacemos para indicarle que si es así debe quitar el filtro aplicado porque queremos ver todas las poblaciones. Si esto no se cumple, en el Else del If, le indicamos que aplique un filtro utilizando la población que está seleccionada en el comboBox. Para aplicar un filtro hemos usado lo siguiente:

Worksheets("Lista Personal").
ListObjects("Trabajadores").Range.AutoFilter 
Field:=4, Criteria1:=TextoSeleccionado

Vamos por partes que es una sola línea pero tiene mucho que explicar. Evidentemente aplicamos un filtro, que observando la primera pare de la línea vemos que se aplica en una tabla llamada Trabajadores que está situada en una hoja llamada Lista Personal. Hace un momento os he dicho que era mejor convertir en tabla los datos, porque después sería más fácil acceder a ellos para aplicar el filtro. Bueno pues aquí lo vemos, usamos el nombre que le hemos dado antes para indicar que queremos aplicar un filtro. Para aplicar el filtro utilizamos Autofilter, esta propiedad nos permite aplicar filtros en los datos como si usáramos el botón Filtro de la ficha Datos. Para aplicarlo pasamos dos argumentos, en el primero, en Field, indicamos la columna en la que queremos aplicar el filtro. Que en este caso es la cuarta, la columna de las ciudades. Después usamos el argumento Criteria1, que es en el que debemos indicar el criterio. Como pasamos el texto que está seleccionado en el cuadro combinado, entiende que sólo hay que mostrar los datos de los trabajadores que tienen la ciudad igual a la que se ha quedado seleccionada. Todo esto, es lo que hacemos en esa línea.

Para quitar el filtro utilizamos la siguiente línea. Es similar a la anterior, sólo que en este caso, como no le indicamos un criterio para aplicar en la columna de las ciudades, se quitan los filtros que estén aplicados en esa columna. No hay criterio, por lo tanto se muestran todos los datos.

Worksheets("Lista Personal").ListObjects("Trabajadores").Range.AutoFilter Field:=4

Bueno, pues así ya solo nos faltaría probar si funciona todo lo que hemos hecho. Si no hemos cometido errores al seleccionar una opción en el cuadro combinado, se mostrarán los datos que correspondan a esa opción. En la siguiente imagen, podéis ver cómo funciona.

Ahora podemos aplicar los filtros desde el comboBox de la Ribbon.
Ahora podemos aplicar los filtros desde el comboBox de la Ribbon.

Bueno pues ya está, esto es todo lo que os quería explicar en este artículo. Ya os había dicho que en este caso no iba a ser muy difícil conseguir nuestro objetivo. El uso de un comboBox de la Ribbon no es muy diferente a lo que haríamos con un comboBox ActiveX, de los que podemos insertar en las hojas de Excel. Evidentemente, puede que queden situaciones y problemas sin controlar, pero eso ya os lo dejo a vosotros. Sobre los cuadros combinados faltan por comentar muchas cosas, sobre todo lo relacionado con utilizar datos variables en su interior. Pero eso lo dejamos para otros artículos... ;)

5 comentarios:

  1. Excelente artículo. Gracias por siempre enseñarnos algo nuevo y compartirlo con los aficionados a la programación en VBA de Excel.

    Felicitaciones....

    ResponderEliminar
    Respuestas
    1. Gracias a ti por comentar!!!!!!

      A mi este tema es el que más me gusta. Estaría todo el día con esto, pero tiene que haber de todo y además cuesta más tiempo preparar material de este tipo.

      Saludos.

      Eliminar
  2. Excelente Trabajo David, he seguido todos los videos para modificar la cinta de excel y me ha servido muchismo, pero tengo dos temas que no he podido solucionar.
    1. como puedo llenar los valores de un combobox dependiendo de la opcion que se seleccione de otro
    2. Como puedo desde una macro obtener los valores seleccionado del ambos combos.
    Espero puedas responderme, muchas Gracias

    Ricardo

    ResponderEliminar
  3. Me olvide de precisar es que estoy trabajando con el Custom UI Editor y lo que necesito es tomar los valores de los combos que personalizado en la cinta.
    Muchas Gracias Otra vez.

    Ricardo

    ResponderEliminar
  4. Puedes hacer un video de como actualizar formulas en Excel usando dos ComboBox personalizados de la Cinta de Opciones con VBA y XML, digamos filtrando el nombre del vendedor y el ano....plis ayudame

    ResponderEliminar