Supongamos esta tabla de ventas por mes y sucursal
Después de aplicar Datos-Subtotales, la tabla se ve así
Apretamos el botón del nivel 2 y veremos la tabla de esta manera
Ahora protegemos la hoja marcando todas las posibilidades de permiso
Si intentamos desplegar algún mes para ver el detalle o cambiar el nivel de agrupamiento, veremos esta nota
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
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: MS Excel
Hola, Jorge.
ResponderBorrarSó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.
Excelente truco, gracias y felicidades por el blog
ResponderBorrarEnhorabuena por tu blog Jorge,
ResponderBorrarEn 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
Fran,
ResponderBorrar¿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.
Excelente truco. No entiendo porqué no lo tiene el producto como opción y hay que recurrir a un truco como este.
ResponderBorrarDe cualquier forma, muchas gracias.
Jorge,
ResponderBorrarLa 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.
Hola Jorge,
ResponderBorrarEntiendo entonces que lo que te pido no es posible, verdad?
Gracias y enhorabuena por tu blog!
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.
ResponderBorrarEsta 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???
ResponderBorrarSe 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.
ResponderBorrarLlevo 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
ResponderBorrar¿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.
ResponderBorrarEn las versiones anteriores a Excel 2007 la opción Filtro Avanzado quedaba activa, aún cuando no señalamos la opción Usar Autofiltro.
Maestraso, tremenda aportación para los que utilizamos esta herramienta día a día en nuestro trabajo. Muchas Gracias
ResponderBorrarDesde Nicaragua, muchas gracias Jorge,
ResponderBorrarhablaste 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
Moises,
ResponderBorrarsi 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 :)
Hola
ResponderBorrarHe inclido la formula y funciona, pero cada vez que abro el archivo no funciona alguna idea por que no funciona
No hay ninguna fórmula en esta técnica, sino código de Vba (macro).
ResponderBorrarAsegurate 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.
No me funciona que estare haciendo mal
ResponderBorrarDado 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.
ResponderBorrarGracias Jorge por este instructivo post...
ResponderBorrarA 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
Sergio,
ResponderBorrarno 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
Para la proteccion con permisos para filtros cambiar los codigos al proteger el libro por este:
ResponderBorrar.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
Hola,
ResponderBorrarUn favor. Podrán copiar el código cuando son varias hojas en el libro? Intento y no me resulta. Muchas gracias! Daniela
Daniela,
ResponderBorrartendrí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
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
ResponderBorrarEstimado, 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.
ResponderBorrarHola que tal.
ResponderBorrarPrimero 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?
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.
ResponderBorrarMuchas gracias por el truco! Lo apliqué a todas las hojas del archivo y funciona 10 puntos. Saludos
ResponderBorrarMuchísimas gracias me ha sido de gran ayuda!!!
ResponderBorrarEs bueno saber que hay gente que se preocupa por difundir su conocimiento por el universo virtual!!
EXCELENTE !!!
ResponderBorrarMuchas gracias!! ha sido de mucha utilidad!
ResponderBorrarCómo hago para que aplique este tipo de macro pero solo a determinadas hojas de un libro?
ResponderBorrarno me interesa que se aplique en todas porque no todas estan agrupadas o protegidas.
Muchas gracias! es muy útil tu blog :)
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.
ResponderBorrarLa 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).
Muchas gracias Jorge,
ResponderBorrarAl 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
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.
ResponderBorrarGracias
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?
ResponderBorrarHola, 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.
ResponderBorrarEste comentario ha sido eliminado por el autor.
ResponderBorrarJorge, 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.
ResponderBorrarHola Angie,
ResponderBorrartendrí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).
Muchas gracias, ha sido justo lo que necesitaba.
ResponderBorrarSaludos.
Muchas gracias, muy práctico y claro.
ResponderBorrarsaludos,
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.
ResponderBorrarJosrge.... listoooo... no he preguntado nada... ya vi como era
ResponderBorrarExcelente, me funciono perfecto. MUCHAS GRACIAS !!!!
ResponderBorrarBuenísimo! me sirvió de mucho. Gracias!
ResponderBorrarBuenísimo aporte. Muchas gracias de verdad.
ResponderBorrarSolo 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?
No se puede arreglar :(
ResponderBorrarHay funcionalidades, como las macros, que quedan desactivadas al compartir un cuaderno. Por favor, fijate en esta nota.
Hola Jorge, gracias por tu rápida respuesta.
ResponderBorrarHe 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
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.
ResponderBorrarBuenas noches:
ResponderBorrarPor 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
Gracias por colaborar.
ResponderBorrarCabe 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.
Muy útil, muchas gracias.
ResponderBorrarDespué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?
Excel online no tiene la capacidad de correr macros. Por eso no te funciona; sólo funcionará en la versión desktop.
ResponderBorrarGran truco pero no graba, al cerrar y abrir el excel ya no se puede desagrupar estando bloqueado, hay alguna solucion a esto??
ResponderBorrarObviamente, 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.
ResponderBorrarPorque cuando lo cierro el excel y lo vuelvo abrir sigue lo mismo...
ResponderBorrarComo puedo hacer
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.
ResponderBorrarHola. Gracias por el aporte, la macro funciona correctamente.
ResponderBorrarDespues de revisar varios blogs, incluyendo este, por fin di con el codigo que funciona para mi caso:
ResponderBorrarTengo un workbook que funciona como cotizacion, la idea es que los clientes puedan desplegar los grupos y que puedan agregar las cantidades en ciertas celdas para ver el precio total.
El codigo que funciona es este: (deben colocarlo en ThisWorkbook)
Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
With ws
.Unprotect Password:="clave"
.EnableOutlining = True
.Protect Password:="clave", UserInterfaceOnly:=True
End With
Next
End Sub
Como hago lo mismo pero en Google Sheets?
ResponderBorrarNo conozco Google Sheets lo suficiente para orientarte. Te sugiero que consultes en alguno de los muchos foros de Google Sheets.
Borrar