Una alternativa al control Calendario en Excel 2010 – Monthview

Como mencionamos en el pasado el control Calendar (mscal.ocx) fue removido del paquete Office 2010. Esto genera problemas en aplicaciones y modelos de Excel que fueron desarrollados en versiones anteriores y queremos usarla en Excel 2010.

En la nota mencionada sugerimos tres alternativas posibles: usar el Data Picker, el complemento desarrollado por Sam Radakovitz o descargar e instalar el control. Esta última alternativa implica tener que registrar el control para poder usarlo. El problema con esta última solución es que no podemos registrar el control en Windows 8.

Para los que, como a mí, no les gusta el Data Picker, existe la posibilidad de usar el control Monthview (para las versiones 2007 y 2010 de Excel).

¿Cuándo queremos usar el control? Cuando necesitamos asegurarnos que el usuario ingrese fechas en el rango y en el formato necesarios, librándolo al mismo tiempo de tener que ingresar los separadores (”/” o “-“) de la fecha.

En esta nota mostraré como implementar un modelo sencillo: al elegir una celda determinada aparece el calendario y al elegir una fecha en él, ésta aparece en la celda y el calendario desaparece

Paso 1 – Crear un Userform

En el editor de VB (Alt+F11) creamos un userform que contendrá el control

Cambiamos la propiedad Name a “UfFecha” (no es indispensable pero es una buena práctica) y cambiamos Caption a “Elija una fecha”.

Paso 2 - Insertar el control Monthview

Apuntamos al cuadro ToolBox y abrimos el menú contextual con un clic en el botón derecho del mouse

Elegimos al opción Aditional Controls y marcamos el Microsoft Monthview Control

Este aparecerá ahora en la parte inferior del cuadro ToolBox. Con un clic lo seleccionamos y lo insertamos en el Userform

Paso 3 – Definiciones del control

Al seleccionar el control podemos ver el cuadro de propiedades en el editor. Esto nos permite definir su apariencia y comportamiento. Por ejemplo, definimos la propiedad ShowTodat como True, de manera que al abrirse el control muestre la fecha corriente.

Paso 4 – Códigos de los controles.

Ahora necesitamos escribir los códigos que manejen el uso del control. En nuestro modelo queremos que cuando el usuario seleccione la celda C3 (hemos creado un nombre definido que se refiere a la celda: “clFecha”) aparezca el calendario. Esto lo hacemos usando el evento Worksheet_SelectionChange de la hoja

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Union(Target, Range("clFecha")).Address = Range("clFecha").Address Then
        ufFecha.Show
    End If

End Sub

El código lo ponemos en el módulo de la hoja (abrimos el módulo de la hoja con la opción Ver Código del menú contextual que se abre al hacer clic derecho en la pestaña de la hoja o con un doble clic sobre el icono de la hoja en el editor de Vb)

Ahora necesitamos un código para pasar el valor elegido en el calendario a la celda

Private Sub MonthView1_DateClick(ByVal DateClicked As Date)
    ActiveCell.Value = DateClicked
    Unload ufFecha
End Sub

Este código va en el módulo del Userform que se abre haciendo un doble clic en el Userform (o apretando Ctrl+Tab hasta llegar al módulo)

Para que el control aparezca junto a la celda de la fecha y no en el centro de la hoja, agregamos el evento UserForm_Activate

Private Sub UserForm_Activate()
    With Me
        .Left = ActiveCell.Left + ActiveCell.Width + 25
        .Top = ActiveCell.Top + 150
    End With
End Sub

El modulo del Userform se verá así

Un tip para el final: si hacemos unn clic sobrel el nombre del mes en el control aparecerán unas flechas que nos permiten saltar de año en año

El archivo con el ejemplo puede descargarse aquí.

Intercalar columnas y filas en Excel – un atajo

A menudo surge la necesidad de intercalar columnas o filas en una tabla de Excel. Por ejemplo, en esta tabla de ventas queremos agregar el segundo trimestre

Es decir, intercalar una columna entre B y C y otra entre C y D

Chandoo publica un atajo para realizar esta tarea con un solo clic:

Manteniendo la tecla Ctrl apretada seleccionamos la columna C y después la columna D (un clic para cada selección); abrimos el menú contextual (clic al botón derecho del mouse) y activamos Insertar

Lo mismo podemos hacer con las filas. Supongamos que queremos insertar filas entre Sucursal 1, Sucursal 2 y Sucursal 3; apretando la tecla Ctrl seleccionamos una después de la otra las filas 3, 4 y 5 y usamos el menú Insertar como en el caso anterior

Actualización selectiva de tablas dinámicas en Excel

Como bien sabemos, las tablas dinámicas de Excel no se actualizan automáticamente al cambiar los datos que las alimentan. Dada esta característica existe el riesgo de publicar informes erróneos si nos olvidamos de pulsar el botón Actualizar en el menú de Herramientas de tabla dinámica

En el pasado propuse una macro sencilla para asegurarnos que después de actualizar datos en una hoja, la tabla dinámica se actualice. Se trata de un método un tanto contundente, actualiza todo lo que se cruce a su paso: tablas dinámicas, tablas, etc.

Un lector me pide un método más sutil que permite actualizar únicamente las tablas dinámicas y deje todo lo demás intacto. Mi primer propuesta, que puse en un comentario en la nota en cuestión, fue este código

Sub actualizar_Tabla_Dinamica()
    Dim strPivotName As String

    strPivotName = Application.InputBox(prompt:="Que tabla actualizar?")

    On Error GoTo errCancel 'en caso de apretar Cancel
    ActiveSheet.PivotTables(strPivotName).PivotCache.Refresh

    Exit Sub

errCancel:
Exit Sub

End Sub


En una hoja con varias tablas dinámicas funciona así

El problema con esta macro es que debemos recordar el nombre de las tablas dinámicas. Como pueden ver, el diálogo que se abre tengo que introducir el nombre “TDin1” que identifica a la tabla en cuestión, lo cual puede conducir a errores. Y ya que estamos, unas palabras sobre los nombres de las tablas dinámicas.

Cuando creamos una tabla dinámica, Excel le asigna un nombre por defecto “TablaDinámicaX” donde X es un número de orden. Usando el menú de opciones de las tablas dinámicas podemos cambiar este nombre por algo más significativo (útil en particular cuando escribimos código)

Volviendo a nuestro tema, era obvio que había que escribir un código que permitiera al usuario elegir que tablas o tablas actualizar.

Par lograr esto ya no podemos depender de la función Input, que tiene muchas limitaciones. En su lugar usamos un ListBox que tiene la ventaja de poder realizar selecciones múltiples. Este objeto tenemos que ponerlo en un Userform y crear los eventos para accionarlo y cancelarlo.

El primer paso es crear una Userform donde ponemos los controles List Box y dos CommandButton

Un doble clic en el Userform nos lleva al módulo correspondiente donde ponemos los códigos para manejarlo

Private Sub cbtAceptar_Click()
    ufListaTablas.Hide
End Sub

Private Sub cbtCancelar_Click()
    Unload Me 'ufListaTablas
End Sub

Ahora, en un módulo común ponemos el código de la macro

Sub actualizar_Tabla_Dinamica()
    Dim pt As PivotTable
    Dim lbItemsCount As Integer
    Dim iX As Integer
  
    'poner los nombres de las tabla en un List Box
    With ufListaTablas.lbPivots
        .Clear
        For Each pt In ActiveSheet.PivotTables
            .AddItem pt.Name
        Next pt
        lbItemsCount = .ListCount
    End With
  
    'abrir el List Box para elegir la tabla a actualizar
    ufListaTablas.Show
  
    On Error GoTo errCancel 'en caso de apretar Cancel
  
    With ufListaTablas.lbPivots
    For iX = 0 To lbItemsCount - 1
        If .Selected(iX) = True Then
            ActiveSheet.PivotTables(.List(iX)).PivotCache.Refresh
        End If
    Next iX
    End With
    
    Unload ufListaTablas
  
    Exit Sub
  
errCancel:
Exit Sub

End Sub


El ListBox muestra las tablas dinámicas de la hoja activa y nos permite elegir cuáles actualizar.
Para que la macro pueda usarse en cualquier cuaderno abierto sugiero guardarla en el cuaderno Personal y crear una icono en la barra de herramientas de acceso rápido.

El cuaderno con el ejemplo puede descargarse aquí.