89 - Un Calendar (u otra herramienta o control) para alimentar varios controles dentro de un Userform.

 Siguiendo con el tema del video Nº 87 (pasar datos de una lista), en esta entrada dejo la guía de ese modelo, más otros casos de cómo compartir controles como un Calendario (u otras herramientas y/o controles).

Caso 1: Pasar datos de una lista a campos del mismo Userform. Se envía el contenido de cada columna a los controles correspondientes:

TextBox8 = ListBox1.List(ListBox1.ListIndex, 0)

ComboBox3 = ListBox1.List(ListBox1.ListIndex, 1)

 

Caso 2: Pasar datos de una lista ubicada en otro formulario. Anteponer, en cada control, el nombre del formulario que recibe los datos de la lista.

UF_UsoMaterial.TextBox8 = ListBox1.List(ListBox1.ListIndex, 0)

UF_UsoMaterial.ComboBox3 = ListBox1.List(ListBox1.ListIndex, 1)

NOTA: Otra manera de escribir el código sería con la estructura With... End With

With UF_UsoMaterial

.TextBox8 = ListBox1.List(ListBox1.ListIndex, 0)

.ComboBox3 = ListBox1.List(ListBox1.ListIndex, 1)

End With

Caso 3: Pasar datos de un Calendar (u otro control como ComboBox, TextBox para ingreso de clave, una Calculadora u otras herramientas) a varios campos dentro del Userform.
La programación será para este caso:

En un módulo, declarar una variable pública para guardar la fecha seleccionada.

Public FechaCalendario As String

Cada botón de llamada tendrá las instrucciones para llamar al control Calendar y guardar el contenido de la variable en el textbox o campo que corresponda. (Ajustar los nombres de los controles)

Private Sub CommandButton8_Click()

Calendario.Show

TextBox10 = FechaCalendario

End Sub

En el Userform que contendrá el Calendario, buscar el evento Clic y desde allí guardar la fecha seleccionada en la variable ya declarada.

Private Sub CAL_Click()

FechaCalendario = Fechas(DD)

Unload Me

End Sub

        Ver video Nº 89 desde aquí.

        Ver video Nº 87 desde aquí.

         Solicitar el documento con la guía al correo: cibersoft.arg@gmail.com

88- Filtro Avanzado en Google Sheets

Si bien en Google Sheets encontramos casi todas las herramientas que presenta la hoja Excel, algunas no están tan visibles o no se ejecutan del mismo modo.

Este sería el caso del Filtro Avanzado. No existe como 'opción'. Sino que debe resolverse de otro modo.

1- Una primera sugerencia, cuando se trate de un filtrado sencillo con solo un par de criterios, es la que se observa en las siguientes imágenes:

- Desde el menú Datos seleccionar 'Nuevo Filtro'.

- Seleccionar la columna a filtrar y desde el desplegable optar por 'La Fórmula personalizada es..'

- Utilizar las funciones propias de Excel para obtener todas las condiciones o criterios para el filtrado.

En este ejemplo se solicitó que solo muestre los productos cuyo precio sea >= 10000. Y con la función Y (and) se agregó un segundo criterio para mostrar solo aquellos productos cuyo nombre comienza con la letra C en adelante. No se mostrarán productos cuyo nombre comience con A y B. 

Observar que en cada argumento, separados por punto y coma, se hace mención al rango completo.

Esta manera de resolver un filtro tiene como desventaja que debemos introducir la fórmula en un espacio reducido, lo que hará que sea una tarea complicada cuando tengamos más argumentos. 

Además, filtrando en cada columna, se van ocultando filas lo que podría hacernos perder información en otro sector de la hoja.

2- Para un correcto Filtro Avanzado las Google Sheets nos presentan la función FILTER desde el menú Insertar, Funcion.

Seleccionamos una celda fuera de la tabla de datos (en la imagen es G6) y allí escribiremos la función con los siguientes argumentos:

=FILTER( rango a filtrar; condición o criterio1; condición o criterio2;....)

Para nuestro ejemplo sería:

        =FILTER(B6:E50;E6:E50 >= 10000;C6:C50 >= "C")

NOTA: podemos escribir la fórmula en la celda seleccionada o en la barra de fórmulas lo que resulta aún más cómodo.

Podemos emular el Filtro Avanzado de Excel, colocando los criterios en celdas o rangos.

Por ejemplo colocaré el primer criterio en la celda E4 y el del nombre en la celda C4. De este modo en lugar de colocar los valores en la fórmula colocaremos la referencia (absoluta) de la celda donde se encuentran.

Aquí para mostrar otra opción modifiqué la segunda condición dejándola en < "D".

=FILTER(B6:E50;E6:E50 >= $E$4;C6:C50< $C$4)

Como podemos observar, esta opción tiene la ventaja de no afectar a nuestra tabla original ni tampoco ocultarnos las filas. Además podemos mover la tabla resultante a otra ubicación u hojas.

Otro ejemplo con rango de criterios donde se establecen 3 condiciones: un rango de precios y nombres que inician con A hasta C. 

La fórmula para este modelo quedaría de este modo:

=FILTER(B6:E50;E6:E50 >= $J$1;E6:E50 <= $J$2;C6:C50< $J$3)

Este tema continuará ...... ;)

Ver video Nº 88 desde aquí.

87- Pasar datos de un ListBox a otros campos y a otros Userforms.

 Al momento de seleccionar algún elemento que tengamos en un control ListBox, dentro de un Userform, podemos optar por pasar cada columna de la lista a controles dentro del mismo formulario o a controles ubicados en otros Userforms. También por supuesto podemos optar por enviarlos a una hoja Excel.

A continuación imágenes y códigos para cada modelo.

CASO 1: pasar algunas columnas del elemento seleccionado a campos dentro del mismo Userform. Aquí se toman los datos de la hoja base de Materiales.

Invocaremos al evento Doble Clic del control Lista. Pasaremos a los diferentes controles del formulario el contenido de algunas columnas de ese elemento seleccionado. La fila seleccionada en la lista se reconoce como ListBox1.ListIndex. Y las columnas inician en el valor 0. 

Por ejemplo, el valor 'Cod' de la fila seleccionada en la imagen, se reconoce como: ListBox1.List(ListBox1.ListIndex, 0)

Ajustar el nombre de cada control que tengan en el formulario.

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean

 'si no se selecciona algún elemento de la lista, cancela. 

If ListBox1.ListIndex < 0 Then Exit Sub

‘se guarda en la variable el contenido de la primera columna

dato = ListBox1.List(ListBox1.ListIndex, 0)

‘se lo busca en la hoja Base de Materiales, previamente llamada como ‘homa’ (*)

Set busco = homa.Range("B:B").Find(dato, LookIn:=xlValues, lookat:=xlWhole)

‘si el dato fue encontrado, se pasan algunas columnas a la hoja

If Not busco Is Nothing Then

    TextBox9 = homa.Range("C" & busco.Row)

    TextBox8 = homa.Range("B" & busco.Row)

    TextBox3 = homa.Range("D" & busco.Row)

    TextBox5 = homa.Range("E" & busco.Row)

End If

‘se posiciona en el primer control a rellenar por parte del usuario

ComboBox1.SetFocus

‘se quita la selección a la lista.

ListBox1.ListIndex = -1

End Sub

(*) En el evento Initialize, se declaró una variable (pública) llamada 'homa' para guardar el nombre de la hoja base de Materiales.

CASO 2: pasar algunos campos del elemento seleccionado a controles dentro de otro Userform. 

En el Userform de la izquierda se observa una lupa que tendrá asociada la subrutina que llama al formulario de la derecha. Ajustar los nombres del control imagen y del segundo formulario.

Private Sub Image1_Click()     'botón que llama al segundo formulario.
UserFactura.Show
End Sub

En el segundo Userform (UserFactura) se programó también el evento Doble Clic. Y en este modelo, se pasó cada columna del elemento seleccionado a los controles del primer Userform (UF_UsoMaterial). Ajustar todos los nombres a los que correspondan en vuestros modelos.

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

‘se controla que esté seleccionado un elemento de la lista

If ListBox1.ListIndex >= 0 Then

     ‘se pasan algunas columnas de la lista a los controles del primer UF

    With UF_UsoMaterial

        .TextBox8 = ListBox1.List(ListBox1.ListIndex, 0)

        .TextBox9 = ListBox1.List(ListBox1.ListIndex, 1)   

        .TextBox3 = ListBox1.List(ListBox1.ListIndex, 2)

        .TextBox5 = ListBox1.List(ListBox1.ListIndex, 3)

    'revisar qué otros campos debieran pasarse al otro formulario

    '....

    'se cierra este uf quedando en el de Factura. Se quita el filtro a la hoja.

    If ActiveSheet.FilterMode = True Then ActiveSheet.ShowAllData

    'se cierra este segundo formulario para volver al primero.

    Unload Me

End If

End Sub

Nota: en el libro se incluye la macro de búsqueda adaptada del ejemplo del video Nº 36: Filtro dinámico.

ATENCIÓN: es importante que los 2 userforms tengan el mismo valor en su propiedad ShowModal. 

CASO 3: pasar algunos campos del elemento seleccionado a una segunda hoja Excel. 

Siguiendo el ejemplo anterior, para pasar elementos seleccionados de una lista a una hoja simplemente se cambiará el destino reemplazando el nombre del primer formulario y su campo por el de la fila y columna en la hoja destino (en el ejemplo es SALIDAS).

Aquí se utilizó el evento Click aunque bien podría ser también el DoubleClick.

IMPORTANTE: No se deben desarrollar los 2 eventos en un mismo Userform. Ya que al intentar presionar doble click ya se ejecuta el simple click ;)

Ejemplo:

Private Sub ListBox1_Click()

‘se controla que esté seleccionado un elemento de la lista

If ListBox1.ListIndex >= 0 Then

‘se pasan algunas columnas de la lista a una hoja resumen de Salidas

    '-------------- OPCIONAL: PASAR A UNA HOJA DE SALIDAS

    X = Sheets("SALIDAS").Range("a" & Rows.Count).End(xlUp).Row + 1

    With Sheets("SALIDAS")

        .Range("A" & X) = ListBox1.List(ListBox1.ListIndex, 0)

        .Range("B" & X) = ListBox1.List(ListBox1.ListIndex, 1)

        .Range("C" & X) = ListBox1.List(ListBox1.ListIndex, 2)

        .Range("D" & X) = ListBox1.List(ListBox1.ListIndex, 3)

    End With

    '----------------------------------------------------------------------

‘otros pases

    Unload Me

End If

End Sub

Descargar el libro de ejemplo desde aquí o solicitarlo al correo cibersoft.arg@gmail.com

Ver video Nº 87 desde aquí.

Otros videos relacionados: 

      Nº 36: Filtro dinámico desde un Userform.

      Nº 39: RowSource. Actualizar desplegables desde otro formulario.

      Nº 9:  Macros compartidas.

R2 - Cómo trabajar con Fechas anteriores al año 1900.

 Si bien ya estamos en el 2024, hay tareas que aún requieren cálculos con fechas anteriores al año 1900. Datos históricos, mediciones de eventos climáticos y tantas otras.

Haciendo cálculos de fechas con Excel nos encontraremos con un error, cuando intentamos trabajar o calcular diferencias entre fechas, si alguna (o las 2) corresponden a años anteriores al 1900.

Aquí les comparto un truco o manera de resolverlo: aumentar el número de año en una cifra que supere ese limite. Puede ser 500, 1000 o la cifra que necesiten.

Partimos de una tabla con diferentes fechas. E intentamos obtener el tiempo transcurrido hasta el día de hoy, expresado en años, meses y días.

Ya podemos observar que Excel no nos está reconociendo como una fecha válida el contenido de la celda A6, alineándolo sobre el margen izquierdo.

Los pasos a seguir para convertir estos datos en fechas válidas son:

1- Agregar algunas columnas auxiliares para obtener las mismas fechas (también la fecha actual, ver Nota) pero con un incremento que en este ejemplo dejé de 1000 (la menor fecha llegaría entonces a 1921 y con esto ya podré realizar los cálculos).

Separamos los argumentos (día, mes, año) en 3 columnas auxiliares. Recordar que no podremos utilizar las funciones DIA(), MES() ni AÑO() para la fecha de la celda A6.

2- Como aquí queremos calcular el tiempo transcurrido hasta la fecha actual, colocaremos ese dato en G9. En G10 aplicaremos la fórmula mostrada en la imagen y en G11 la siguiente:

=FECHA(G10;MES(G9);DIA(G9))

3- Ahora nos resta 'armar' las nuevas fechas y con ellas realizar el cálculo del tiempo transcurrido.

Las fórmulas utilizadas en la fila 10 son las siguientes que luego se arrastran al resto de las filas.

I10  =FECHA(C10+1000;B10;A10)

J10 =SIFECHA(I10;$G$11;"Y")     

K10 =SIFECHA(I10;$G$11;"YM")  

L10 =SIFECHA(I10;$G$11;"MD") 

En la imagen observamos el total de las fórmulas de las columnas auxiliares.

NOTA: en caso de que necesitemos evaluar el tiempo transcurrido entre 2 fechas (no necesariamente la fecha del día) en el punto 1 utilizaremos las col E:G para separar los argumentos de las fechas finales.

Utilizaremos 2 columnas más (I:J) para obtener los años finales con el incremento de 1000 y así poder volver a armar las 'nuevas' fechas de evento y final. 

Las fórmulas en columnas I:P se muestran en la siguiente imagen.

http://aplicaexcel.com/manuales

Visitar el canal de Soluciones Excel desde aquí.

R1 - Rellenar celdas vacías en rangos múltiples.

RESPUESTA:  Si nuestra tabla de datos presenta este aspecto, con celdas vacías en una columna:

y nuestro objetivo es rellenar cada rango vacío con el nombre del departamento de cada grupo, solo tendremos que hacer Doble Clic en cada marcador, como se observa en la siguiente imagen:

 

Solución extraída del Manual de Herramientas Excel que se adjunta como obsequio con la compra de los manuales 500Macros y el de Userforms.

http://aplicaexcel.com/manuales

Visitar el canal de Soluciones Excel desde aquí.

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í.