Hoy aprenderemos a utilizar los eventos Change e Intersect de Worksheet en Excel. Así, usaremos cambios en nuestras hojas de cálculo, para desencadenar operaciones automáticas.

Como ejemplo, he preparado un gráfico con la información anual del consumo de gasoil en una empresa de transportes desglosado por meses.

Pero antes de nada, os dejo un enlace donde explico cómo configurar Microsoft Excel para el uso de macros, por si no lo habéis hecho aún.

Como se puede observar en la figura, la empresa cuenta con un objetivo mensual de 350€, es decir, debe optimizar los recorridos para realizar las entregas y no gastar más gasoil de la cuenta.

La última barra corresponde al promedio anual, que no es más que la media de los doce meses.

Este formato podría pegarse en el tablón de anuncios de la empresa para que los empleados tomaran conciencia sobre el consumo de combustible, les sirviera de motivación y actuaran en consecuencia.

Para ello, y a modo de resumen más allá de las cifras, vamos a resumir el estado en el que se encuentra el consumo, a una carita situada en el encabezado del informe, que será:

• Verde: si el promedio se encuentra por debajo del 5% objetivo.
• Amarilla: si el promedio se encuentra entre el 5% tanto por encima como por debajo del objetivo.
• Roja: si el promedio se encuentra por encima del 5% del objetivo.

Así, conforme vayamos introduciendo la información de cada mes, la carita va a ir cambiando de color en función de cómo vaya el promedio anual.

Esto lo conseguimos usando el evento Change de worksheet y el método Intersect de Excel, que detectarán el cambio en la hoja al introducir el importe mensual, evaluarán el promedio y mostrarán la carita correspondiente.

Os recuerdo que, para usar este procedimiento, el código lo introducimos directamente en la hoja de cálculo:

Antes de continuar, os voy a explicar los “trabajos previos” que hay que realizar en la hoja antes de ponernos a programar.

En primer lugar, descargamos las tres imágenes con las caritas verde, amarilla y roja (las puedes descargar al final del tema) y las guardamos en el directorio raíz C.

Para insertarlas en la hoja, hay que crear previamente un control ActiveX de imagen. Para ello, nos vamos a la pestaña de desarrollador, y en insertar seleccionamos la opción imagen (Control ActiveX):

Seleccionamos la zona de la hoja donde queremos crearlo, así como sus dimensiones:

Si pulsamos en propiedades, podemos modificar las características del mismo, así como su nombre.

En este caso le vamos a dejar el que está Image1, pero sí que vamos a cambiar algunas propiedades:

• BackColor: &H8000000B& (color de fondo desactivado)
• BackStyle: 0 (Transparente)
• BorderColor: &H8000000B& (color del borde desactivado)
• Height: 40,5 (alto de la imagen)
• Locked: False (no bloqueado)
• PictureSizeMode: 1 (para que se ajuste automáticamente al recuadro)
• Width: 40,5 (ancho de la imagen)

A continuación, os dejo el código con el que aprenderemos a utilizar los eventos Change e Intersect en Excel.

Macro 1. Vigilar cambios en celdas.

Macro 2. Cargar imagen correspondiente en función del promedio anual.

Supongamos que nos encontramos a principios del mes de octubre, y ya tenemos a nuestra disposición la información del consumo de gasoil del mes anterior.

El rango vigilado es,

Cuando introduzcamos la cantidad de gasoil consumida durante el mes de septiembre en la celda N50, al pertenecer al rango vigilado, se ejecutará automáticamente la función foto.

¿Qué hará la función foto?

Simplemente evaluar en que franja se encuentra el consumo respecto al objetivo.

Si el promedio se encuentra por debajo del 5% del objetivo, cargará la imagen con la carita verde mediante el comando LoadPicture en el control ActiveX Image1.

En el ejemplo, introducimos en septiembre la cantidad 10. Vemos que el promedio baja a 331.67, que es inferior al 95% del objetivo y cómo la carita se convierte en verde.

Si el promedio se encuentra por encima del 5% del objetivo, cargará la imagen con la carita roja,

Introducimos ahora para el mes de octubre la cantidad 800. Vemos que el promedio sube a 378.50, que es superior al 105% del objetivo y cómo la carita se convierte en roja.

En caso contrario, el promedio se encuentra en una franja del 5% por encima o por debajo del objetivo.

Para el mes de noviembre, introducimos la cantidad 200. Vemos que el promedio baja a 362.27, que se encuentra entre el 95% y el 105% del objetivo y cómo la carita se convierte en amarilla.

Con esto llegamos al final de la trigésimo primera lección, en la que has aprendido a utilizar los eventos Change e Intersect de Excel.

Espero que te esté gustando el curso. Si quieres, puedes proponerme macros que te interese desarrollar y las voy incluyendo en el temario, soy todo oídos.

Por último, quiero invitarte a compartir tus dudas en los comentarios o en el foro. Intentaré ayudarte en todo lo que pueda, y así aprenderemos todos.

Nos vemos en el próximo capítulo.