#ExcelPedroWave

No voy a hablar de los miles de menús que me comí en los restaurantes de la capital de España, durante los 9 años que estuve trabajando en varias oficinas de Madrid.

Voy a hablar de cómo hacer menús interactivos usando la segmentación de datos para filtrar datos de tablas dinámicas (Slicers en inglés), con un ejemplo de un tablero de ajedrez interactivo.

He intentado que los menús sean intuitivos, vistosos, amigables y fáciles de usar y de aprender, según el principio de acción-reacción, y procurando mejorar la interfaz del usuario (User Interface - UI) y la experiencia del usuario (User eXperience - UX). Aunque no he programado la posiblidad de deshacer ninguna de las acciones con los menús, siempre se puede obtener cualquier posición de las piezas de ajedrez en el tablero o incluso borrarlas todas y comenzar de nuevo.

En esta entrada del blog aprenderemos a:

1. Mantener dos tablas dinámicas: una con un único filtro para el menú y otra con un filtro para los submenús.
2. Mantener dos tablas normales: una para el menú y otra para los submenús.
3. Mantener una tabla auxiliar para los submenús con su posición, número de filas y de columnas.
4. Modificar la segmentación de datos del menú.
5. Modificar la segmentación de datos de los submenús.
6. Analizar las macros de Excel, en lenguaje VBA, que interactúan con los menús y submenús.
7. Representar un tablero de ajedrez interactivo dentro de un rango de celdas de una hoja de cálculo.

Esta es la apariencia que tiene el menú principal y los diferente subménus gráficos, con un ejemplo de cómo animar un tablero de ajedrez en Excel.


En esta imagen gif animada se muestra un menú principal, numerado del 1 al 7, diseñado con una segmentación de datos. Los 7 submenús están diseñados con una única segmentación de datos con un formato de filas y columnas diferente para cada uno de los submenús. Los submenús se despliegan a la altura de su correspondiente entrada desde el menú principal. Algunos submenús son de texto y otros son gráficos, como las piezas del ajedrez o sus posibles movimientos en vertical, horizontal o diagonal.

Descarga de la plantilla

Descarga la plantilla totalmente gratuita, con las macros visibles y las hojas protegidas sin contraseña, desde Google (con el botón "Excel Download") o desde el enlace a Microsoft OneDrive:

Tablas normales para el menú y los submenús

Para crear los menús hace falta una hoja auxiliar 'TD_Menu' en la que se han insertado 3 tablas normales, un rango para los submenús y una celda auxiliar:

1. TablaMenu: Para el menú principal en el rango E4:E11 con la lista de submenús numerados.
2. TablaSubmenus: Para los submenús en el rango H14:N16 con una columna por cada submenú, con los valores posibles de cada submenú.
3. TablaSubmenu: Tabla auxiliar con los valores del submenú seleccionado en el menú principal.
4. Rango de parametrización de cada submenú: Rango F26:N29 con el tope, filas y columnas de la segmentación de datos que sirven para personalizar cada submenú.
5. Celda auxiliar: En la celda E2 se calcula el número de submenú elegido en el menú principal.

La tabla para el menú principal se puede personalizar para cambiarlo según las necesidades del usuario final. En este ejemplo, con traducción simultánea a varios idiomas, los valores del menú principal se encuentran en la hoja 'Idiomas', desde donde se deben cambiar:

En la columna de la izquierda se muestra la tabla con el submenú elegido y las demás columnas son de la tabla de submenús, que se pueden personalizar para formar otro tipo de submenús. Hay que tener en cuenta que, para la traducción a varios idiomas, los valores del submenú 4 (Iniciar; Borrar) y del submenú 7 (SI; NO) están en la tabla de la hoja 'Idiomas':

En esta imagen se muestra el rango de parametrización de la segmentación de datos de cada submenú. Hay que prestar atención que en la fila de "Columnas" el valor del número de columnas del submenú 6 es fijo:

Tablas dinámicas para el menú y los submenús

En la hoja auxiliar 'TD_Menu' se han insertado dos tablas dinámicas:

1. TD_Menu: Para el menú principal en el rango B4:C4 con origen de datos en la TablaMenu y sólo con un filtro para el campo "Menu".
2. TD_Submenu: Para el submenú en el rango B14:C14 con origen de datos en la TablaSubmenu y sólo con un filtro para el campo "Submenu".

Segmentación de datos del menú principal

La segmentación de datos del menú principal está conectada con la tabla dinámica TD_Menu y está en la hoja 'Tablero'.

La configuración de la segmentación de datos es la siguiente:

• Mostrar encabezado en estado sin chequear.

• Se ha creado un nuevo estilo de segmentación de datos sin bordes.

Segmentación de datos de los submenús

La segmentación de datos de los submenús está conectada con la tabla dinámica TD_Submenu y está en la hoja 'Tablero'.

Se basa en la misma configuración de la segmentación de datos del menú principal, sin mostrar encabezado ni bordes.

La principal ventaja de está técnica para diseñar menús y submenús es que la misma segmentación de datos de submenús sirve para mostrar cada uno de los submenús, simplemente variando su posición, su número de filas y columnas y los valores del submenú seleccionado con el menú principal, gracias a las tablas de la hoja 'TD_Menu' que ya he descrito más arriba.

Con una única segmentación de datos se configuran varios submenús, por ejemplo:

Se puede observar que algunos de ellos están formados por caracteres gráficos, como las piezas de ajedrez o las flechas de movimiento. Para ordenar las flechas de movimiento ha hecho falta definir, en las opciones avanzadas de Excel, una nueva lista personalizada:


Lo importante es que los submenús cambian dinámicamente cuando se cambia la selección del submenú desde el menú principal y se coloca a la altura del submenú elegido. Lo mejor es verlo en el siguiente vídeo en acción : 


Al seleccionar en el menú principal la opción "1. Fuente" aparece el submenú con la lista de las dos fuentes programadas:

• Segoe UI Symbol
• Times New Roman

Las figuras de las piezas del ajedrez creo que se llaman trebejos. De la hoja 'Ajedrez' se obtiene la posición inicial de las piezas en una partida de ajedrez y ejemplos de las dos fuentes que pintan las piezas:

Macros del menú y los submenús

Cuando se selecciona un menú o un submenú se ejecutan las macros Excel, escritas en lenguaje VBA, que permiten la interacción con el tablero de ajedrez del ejemplo propuesto.

Para explicar e interpretar las macros de esta aplicación es preciso conocer los nombres definidos que son los que aparecen en el menú:
Fórmulas --> Administrador de nombres 


Nombres definidos más relevantes:

• Rango_TD_Submenu: Es el origen de datos de la tabla dinámica TD_Submenu, creado mediante las funciones INDICE() y CONTAR.SI(), esta última con el argumento "?*" con comodines para contar solamente las filas con datos.
• Rango_Tabla_Submenu: Con el rango completo de la tabla "TablaSubmenu", que se usa en el nombre definido anteriormente: Rango_TD_Submenu
• El resto de nombres definidos son rangos o celdas para que sea más clara y fácil su referencia en las fórmulas y macros.

Módulos de la aplicación

• Hoja 'Tablero': Se produce el evento SelectionChange cuando cambia la celda seleccionada de la hoja y que representa un escaque del tablero de ajedrez. Se ejecuta la macro GetSubmenu únicamente cuando se ha seleccionado en el menú principal el submenú 2 con las piezas.


• Hoja 'TD_Menu': Se produce el evento Change cuando cambia el valor de la celda. Si cambia el valor del filtro de la tabla dinámica TD_Menu se ejecuta la macro ChangeSubmenu. Si cambia el valor del filtro de la tabla dinámica TD_Submenu se ejecuta la macro ActionSubmenu.


• Hoja 'ThisWorkbook': Los eventos Open; BeforeSave y BeforeClose lanzan la macro para proteger las hojas ProtectSheets. Además el evento Open oculta todos los menús y barras de Excel llamando a la macro VisibleExcel.


• Módulo modChangeMenu: Contiene las macros necesarias para interactuar con el menú principal y con los submenús:


• ChangeSubmenu: Realiza las acciones que permiten el cambio de un submenú desde el menú principal.
• GetSubmenu: Obtiene el último valor del submenú seleccionado, por lo que hay que modificar el número de casos si se cambian los submenús.
• ActionSubmenu: Ejecuta las acciones de un determinado submenú, por lo que hay que modificar el número de casos si se cambian los submenús.
• Select2Slicer: Llama 2 veces a la macro SelectSlicerItem, pues devuelve un error la primera vez. A tener en cuenta para una mejora.
• SelectSlicerItem: Selecciona un único valor de la segmentación de datos.
• TopMenu: Obtiene el tope de la posición de la segmentación de datos del menú principal.
• RowHeightMenu: Obtiene el alto de la fila de la segmentación de datos del menú principal.
• SelectFirstValuePivotTable: Cuando se seleccionan múltiples valores en una segmentación de datos, obtiene solamente el primer valor.
• RefreshAll: Refresca la caché de las 2 tablas dinámicas y las fórmulas de todo el libro de trabajo.

• Módulo modChangeChessboard: Contiene las macros necesarias para interactuar con el tablero de ajedrez:


• ChangeChessboard: Borra todas las piezas del tablero o inicializa el tablero con la posición inicial de las piezas en una partida de ajedrez.
• IniChessboard: inicializa el tablero con la posición inicial de las piezas en una partida de ajedrez.
• DeleteChessboard: Borra todas las piezas del tablero.
• ChangeFont: Cambia la fuente de caracteres de las piezas de ajedrez.
• ChangePiece: Cambia la pieza de ajedrez en una de las celdas que representa un escaque.
• MovePiece: Mueve una pieza de ajedrez a otro escaque del tablero.

• Módulo modChangeProtect: Macros para proteger las hojas:


• ProtectSheets: Protege las hojas sin contraseña.
• UnprotectSheets: Desprotege las hojas.
• ProtectOneSheet: Protege una hoja de los usuarios pero no de las macros, gracias al argumento: UserInterfaceOnly:=True
• UnprotectOneSheet: Desprotege una hoja.

• Módulo modHideExcel: Macros para ocultar o mostrar los menús y barras propias de Excel. Se puede ocultar todo excepto el título de la aplicacion. Como este módulo es un extra de ejemplo, no voy a explicar cada una de las macros. Quien tenga ganas de estudiarlas que las analice ya que son muy claras y simples, como dice el conocido bloguero Robert Mundigl en su blog Clearly and Simply, del que tanto estoy aprendiendo y que recomiendo desde estas líneas. Si has llegado a leer hasta aquí, lo que si recomiendo es optar por no ocultar Excel desde el menú, ya que quitar la ocultación manualmente es un trabajo arduo.

Más juegos de ajedrez en este blog

Desde que uso Excel me gusta escribir publicaciones en el blog sobre ajedrez, aquí tienes unos cuantos ejemplos que espero que te gusten y que hagas comentarios sobre ellos en alguna de las entradas del blog:

• Conversión de Datos 1: Visor de Ajedrez en Excel
• Conversión de Datos 2: Instrucciones del Visor de Ajedrez
• Conversión de Datos 3: Juego de Ajedrez con fórmulas
• Proyecto Chess - PC
• Como generar un diagrama de ajedrez en Excel
• Visor PGN Tablero de Ajedrez Excel
• Cómo hacer un tablero de juego

Posdata:

Cuando escriba el próximo artículo ya estaré felizmente jubilado y mi idea es construir próximamente un tablero con la historia de los 40 años de mi carrera profesional (enlace aquí), en un storyboard dinámico como el que aparece en el siguiente enlace, pero con otra técnica: A practical Example for Dynamic Storyboards

Si detectas alguna traducción incorrecta al cambiar de idioma, dímelo en un comentario o modifica tú mismo la tabla con los idiomas, a partir de la columna C de la hoja 'Idiomas', donde puedes añadir más columnas si quieres más idiomas...