Complemento para mostrar u ocultar hojas del cuaderno activo de Excel

martes, febrero 05, 2013

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.

22 comments:

MFC 06 febrero, 2013 04:38  

Gracias, muy interesante. Solo que no se porque cada vez que lo ejecuto, desde el icono de la barra de herramientas de aacceso rapido, tambien me abre el archivo "manejo de hojas"... Como hago para que esto no suceda?
Gracias de nuevo

Jorge L. Dunkelman 06 febrero, 2013 07:26  

Tal vea no lo haya señalado con suficiente claridad en la nota; no hay que correr la macro desde el cuaderno "manejo...xlsm" sino copiar el código y el userform al Personal.xlsb (o Personal.xls, depdiendo de la versión de Excel).
O descargar el compemento (manejo...xlam) e instalarlo como muestro en la nota.

Anónimo,  06 febrero, 2013 20:14  

Excelente complemento, funciona de maravilla.
Felicitaciones por el blog.
Jean

Pilar [PALEL] Arguiñáriz 08 febrero, 2013 16:27  

Muchas gracias, excelente.
Añadir que la trayectoria en W8 con sistema y Office en español es:
C:\Users\usuario\AppData\Roaming\Microsoft\Complementos

Jorge,  02 marzo, 2013 12:51  

Justo lo que buscaba, voy a intentarlo. Sabes si me respetara la contraseña de las hojas? Me interesa mantenerla para que solo yo pueda mostrar las hojas. Muchas gracias!
Saludos y enhorabuena por el blog, muy didáctico

Jorge L. Dunkelman 02 marzo, 2013 19:25  

No, la protección de las hojas no impide que estas pueden ser ocultadas o descubiertas. Para impedirlo hay que aplicar protección al libro.
Si aplicas protección al libro hay que agregar algunas sentencias al código para desprotegerlo y volver a protegerlo.
En el código del botón Aceptar (Private Sub cbtOK_Click()) inmediatamente después de

Application.ScreenUpdating = False

agregas

ActiveWorkbook.Unprotect "contraseña"

y antes de Unload ufSheetsSelect, agregas



ActiveWorkbook.Protect "contraseña"

JORGE 04 marzo, 2013 18:27  

Gracias de nuevo Jorge. Alguna posibilidad de hacerlo desde el archivo que nos descargamos como complemento? Me lo he instalado así y funciona, pero efectivamente, si protejo el libro aparece "se ha producido un error 1004 en tiempo de ejecución: no se puede asignar la propiedad visible de la clase Worksheet"
Me recomiendas copiar el diálogo en el cuaderno personal con los cambios comentados mejor, o puedo "modificar" el complemento de alguna forma?
Gracias¡¡¡¡¡¡¡¡

JORGE 04 marzo, 2013 18:50  

Jorge, lo he conseguido, gracias, muchas muchas gracias.

Jorge L. Dunkelman 04 marzo, 2013 20:59  

Jorge,
estaba por decirte que te fijes en mi comentario del 2 de marzo.

DANIEL HERNANDEZ,  11 junio, 2013 22:55  

COMPLEMENTO PARA MOSTRAR HOJAS
Gracias por tu valiosa asesoría. Sigo intentando mostrar hojas ocultas de Excel. Instale la Macro descrita en tu blog y la misma corre hasta trasladar las paginas no visibles a visibles, pero aquí me da el siguiente mensaje:
“Se ha producido el error 1004 en tiempo de ejecución: No se puede asignar la propiedad Visible de la clase Worksheet” y me marca con amarillo el siguiente texto de la macro:
ActiveWorkbook.Sheets(.List(iSh)).Visible = xlSheetVisible

Jorge Dunkelman 12 junio, 2013 07:07  

Parece ser que el cuaderno está protegido. Fijate en mi comentario del 2 de marzo.

Daniel Hernandez,  12 junio, 2013 21:08  

COMPLEMENTO PARA MOSTRAR HOJAS
Nuevamente gracias por tu ayuda. Probé a correr la macro agregando el texto de tus comenetarios del 2 de marzo y aun no se logra mostrar las paginas ocultas por tener contraseña. La macro SI FUNCIONA en otros libros no protegidos. Que deberia hacer para quitar la contraseña y mostrar las hojas en forma simultanea.

Jorge Dunkelman 13 junio, 2013 07:55  

Hola Daniel, el hecho que una hoja esté protegida con contraseña no impide que se pueda ocultar o mostrar. Lo único que impide mostrar u ocultar hojas es que el cuaderno esté protegido lo que impide cambiar su estructura.
Para quitar la protección de las hojas en el código hay que modificar el código del evento Private Sub cbtOK_Click() poniendo la sentencia

ActiveWorkbook.Sheets(.List(iSh)).Unprotect "contraseña"

antes de

ActiveWorkbook.Sheets(.List(iSh)).Visible = xlSheetVisible

y la sentencia

ActiveWorkbook.Sheets(.List(iSh)).Protect "contraseña"

después.

Pero, como te señalo más arriba, esto es innecesario. Te sugiero que me mandes el archivo para que pueda ver cuál es el problema.

zona lerh 25 julio, 2013 18:43  

Simplemente genial, muchas gracias!!

luisEduardo,  16 mayo, 2015 16:09  

Estimado Jorge, seguramente puedas indicarme un link o una solución a esta duda. Instalé un complemento (viene protegido) que cuenta con varias funciones. ¿Cuál sería la instrucción VBA para ejecutar una de estas funciones? Intenté realizar los pasos manualmente con la grabadora encendida pero no se guardó ninguna instrucción. Muchas gracias por tu atención.

Jorge Dunkelman 16 mayo, 2015 16:50  

Luis Eduardo, no me queda claro. ¿La idea es ver el código de las funciones incluidas en el complemento? Para eso tendrías que conseguir la clave para desproteger el complemento o pedirle el código al autor. La grabadora graba las acciones que hacés vos, no el código que corre.

luisEduardo,  17 mayo, 2015 02:45  

Gracias por atender mi consulta. No, no deseo conocer el código. Al instalar el complemento se me colocó una nueva ficha y dentro de ella varios grupos u opciones. Quisiera ejecutar 1 de esa funciones, pero si la ejecuto con la grabadora de macros no me aparece ninguna instrucción grabada. Y no se cómo llamar con Application.Run a la función que necesito.

Jorge Dunkelman 17 mayo, 2015 07:03  

Si quieres usar una función de un complemento en tu código primero tienes que crear una referencia en el editor de Vb.
Fijate en esta nota (en inglés).

luisEduardo,  17 mayo, 2015 17:36  

¿Podría enviarte el complemento o imágenes para que observes cómo se ven las referencias? Aparece como VBA Proyect (nada mas) y en 'Ubicación' si compruebo que se trata del complemento.... pero con ese nombre, cuando lo tildo aparece el mensaje de error: 'El nombre entra en conflicto con un módulo, etc' Comentame si puedo enviarte el complemento. Gracias por tu atención.

Jorge Dunkelman 17 mayo, 2015 19:00  

Luis Eduardo, te sugiero que consultes en alguno de los foros de Vba.

Unknown 20 septiembre, 2015 13:18  

Esto se publico el 60/02/13:
Tal vea no lo haya señalado con suficiente claridad en la nota; no hay que correr la macro desde el cuaderno "manejo...xlsm" sino copiar el código y el userform al Personal.xlsb (o Personal.xls, depdiendo de la versión de Excel).
O descargar el compemento (manejo...xlam) e instalarlo como muestro en la nota.
No consiguo hacerlo; serias tan amable de "explicarlo mejor. Mil GRACIAS anticipadas

Jorge Dunkelman 20 septiembre, 2015 16:32  

¿Qué es lo que no conseguís hacer? Te sugiero ver esta nota en caso que no veas o no exista el cuaderno Personal.
En esta otra nota podés ver cómo instalar un complemento (casi al final del post donde empieza el párrafo;"Para instalar el complemento usamos el menú..."). En el post muestro c[omo hacerlo en Excel 97-2003. EL proceso en Excel 2007-2013 es el mismo pero usando el menú Programador-Complementos.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP