Complemento para mostrar u ocultar hojas del cuaderno activo de Excel

En el desarrollo de modelos que me encargan mis clientes suelo utilizar hojas auxiliares. Estas hojas contienen listas, tablas de datos o cálculos y es imperativo que el usuario no pueda introducir cambios en ellas. Por ese motivo estas hojas están ocultas en el modelo, por lo general estableciendo la propiedad Visible de la hoja a xlVeryHidden.

Cuando el modelo es complejo, con muchas hojas ocultas, los métodos tradicionales de Excel (a través del menú contextual de la hoja o cambiando la propiedad en el editor de Vb) consumen mucho tiempo. Si bien podemos ocultar un grupo de hojas seleccionándolas con Ctrl, para mostrarlas tendremos que hacerlo una por una. Lo mismo con la propiedad VeryHidden .

Para agilizar mi tarea he desarrollado un complemento que permite:

• Elegir una o más hojas y convertirlas en invisibles
• Elegir una o más hojas y volverlas visibles
• Volver todas las hojas del cuaderno visibles

Para que la macro pueda aplicarse a todo cuaderno activo podemos copiar el código en el cuaderno Personal o instalarlo como complemento (complementos (Add-Ins) son elementos que permiten extender las capacidades de Excel).

Una vez instalado, el código funciona de esta manera en este cuaderno con seis hojas de las cuales tres están ocultas



Para instalar el código en el libro Personal, hay que descargar este archivo, abrir el editor de Vba (Alt + F11) y copiar los códigos y el formulario (se puede hacer arrastrando los elementos con el mouse como puede verse en la animación)

Después de abrir el cuaderno “Manejo de hojas.xlsm”, hay que activar el editor de Vba copiar/arrastrar los elementos como se muestra en la animación

Una vez instalado en el Personal podemos crear una icono en la barra de acceso rápido para activar la macro cómodamente como se muestra en este video o usar el atajo de teclado (Ctrl+Mayúsculas+A)



Otra posibilidad es instalar el complemento para lo cual hay que descargar este archivo. Una vez descargado existen dos posibilidades:

• Guardarlo en la carpeta de complementos de Excel (por lo general será C:\Documents and Settings\UserName\Application Data\Microsoft\AddIns) , lo cual hará que automáticamente aparezca en el formulario de los complementos
• Guardarlo en alguna otra carpeta y usar el botón “Examinar” para localizar el archivo

Una vez instalado el complemento (señalando la casilla al lado del nombre), podemos usarlo con el atajo de teclado o crear un icono en la barra de acceso rápido con la misma técnica mostrada anteriormente.

Las descargas de los archivos son gratuitas. También se puede acceder a la hoja de descarga aquí.

Acutalización (13/06/2013): los archivos han sido modificados para el caso en que el cuaderno activo esté protegido. Cuadernos protegidos, a diferencia de hojas protegidas, no permiten cambiar la estrucutura del cuaderno y por lo tanto no se puede mostrar hojas ocultas.

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