Agrupar y desagrupar filas en hojas protegidas

domingo, noviembre 08, 2009

Ya en el pasado hemos hablado de la funcionalidad Datos--Subtotales en Excel. Esta funcionalidad tiene un serio problema: no funciona si la hoja está protegida.
Supongamos esta tabla de ventas por mes y sucursal



agrupar en hojas protegidas

Después de aplicar Datos-Subtotales, la tabla se ve así



agrupar en hojas protegidas

Apretamos el botón del nivel 2 y veremos la tabla de esta manera




agrupar en hojas protegidas

Ahora protegemos la hoja marcando todas las posibilidades de permiso



agrupar en hojas protegidas

Si intentamos desplegar algún mes para ver el detalle o cambiar el nivel de agrupamiento, veremos esta nota

agrupar en hojas protegidas

Es decir, Excel no tiene una opción para permitir agrupar o desagrupar líneas en una hoja protegida.

Una solución obvia es desproteger la hoja antes de efectuar el cambio. Pero esto puede contradecir nuestra intención (que el usuario no pueda modificar los datos en la hoja).


La solución consiste en proteger la hoja programáticamente, es decir, con una macro. En este caso se trata de un evento WorkBook_Open y el código lo ponemos en el módulo del objeto ThisWorkbook

Private Sub Workbook_Open()
    With Worksheets("Hoja1")
        .EnableOutlining = True
        .Protect Password:="", _
        Contents:=True, UserInterfaceOnly:=True
    End With
End Sub


agrupar en hojas protegidas


En este código hemos dejado la contraseña en blanco (.Protect Password:=""), lo que permite al usuario quitar la protección. Si queremos agregar la contraseña la pondremos entre las comillas.
Como hemos usado el evento Workbook_Open, cada vez que se abre el cuaderno la protección entra en efecto.


De esta manera hemos bloqueado las celdas protegidas, pero permitimos a nuestros usuarios agrupar y desagrupar las líneas.



Technorati Tags:



59 comments:

Mariano,  10 noviembre, 2009 09:34  

Hola, Jorge.

Sólo quería comentarte si podría hacer algo parecido con Autofiltro. Es decir, cuando protejo una hoja, pongo el permiso "Usar autofiltro", pero luego no me deja usarlo, ya que me sale el mensaje que viene a decir que la hoja está protegida.

Anónimo,  10 noviembre, 2009 09:45  

Excelente truco, gracias y felicidades por el blog

Fran 02 marzo, 2010 19:29  

Enhorabuena por tu blog Jorge,

En relación a este post te quería comentar si se podría añadir algún código a la macro que permitiera la autocorrección de las fórmulas, en concreto cuando se expanden automáticamente las tablas 2007 (o listas 2003).

Gracias de antemano

Jorge L. Dunkelman 03 marzo, 2010 18:26  

Fran,
¿cuál es la intención de autocorrección? En tablas (o listas de XL2003), las fórmulas y los atributos de las celdas de cada columna se expande automáticamente al agregar datos.

Anónimo,  16 marzo, 2010 17:42  

Excelente truco. No entiendo porqué no lo tiene el producto como opción y hay que recurrir a un truco como este.
De cualquier forma, muchas gracias.

Fran,  18 marzo, 2010 17:37  

Jorge,
La intención de la consulta es que no se realiza la "autocorrección" (al expandirse la tabla 2007 / lista 2003) cuando está protegida la hoja. Por eso te lo pregunto al hilo de tu explicación de como has arreglado esto para la agrupación de celdas, por si se puede hacer también.
Un saludo.

Fran 11 abril, 2010 17:15  

Hola Jorge,
Entiendo entonces que lo que te pido no es posible, verdad?
Gracias y enhorabuena por tu blog!

Jorge L. Dunkelman 11 abril, 2010 18:37  

No, mil disculpas pero me olvidé de tu consulta. Es verdad, hay algunas funcionalidades que no funcionan cuando la hoja está protegida. Se podría buscar una solución con Vba.

Anónimo,  14 abril, 2010 00:13  

Esta pag me ha ayudado muchisimo por lo que en primer lugar quiero dar las gracias a los que hacen esta información posible, Muchas Gracias! tomando este mismo ejemplo de Agrupar y desagrupar filas en hojas protegidas, como puedo hacer si quiero aplicar este mismo macro a tres hojas mas???

Jorge L. Dunkelman 14 abril, 2010 07:16  

Se puede crear una rutina para cada hoja, lo que permite establecer una contraseña distinta para cada una, o usar un condicional para evaluar cual es la hoja activa.

Anónimo,  25 mayo, 2010 14:19  

Llevo mucho tiempo haciendo uso y disfrute de este estupendo blog, por tanto, de corazón muchas gracias, y despues insistir en lo planteado por Mariano:Cuando protejo una hoja, pongo el permiso "Usar autofiltro", pero luego no me deja usarlo, he conseguido desbloquear varias columnas, y bloquear otras, consiguiendo poder filtrar pero no me permite utilizar "mostrar todo", por lo que tengo que ir columna por columna buscando el filtro. Muchas gracias

Jorge L. Dunkelman 25 mayo, 2010 18:18  

¿Con cual versión de Excel estás trabajando? Cuando protegemos con la opción Usar Autofiltro, la opción "mostrar todo" queda desactivada, pero podemos filtrar.
En las versiones anteriores a Excel 2007 la opción Filtro Avanzado quedaba activa, aún cuando no señalamos la opción Usar Autofiltro.

Anónimo,  26 octubre, 2010 15:48  

Maestraso, tremenda aportación para los que utilizamos esta herramienta día a día en nuestro trabajo. Muchas Gracias

Moises 04 enero, 2011 07:09  

Desde Nicaragua, muchas gracias Jorge,

hablaste de usar un condicional para evaluar la hoja activa? con estos podrias proteger y desprotger varias hojas de forma simultanea? y si esto es asi, lo cual me parece fantastico, como es posible hacer esto? muchas gracias

Jorge L. Dunkelman 07 enero, 2011 07:51  

Moises,
si con condicional te refieres a usar fórmulas, la respuesta es no. No se puede usar fórmulas para proteger o desproteger hojas (o realizar cualquiier otro cambio). Lo que estamos usando es Vba (macro) y con macros podemos proteger o desproteger hojas en forma simultánea, sólo hay que escribir el código adecuado :)

Anónimo,  26 julio, 2011 22:39  

Hola
He inclido la formula y funciona, pero cada vez que abro el archivo no funciona alguna idea por que no funciona

Jorge L. Dunkelman 27 julio, 2011 07:05  

No hay ninguna fórmula en esta técnica, sino código de Vba (macro).
Asegurate de poner el código en el módulo de ThisWorkbook, tal como indico en la nota. También hay que segurarse que las macros estén habilitadas.

Anónimo,  27 octubre, 2011 17:48  

No me funciona que estare haciendo mal

Jorge L. Dunkelman 27 octubre, 2011 18:48  

Dado que mis funciones telepáticas no están funcionando ultimamente, sería útil que describas lo que estás haciendo o que me envíes el archivo con una explicación.

Anónimo,  30 diciembre, 2011 10:48  

Gracias Jorge por este instructivo post...

A ver si me puedes ayudar, necesitaría proteger varias hojas en un mismo libro, es posible??

He introducido la macro en ThisWorkbook y he añadido las diferentes hojas; With Worksheets ("Hoja1", "Hoja2", ....) pero no me funciona....

Acabo de empezar en el mundo de las macro y la programación con Visual Basic, es decir no tengo muchos conocimientos... ;-))

Gracias y un saludo,
Sergio

Jorge L. Dunkelman 30 diciembre, 2011 17:34  

Sergio,
no funciona porque la sintaxis es incorrecta. Lo que tienes que hacer es un loop (bucle), por ejemplo:

dim sh as worksheet

for each sh in ThisWorkbook.Worksheets
sh.protect
next sh

Daniel,  28 noviembre, 2012 22:30  

Para la proteccion con permisos para filtros cambiar los codigos al proteger el libro por este:
.Protect "", DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True

Esto te permite utilizar filtros y agrupaciones aunque el libro este bloqueado. Cabe mencionar que los filtros ya deben estar colocados antes de bloquear la hoja. Saludos

Anónimo,  30 noviembre, 2012 01:25  

Hola,
Un favor. Podrán copiar el código cuando son varias hojas en el libro? Intento y no me resulta. Muchas gracias! Daniela

Jorge L. Dunkelman 30 noviembre, 2012 16:04  

Daniela,
tendrías que usar un loop para aplicar el código a todas las hoja. Sería

Private Sub Workbook_Open()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
With ws
.EnableOutlining = True
.Protect Password:="", _
Contents:=True, UserInterfaceOnly:=True
End With
Next

End Sub

Anónimo,  25 enero, 2013 23:35  

Hola buenas tardes comunidad, quiero proteger s�lamente algunas columnas no todas y no logro hacerlo como me indica la ayuda "F1" que es seleccionar celdas que deseo bloquear ir a formato de de celdas con click derecho y "proteger celdas" pues no me funciona y ya intente mil maneras y no lo logro, podr�an auxiliarme, gracias. mil. Tulio Espinosa

Jorge L. Dunkelman 27 enero, 2013 07:21  

Estimado, después de definir las celdas ("bloquedado" es la definición por defecto), hay que proteger la hoja usando el menú Revisión-Proteger hoja.

Anónimo,  05 junio, 2013 07:34  

Hola que tal.
Primero que todo quiero agradecer esta gran ayuda.
Tengo una duda.
Porqué en celdas bloqueadas con VBC de la manera que se indicó acá en unas celdas permite escritura y en otras no, teniendo ambas la misma configuración de bloqueo?

Jorge Dunkelman 05 junio, 2013 09:13  

Supongo que te refieres al código (Vba). El código sólo pone o quita la protección. Por lo visto las celdas a que te refieres (supongo que en tu cuaderno) están desprotegidas.

Anónimo,  01 octubre, 2013 01:28  

Muchas gracias por el truco! Lo apliqué a todas las hojas del archivo y funciona 10 puntos. Saludos

Markus Titus Brutus 22 noviembre, 2013 01:45  

Muchísimas gracias me ha sido de gran ayuda!!!
Es bueno saber que hay gente que se preocupa por difundir su conocimiento por el universo virtual!!

Anónimo,  18 marzo, 2014 12:19  

Muchas gracias!! ha sido de mucha utilidad!

Candelaria Carranza 18 marzo, 2014 13:53  

Cómo hago para que aplique este tipo de macro pero solo a determinadas hojas de un libro?
no me interesa que se aplique en todas porque no todas estan agrupadas o protegidas.

Muchas gracias! es muy útil tu blog :)

Jorge Dunkelman 18 marzo, 2014 16:41  

Candelaria, en el ejemplo la macro se aplica sólo a una hoja (Hoja1). Podrías definir en la macro a qué hojas aplicarla. También podrías definifr el evento en el módulo de cada hoja de manera se que aplique sólo a esas hojas.
La explicación es un poco técnica. Si no te queda claro, ponte en contacto conmigo por mail privado (mi dirección en el botón Ayuda, en la parte superior de la plantilla).

Candelaria Carranza 19 marzo, 2014 11:10  

Muchas gracias Jorge,
Al final hice esto:

Private Sub Workbook_Open()

With Worksheets("Hoja 1")
.EnableOutlining = True
.Protect Password:="clave", _
Contents:=True, UserInterfaceOnly:=True
End With

With Worksheets("Hoja 4")
.EnableOutlining = True
.Protect Password:="clave", _
Contents:=True, UserInterfaceOnly:=True
End With

With Worksheets("Hoja 6")
.EnableOutlining = True
.Protect Password:="clave", _
Contents:=True, UserInterfaceOnly:=True
End With

End Sub

y me ha dado resultado :) asique en principio lo tengo solucionado.
Mil gracias por la ayuda, este blog es de mucha utilidad :)
Saludos

Angie Castro 11 marzo, 2015 22:00  

Buenas tardes estoy usando esta macro y me va excelente, ahora necesito agregarle algo y no se como hacerlo, requiero que con las hojas protegidas el libro me permita cambiar el origen de los datos vinculados desde otro archivo.

Gracias

Jorge Dunkelman 12 marzo, 2015 07:30  

Angie, para cambiar el origen de los datos hay que previamente desproteger la hoja. ¿La idea es hacer el cambio manualmente o con código?

Angie Castro 12 marzo, 2015 14:56  

Hola, claro entiendo que para ello habría que desproteger la hoja pero justamente lo que quiero es que se pueda hacer sin tener que desprotegerla ya que es una hoja compartida y no quiero que las demás personas tengan la clave pues me hacen cambios en los formatos. Es por eso que quisiera poder hacerlo con una macro a ver si es posible que los demas usuarios de las hojas puedan cambiar origen de los vinculos sin dañar los formatos y la estructura del archivo.

Angie Castro 12 marzo, 2015 18:28  
Este comentario ha sido eliminado por el autor.
Angie Castro 13 marzo, 2015 16:47  

Jorge, justamente lo que requiero es poder usar la función de editar vínculos - cambiar origen, sin tener que desproteger la hoja ya que son varios usuarios y si comparto la contraseña me dañan los formatos. Gracias por tu ayuda.

Jorge Dunkelman 13 marzo, 2015 17:28  

Hola Angie,
tendríamos que usar otro código (no el evento Open, sino una macro) con el cual el usuario tendría que ingresar la definición del nuevo vínculo; el código luego haría los cambios sin que el usuario tenga que desproteger la hoja.
Te sugiero que te pongas en contacto conmigo por mail privado (fijate en el enlace Ayuda, en la parte superior del blog).

Unknown 22 octubre, 2015 22:12  

Muchas gracias, ha sido justo lo que necesitaba.
Saludos.

Alexader Tangarife Roldán 25 octubre, 2015 19:35  

Muchas gracias, muy práctico y claro.

saludos,

Luis Miguel Corrales 30 octubre, 2015 00:10  

Jorge, el código me resultó super en mis dos hojas, pero me bloquea todas las celdas, tanto las que le quité el desbloqueo como las que no. Tengo listas desplegables y no me deja abrirlas.

Luis Miguel Corrales 30 octubre, 2015 00:28  

Josrge.... listoooo... no he preguntado nada... ya vi como era

Martin Miguel Silva 08 noviembre, 2015 06:27  

Excelente, me funciono perfecto. MUCHAS GRACIAS !!!!

leandro torri 13 noviembre, 2015 04:19  

Buenísimo! me sirvió de mucho. Gracias!

Anónimo,  26 noviembre, 2015 18:09  

Buenísimo aporte. Muchas gracias de verdad.

Solo una cosa que me trae loca!! Me ha funcionado perfectamente y puedo agrupar y desagrupar sin problema. Pero cuando comparto el libro para que más de usuario pueda modificarlo a la vez, en el momento de compartir todo funciona bien, pero al cerrar y abrir de nuevo me sale el siguiente mensaje:
"Se ha producido el error 1004 en tiempo de ejecución.
Error definido por la aplicación o el objeto"

Le doy a finalizar, y ya no funciona agrupar y desagrupar... :(

Alguna idea de como puedo arreglarlo por favor?

Jorge Dunkelman 26 noviembre, 2015 18:28  

No se puede arreglar :(
Hay funcionalidades, como las macros, que quedan desactivadas al compartir un cuaderno. Por favor, fijate en esta nota.

Anónimo,  30 noviembre, 2015 11:58  

Hola Jorge, gracias por tu rápida respuesta.

He revisado la nota que me indicas sobre la funcionalidad de las macros en libros compartidos, y me surge la duda de si lo que no se puede es crear nueva macro en libro compartido (es lo que me parece entender en la nota). Es que en mi caso no se trataría de crear la macro en el libro compartido, sino que la creo antes de compartir, y lo que quiero es ejecutarla. ¿Es esto posible? En este caso... ¿A que podría deberse el error que me da? o mejor dicho ¿Lo puedo solucionar?

Gracias de nuevo por tu paciencia y atención

Jorge Dunkelman 30 noviembre, 2015 15:25  

Como explicaba anteriormente, cuando se comparte un cuaderno hay funcionalidades que dejan de trabajar, entre ellas las macros. Si el cuaderno contiene una macro y no tiene el estatus de compartido, la macro funcionará. Cuando se cambia el estatus del cuaderno a compartido, dejará de funconar.

Argenis Chaffardet 09 noviembre, 2016 00:03  

Buenas noches:

Por si alguien le interesa, aquí estás los códigos que uso cuando tengo varias hojas con agrupaciones de filas o columnas y quiero recorrer todas las hojas y bloquearlas, poder usar autofiltro también


Sub ProtegerHojas()
Dim i As Integer, HojaActual As Integer
Application.ScreenUpdating = False
HojaActual = ActiveSheet.Index 'es para después restaurar
For i = 1 To Sheets.Count
Sheets(i).Select
ActiveSheet.EnableOutlining = True

ActiveSheet.Protect "clave", DrawingObjects:=True, Contents:=True, Scenarios:= _
True, AllowSorting:=True, AllowFiltering:=True, UserInterfaceOnly:=True
Next i
Sheets(HojaActual).Select
Application.ScreenUpdating = True
End Sub


Sub DesprotegerHojas()
Dim i As Integer, HojaActual As Integer
Application.ScreenUpdating = False
HojaActual = ActiveSheet.Index 'es para después restaurar
For i = 1 To Sheets.Count
Sheets(i).Select
ActiveSheet.Unprotect "clave"

Next i
Sheets(HojaActual).Select
Application.ScreenUpdating = True
End Sub

Jorge Dunkelman 09 noviembre, 2016 07:25  

Gracias por colaborar.
Cabe aclarar que habrá que reemplazar "clave" por la contraseña real. También so podría agregar un diálogo que pida al usuario introducir la contraseña.

Unknown 23 enero, 2017 18:38  

Muy útil, muchas gracias.

Después de programar el macro subí el archivo a Excel Online para que varios usuarios lo puedan consultar y actualizar. La agrupación de filas funciona perfecto pero cuando quiero agrupar columnas me sale "La hoja está protegida. Puede que algunos elementos solo se puedan ver pero no modificar." En el archivo fuera de línea funciona la agrupación tanto de filas como de columnas.

Alguna sugerencia?

Jorge Dunkelman 24 enero, 2017 07:13  

Excel online no tiene la capacidad de correr macros. Por eso no te funciona; sólo funcionará en la versión desktop.

Unknown 23 junio, 2017 15:42  

Gran truco pero no graba, al cerrar y abrir el excel ya no se puede desagrupar estando bloqueado, hay alguna solucion a esto??

Jorge Dunkelman 23 junio, 2017 16:48  

Obviamente, después de poner la macro en (asegurate de ponerla en el lugar indicado en la nota y no en módulo común del editor de Vb), hay que guardar el cuaderno para que el cambio tenga efecto la próxima vez que abramos el cuaderno. Lo mismo que con todo cambio que hagamos en el cuaderno.

Miguel Angel Galvez Cruz 13 septiembre, 2017 23:44  

Porque cuando lo cierro el excel y lo vuelvo abrir sigue lo mismo...
Como puedo hacer

Jorge Dunkelman 14 septiembre, 2017 16:54  

Miguel Ángel, además de lo que señalo en el comentario anterior, hay que asegurarse de poner el código del evento en el módulo correspondiente (del objeto ThisWorkbook) y no en un módulo común de Vba.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP