86 - Ejecutar macro cuyo nombre coincide con el texto seleccionado en una lista o combo.

 Ya hemos visto como ejecutar macros de manera dinámica en el video Nº 75:
- desde el evento BeforeDoubleClick de una celda, la macro cuyo nombre es el valor de la celda seleccionada. 
- al seleccionar un elemento desde un ComboBox ubicado en un Userform. La macro tiene por nombre el texto del elemento seleccionado.

En esta entrada  vamos a ver otros casos, con controles dibujados en una hoja Excel, desde el menú Desarrollador/Programador (ver*)
1 - al seleccionar un elemento de un control Lista Desplegable del menú Formularios.
2 - al seleccionar un elemento de un control ComboBox del menú ActiveX.

(*) Si el menú Desarrollador o Programador no aparece en la Cinta de Opciones seguir los pasos mostrados en las siguientes imágenes.

Generalmente asociamos una celda donde guardamos el elemento seleccionado en estos controles del tipo lista. Pero debemos tener presente que el cambio en las celdas al seleccionar algún elemento, no llama ni ejecuta macros desde el evento WorkSheet_Change. 

NOTA: aquí podemos observar que la celda asociada de un control de formulario no guarda su contenido o texto sino su número de índice u orden de los elementos.
Es decir, que si se pudiese tomar el valor de la celda asociada, de todos modos habría que hacer la búsqueda a su ubicación para tomar el texto que luego será el nombre de la macro que vamos a ejecutar.

Por eso vamos a programar el clic o cambio en el Control dibujado, no en su celda asociada.
Para el ejemplo tengo en una hoja una lista en el rango M1:M12 (por ejemplo con los meses del año) y un control desplegable de cada una de las barras: Formulario y ActiveX. 

CASO 1: barra Formulario.

Una vez dibujado el control en la hoja, hacer clic derecho sobre él, opción Formato de control y desde allí establecemos el rango de entrada (M1:M12) y la celda vinculada (E3). Aceptar.

Para programarlo haremos clic derecho sobre el control, opción 'Asignar macro'.

Se nos abrirá la ventana con la lista de macros y la de este control con un nombre que luego podemos modificarlo a nuestro gusto. Presionando el botón 'Nuevo' nos llevará al Editor y en un módulo encontraremos las siguientes líneas (el nombre puede ser diferente)

Sub Listadesplegable1_Cambiar()

End Sub

Allí colocaremos las instrucciones necesarias para llamar a la macro cuyo nombre coincide con el texto que se encuentra en la columna M y en la fila que coincide con el índice del elemento seleccionado. Recordemos que a este control lo vinculamos a la celda E3. 

Sub Listadesplegable1_Cambiar()

    If [E3] = "" Then Exit Sub

    On Error Resume Next

    dato = Range("M" & Range("E3"))

    Application.Run (dato)

End Sub

NOTA: para borrar el contenido de la lista, borrar la celda asociada.

CASO 2: barra ActiveX.

Para trabajar con esta barra previamente pasaremos a Modo diseño (botón que se encuentra al lado del de Insertar en menú Desarrollador o Programador).

Una vez dibujado el control en la hoja, clic derecho sobre él, opción Propiedades.

Allí ingresamos en 'LinkedCell' el valor E7 y en 'ListFillRange' el rango M1:M12.

Y para asociar la macro, al clic derecho sobre el control optaremos por 'Ver código'.

Nos llevará a la Hoja donde se encuentra este control y allí completaremos el código de esta manera (puede ser que aparezca otro nombre de control).

Private Sub ComboBox1_Change()

On Error Resume Next

If ComboBox1 <> "" Then

    Application.Run ComboBox1.Value

End If

End Sub

NOTA: observar que aquí no hacemos mención a la celda asociada, por lo que tampoco sería necesario establecer su propiedad LinkedCell.

CASO 3: en un Userform.

Como aquí utilizamos los mismos controles ActiveX que en la hoja, lo único que cambia es que el evento Change o Click del control se ubica en el mismo Userform en lugar de ubicarse en la hoja.

Este ejemplo lo pueden encontrar en la Entrada de Marzo 2024 o video Nº 75 (aprox.04:20)

Acceder al video Nº 86 desde aquí.

Acceder al video Nº 75 desde aquí.

85 - Agrupar registros en Tabla Dinámica para totalizar por criterios

 Podemos tener una hoja de datos donde los conceptos no están separados por ningún criterio en común, como por ejemplo en estos ejemplos de Productos y Clientes. 
Aquí por error encontramos un mismo producto escrito de manera diferente (con y sin acento) o con nombres similares. Lo mismo pasa en Clientes, donde se puede encontrar un mismo cliente con nombre diferente.

Cuando diseñamos una Tabla dinámica para acumular ventas u otros registros por cada producto o cliente nos encontraremos que la tabla nos dará totales por cada elemento de la hoja Base, siendo 2 clientes diferentes en el caso de Zanella.

Si no podemos cambiar la hoja Base, tendremos que trabajar en la Tabla dinámica agrupando los totales que consideremos que corresponden al mismo concepto .

En el siguiente ejemplo la hoja Base está bien registrada, sin registros repetidos, pero si intentamos totalizar por criterios (Abrazaderas, Capacitores, etc.) nos encontramos que no tenemos ninguna columna que nos identifique ese criterio.

Entonces aquí también haremos uso de la opción AGRUPAR de la Tabla Dinámica. Una vez diseñada la Tabla, ejecutaremos los siguientes pasos:

1 - Seleccionamos, manteniendo la tecla CTRL presionada, todos los campos que vamos a agrupar, por ejemplo de Abrazaderas.

Desde el menú 'Analizar tabla dinámica', Grupo, optamos por Crear grupo de selección.

2 - Se nos colocará una nueva categoría (Grupo1). Desde la barra de fórmulas vamos a reemplazar este nombre por 'Abrazaderas'.

3 - Y este paso lo repetiremos para todos los conceptos que correspondan a un mismo criterio. Aquí hice lo mismo para Acc.Curva y Arco Sierra. 

  

4 - Para que el resto de la tabla no muestre los campos separados entre título o criterio y su registro, vamos a 'contraer' toda la tabla. Seleccionamos desde el primer grupo (Abrazaderas)  hasta el último campo de la tabla. Clic derecho, opción Expandir o contraer, Contraer todo el campo. 

 

5 - A continuación, desde el signo + de cada grupo desplegaremos los detalles de este modo:

 

6- Luego se podrá dar algún formato especial a cada grupo o elemento de la tabla del modo habitual.
Para 'contraer' algún grupo presionar el botón -. Para 'expandir o contraer' toda la tabla, seleccionar la columna de items y con clic derecho opción Expandir o Contraer, todo el campo.

Ver video Nº 85 desde aquí.