#ExcelPedroWave

Cuando pasa el ratón por encima (evento MouseMove) de un país del mapa de la UE, se muestra su nombre, su tasa de desempleo para el año seleccionado y su bandera. Esta es la principal mejora y es la que más me ha costado desarrollar.

Comentario de Robert:

Según tu segunda sugerencia: Me interesaría mucho cómo proporcionarías una descripción con el nombre del país, la tasa de desempleo y la bandera al pasar el ratón sobre el mapa. No sé cómo lograr esto, así que estaría muy interesado en ver cómo harías esto.

Comentario de Robert:

Finalmente, reemplazaste la imagen vinculada por dos gráficos y usaste el código VBA para hacerlos visibles o invisibles. Esta es una buena idea, pero solo si aprovecha el hecho de tener el gráfico en su tablero, es decir, para seleccionar un país haciendo clic o desplazándose sobre las líneas con el mouse. Echa un vistazo a esta publicación, donde se explica la selección de una serie de datos al pasar el mouse sobre:

https://www.clearlyandsimply.com/clearly_and_simply/2020/06/how-to-handle-line-charts-with-many-data-series.html

Mejora 3) Nuevos botones de animación

El reloj de arena ha sido sustituido por los botones de animación: Play, Pause, Stop.

Comentario de Robert:

Animar la vista es una gran idea. No lo he hecho en mi artículo, porque quería centrarme en resaltar todas las vistas de un tablero. Pero definitivamente es una buena idea. Dicho esto, aún preferiría los botones de reproducción que utilicé en mis libros de animación, porque creo que son más intuitivos que un icono y una barra de desplazamiento. Si el usuario ve los botones de reproducción en un tablero, comprende inmediatamente para qué sirven. Este no es el caso con un icono de reloj de arena y una barra de desplazamiento. Echa un vistazo al libro de trabajo que viene con esta publicación, por ejemplo:

https://www.clearlyandsimply.com/clearly_and_simply/2014/09/gatminder-replica-in-microsoft-excel.html

Mejora 1) Información emergente (tooltip) con banderas

La idea de mostrar información emergente con el nombre del país y la tasa de desempleo me pareció demasiado sencilla, por lo que añadí el reto de mostrar la bandera. Eso ya no es tan fácil, como Robert Mundigl dijo en sus comentarios en una de mis publicaciones en Linkedin:

Comentarios de Robert en Linkedin: Tasas de desempleo de la UE

 

Proporcionar una tooltip sobre mapas de formas en hojas de cálculo de Excel no es tan fácil como puede sonar. Por lo que sé, sólo hay dos soluciones para hacer esto y ninguna de ellas funcionará realmente para este panel:

 

La opción 1 es la brillante técnica de mi amigo Jordan Goldmeier (hipervínculos que desencadenan una función definida por el usuario VBA), como se describe aquí:

https://optionexplicitvba.blogspot.com/2012/09/the-excel-rollover-mini-faq.html

Esto funciona muy bien, pero requiere una cuadrícula de celdas minimizada debajo de las formas (alturas de fila muy estrechas y anchos de columna) y esto se convierte en un problema con el diseño del panel, si está utilizando otros rangos de celdas para mostrar, por ejemplo, la tabla desplazable o las etiquetas de eje del gráfico de barras.

 

La opción 2 usa la agregación de hipervínculos a las formas del mapa. Los hipervínculos están "muertos" y hacer clic en ellos no hace nada, pero se puede usar VBA para aprovechar las sugerencias de pantalla del hipervínculo para mostrar el nombre y la tasa de desempleo del país (pero no la bandera...). Echa un vistazo a este post de 2010, donde esta técnica se utiliza en un mapa de Argentina:

https://www.clearlyandsimply.com/clearly_and_simply /2010/02/spice-up-your-choropleth-maps-with-excel.html

Importante desventaja de esta 2ª opción: ya no se puede hacer clic en la forma para seleccionar un país. Para mí, este es un no-go para el tablero, porque el clic y la selección es más importante que la tooltip en mi humilde opinión.

 

De todos modos, no dejes que te desanime. Si tienes una idea de cómo mostrar una tooltip para las formas en los gráficos de Excel, me encantaría verla.

Tengo que decir que estas dos opciones ya las conocía yo. La segunda opción no permite mostrar la bandera, por lo que se tiene que descartar. La primera opción se basa en la increible técnica de Jordan Goldmeier, con el excelente truco con el efecto rollover que he estado usando durante 7 años, desde que conocí a nuestro amigo común Jordan. Ver mi ejercicio con el efecto rollover en este enlace: pedrowave.blogspot.com - Como pintar con Excel

El problema con esta opción es crear una retícula con gran cantidad de filas y columnas, lo que sólo se puede hacer si el mapa es lo único que hay en la hoja y éste no es el caso.

Hay una tercera opción que permite mostrar los datos del país y su bandera en un cuadro con información emergente (tooltip) que voy a exponer a continuación.

Este vídeo está grabado con una versión preliminar que después ha sido mejorada para que la información del país y su bandera persiga al ratón, que estará centrado en el cuadro emergente (tooltip). También se ha añadido la posibilidad de fijar la tooltip y que cambie cuando se mueva el ratón por encima de los países del mapa y la posibilidad de ocultar la tooltip. Por supuesto que, haciendo clic encima de la tooltip, queda seleccionado el país sobre el que está el ratón.

Técnicas empleadas para mostrar la tooltip con datos del país y su bandera

A) Las formas de la hoja 'dashboard' se ordenan en varias capas de información:

1) Capa superior con los controles: Combobox; Scrollbar; DropDown; Option Button; Charts.

2) Capa de etiquetas invisibles (control ActiveX): Cada etiqueta se nombra con "L_" y la abreviatura del país con 3 letras. Este es el truco principal pues las etiquetas activan el evento MouseMove cuando el usuario mueve el ratón, lo que permite modificar la información de la tooltip.

3) Capa de la Tooltip con una única imagen obtenida con la cámara de Excel. Al situar esta capa entre la capa de etiquetas y la capa de formas con los contornos de los países, es posible que la tooltip siga los movimientos del ratón de manera continua. 

4) Capa de formas de los países. Son las formas originales con las fronteras de los países y sus áreas coloreadas para formar el mapa cloropético. Cada forma se nombra con "S_" y la abreviatura del país con 3 letras. 

5) Capa inferior con el resto de formas: Países fuera de la UE; Map Icon; Play Button Circle; Image_Copyright. 

El ordenamiento de estas capas se ha hecho desde el menú: Inicio | Buscar y seleccionar... | Panel de selección..., con las formas de la hoja.

1) Capa con la Tooltip:

Denominada TooltipMap en la hoja 'dashboard', está generada con la cámara de Excel y con la siguiente fórmula: =tooltip!$B$3:$D$7

 

En la hoja 'tooltip' se obtiene el nombre del país, su tasa de desempleo para el año seleccionado y su bandera, a partir del valor de la celda G8 con el nombre: Tooltip_Country

 

En las celdas C4 y H8 se obtiene la bandera a partir del nombre definido: myFlag con la siguiente fórmula:

=INDICE(TableTooltipCountry[Flag];COINCIDIR(Tooltip_Country;TableTooltipCountry[Shape];0);1)

 

La tooltip no se mueve cuando no está chequeada la casilla de verificación "Move", en cuyo caso se muestra en una posición fija y con una línea alrededor, con el color azul de la bandera de Europa, RGB(0,51,153):

  

Se han dispuesto dos casillas de verificación (checkbox) para actuar sobre la Tooltip mediante la macro ChangeTooltipStatus:

Visible: para mostrar u ocultar la Tooltip.

Move: para permitir el movimiento de la Tooltip o dejarla inamovible en una posición fija.

Estos dos checkbox se han simulado en dos celdas de la hoja y llaman a la macro ChangeCountryTooltip

El formato de la Tooltip es fácilmente modificable a gusto del usuario con sólo editarlo en la hoja 'tooltip' y modificar la tabla TableTooltipCountry con los datos a informar.

2) Capa de etiquetas invisibles:

Esta es la nueva capa añadida al efecto para poder mover la Tooltip con la bandera a través de las áreas de los distintos países de la UE siguiendo el movimiento del ratón, por lo que esta mejora es la principal aportación de este mapa.

a) Lo primero que he hecho ha sido crear las nuevas formas como etiquetas control ActiveX desde el módulo VBA modChangeTooltip con las macros:

AddLabels: Recorre todas las formas con los mapas de cada país, denominadas "S_(3 letras del país)" y llama a la macro AddLabel.

AddLabel: Crea una etiqueta invisible denominada "L_(3 letras del país)" un 40% más pequeña que la forma del tipo "S_" y situada por encima de ella. Sus propiedades más interesantes son:

• BackStyle = fmBackStyleTransparent
• BorderStyle = fmBorderStyleNone
• ShapeRange.ZOrder msoBringToFront
• ShapeRange.Fill.Visible = msoFalse

DeleteLabels: Borra todas las etiquetas invisibles denominadas "L_(3 letras del país). Esta macro sirve para hacer pruebas y reintentar crear de nuevo las etiquetas hasta conseguir el resultado esperado.

No es necesario ejecutar de nuevo ninguna de estas 3 macros para usar la plantilla. Así quedaría el mapa si se hubieran creado las etiquetas con la propiedad visible:

b) Lo segundo que he hecho ha sido ajustar manualmente la posición y el tamaño de algunas de las etiquetas.

Para las etiquetas creadas anteriormente, que se pueden ver en el mapa de arriba, su área no se correspondía con la zona donde mover el cursor sobre alguno de los países. Esta labor manual ya no se debe hacer si el resultado es el deseado, si no es del agrado del usuario habrá que ajustar manualmente algunas de las etiquetas.  

Algunos países ha habido que retocarlos más que otros, por ejemplo para Dinamarca se puede ver la etiqueta L_DNK que no se ajustaba al contorno del país. También se ha intentado que las etiquetas no se solapen unas con otras para poder mostrar el país correcto en la Tooltip cuando se mueva el ratón por el mapa.

 

El caso de España es especial pues en el mapa de la UE no se muestran las Islas Canarias, localizadas en el Océano Atlántico, por cuestiones de espacio en la versión original de este mapa cloropético.

 

 

Será una nueva mejora a considerar para una futura entrada en el blog.

c) Lo tercero que he hecho ha sido crear un módulo de clase en VBA llamado 

CActiveXLabel para no tener que crear un par de eventos por cada una de las 28 etiquetas invisibles creadas en los pasos anteriores.

La clase ClassLabel definida como MSForms.Label permite atender estos dos eventos:

• ClassLabel_MouseMove: lanza el evento MouseMove cuando el ratón pasa por encima de una etiqueta invisible. Es el evento más importante del truco para mostrar la Tooltip.

• ClassLabel_Click: lanza el evento Click cuando se hace clic en una etiqueta invisible para cambiar el país seleccionado. 

El código del módulo es el siguiente:

' ----------------------------------------------------------------------------------------------------------------------------------
'   VBA Project:    EU Unemployment Rates - Highlight item across the dashboard
'   Class Module:   CActiveXLabel
'   Author:         Pedro Wave
'   Copyright:      © 2020 by Pedro Wave for Excel Guys, https://pedrowave.blogspot.com
'   Last edit:      16-Jun-2020
'   Purpose:        Country labels class events: MouseMove & Click
' ----------------------------------------------------------------------------------------------------------------------------------

Option Explicit

Public WithEvents ClassLabel As MSForms.Label

Private Sub ClassLabel_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal y As Single)
'
' Class Label MouseMove event
'
    Dim sCountry As String
    
    On Error Resume Next
    
    ' Change country label by country shape
    sCountry = Replace$(Me.ClassLabel.Name, "L_", "S_")
    
    Sheets(C_TOOLTIP_SHEET_NUMBER).Range("Tooltip_Country").Value2 = sCountry
    
    ' TooltipMap position
    If Sheets(C_MAP_SHEET_NUMBER).Range("myTooltipMove").Value2 = "a" Then
        With Sheets(C_MAP_SHEET_NUMBER).Shapes("TooltipMap")
            .Left = ClassLabel.Left + x - .Width / 2
            .Top = ClassLabel.Top + y - .Height / 2
        End With
    End If
    
End Sub

Private Sub ClassLabel_Click()
'
' Class Label Click event
'
    Dim sCountry As String
    
    ' Label lost focus
    Sheets(C_MAP_SHEET_NUMBER).Select
    Range("myChartImage").Select
    
    ' Chane country label by country shape
    sCountry = Replace$(Me.ClassLabel.Name, "L_", "S_")
    
    ' Update after user selected another country by clicking on a map label
    UpdateShape sCountry

End Sub

d) Lo cuarto ha sido crear, en el módulo modChangeTooltip, una Colección (Public LabelColl As Collection) que contiene todas las clases del tipo ClassLabel con la macro FillLabelCollection que rellena la colección con las clases de cada una de las 28 etiquetas del tipo "L_".

Con lo que tenemos 3 escenarios posibles para cambiar el país seleccionado: