Uso de tablas en hojas protegidas

sábado, mayo 05, 2012

No es necesario que nos extendamos sobre las bondades del uso de Tablas (Listas en Excel 2003). Sin embargo existe un inconveniente que aún no ha sido tratado por Microsoft. En una hoja protegida las tablas dejan de expandirse automáticamente.

En general usamos tablas para introducir datos en una base de datos plana y que los objetos que hayamos creado a partir de la tabla (gráficos por ejemplo) se adapten automáticamente y/o para evitar la necesidad de copiar fórmulas a lo largo de una columna cuando agregamos filas. En este tipo de situaciones no existe una necesidad real de proteger la hoja. La necesidad puede surgir si tenemos una o más columnas en la tabla con fórmulas y queremos evitar que el usuario las pueda modificar o queremos ocultarlas.

Si bien el menú de protección incluye las posibilidades Insertar Columnas e Insertar Filas,



la tabla dejará de agregarlas automáticamente en una hoja protegida.

Una solución posible es agregar filas en la tabla de antemano. Esto es relativamente razonable si el modelo tiene lógicamente un número definido de filas (por ejemplo, comparación de ventas-plan por mes de un año determinado).

Una solución más dinámica es usar una macro, más precisamente un evento. Supongamos este modelo



El rango A1:E8 lo hemos definido como tabla y debe expandirse automáticamente a medida que agregamos datos. La tabla H1:I6 contiene el plan de ventas mensual de las sucursales y la usamos en la fórmula de la columna D de la tabla

=BUSCARV([@Sucursal],tblPlanMensual,2,0)

Para que la tabla se autoexpanda, también en una hoja protegida, abrimos el módulo del editor Vba de la hoja (clic con el botón derecho a la pestaña de la hoja)



y ponemos este código

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Count > 1 Then Exit Sub
    If Target.Row = 1 Then Exit Sub
    If Target.Column <> 1 Then Exit Sub
    With ActiveSheet
        .Unprotect
        .ListObjects("Tabla1").Resize Target.CurrentRegion
        .Protect
    End With
 
End Sub


Explicación del código:
Las tres primeras líneas del código interrumpen el evento si:

“If Target.Count > then Exit Sub” – el rango seleccionado incluye más de una celda.

“If Target.Row = 1 Then Exit Sub” - si la fila es 1

“If Target.Column <> 1 Then Exit Sub” – si la columna no es A.

El resto del código quita la protección (.Unprotect), expande el rango de la tabla (.Resize Target.CurrentRegion) y vuelve a proteger la hoja.

“Tabla1” es el nombre de la tabla, visible en el menú Tabla cuando activamos alguna de sus celdas



Es una buena práctica cambiar el nombre por defecto (Tabla1) por un nombre más significativo. El cuaderno con el código puede descargarse aquí.

18 comments:

Juani 11 mayo, 2012 20:49  

Buena info Jorge, justo ayer estuve lidiando con este tema y no pude resolverlo.
Probe con "Permitir que los usuarios modifiquen rangos" dentro de la pestaña "Revisar" pero no pude hacer que me tome un rango variable.
Gracias

Miguel Angel Miniguano 10 junio, 2012 14:42  

Muy buen aporte y un tema de gran utilidad

Gracias

Anónimo,  22 octubre, 2012 01:57  

Escribo el Código y no me funcionó (igualmente intenté copiarlo directamente de esta page y nada). Bajé el archivo y nada!.... ¿a qué se puede deber que no funcione (no actualiza la tabla, no la expande)?

Jorge L. Dunkelman 22 octubre, 2012 19:55  

¿Dónde estás escribiendo el código?

Anónimo,  28 octubre, 2012 22:14  

pues en vba, justamente como lo indicas acá: como un evento para la hoja (en la hoja dí "ver código" para escribirlo). Luego modifiqué el nombre de mi objeto (cambiando Hoja1 por RCI, que es como se llama mi tabla)...

Anónimo,  12 diciembre, 2012 08:05  

Hola, muchas felicidades por tu blog, es genial, hace unos días que estoy navegando por aquí y he encontrado muchas cosas nuevas que me han servido mucho.
Respecto a esta publicación en particular, tengo una tabla en excel que se va llenando con los datos que ingresan los usuarios, obviamente esta protegida, al insertar este código funciona muy bien, siempre y cuando vaya avanzado una fila y en la columna A ingrese un dato, sin embargo, si me posiciono en otra celda que no sea en la siguiente fila en el rango de la tabla, marca un cuadro con esto: error '1004' en tiempo de ejecución, El rango especificado no es valido..., con un boton de finalizar y depurar.
Yo presiono finalizar, para continuar con la captura, pero para los usuarios que la utilizan es muy recurrente que les aparezca esta ventana.
La verdad no se nada de macros, solo vi esta publicación, copie y pegue tal cual, y funciona bien, salvo este detalle, ¿hay alguna forma de hacer que este cuadro no aparezco o tal vez el código de la macro tenga algo que ver?.

Saludos y muchas gracias por compartir tu saber con nosotros.

Jesús.

Jorge L. Dunkelman 12 diciembre, 2012 18:08  

Hola Jesús,
eso sucede porque el evento supone que estás ingresando valores en la tabla. Una forma de evitar el error es usar un comando (On error resume next) que ignora el error y sigue corriendo el código. El problema sería que los datos ingresados después del error estarían fuera del rango de la tabla.
Otra forma sería agregar un código para manejar el error que advierta al usuario y lo "obligue" a ingresar un dato en la columna A antes de seguir adelante.

RafaPz 01 agosto, 2013 18:23  

Beunas, a mi me funciono perfectamente, lo unico es que la tabla me va agregando filas automaticamente con solo posicionarme en la siguiente fila sin ni siquiera insertar datos, como hago para que la tabla solo me agregue filas cuando ingreso datos y no solo con posicionarme??

Jorge Dunkelman 02 agosto, 2013 18:38  

Hola RafaPz, no había prestado atención a ese detalle. La solución es usar el evento Change en lugar del SelectionChange. Además hay que agregar un control de errores para el caso que se cambie alguna celda en la columna 1 que no sea adyacente a la tabla. En dfinitiva el código debe quedar así

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Row = 1 Then Exit Sub
If Target.Column <> 1 Then Exit Sub
On Error Resume Next
With ActiveSheet
.Unprotect
.ListObjects("Tabla1").Resize Target.CurrentRegion
.Protect
End With
On Error GoTo 0

End Sub

Anónimo,  30 mayo, 2014 05:33  

Gracias por el aporte, pero que sucede cuando la tabla tiene totales

Jorge Dunkelman 01 junio, 2014 07:45  

Puedes probar con este código

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If Target.Row = 1 Then Exit Sub
If Target.Column <> 1 Then Exit Sub
On Error GoTo RestoreEvents
Application.EnableEvents = False
With ActiveSheet
.Unprotect
.ListObjects("Tabla1").ShowTotals = False
.ListObjects("Tabla1").Resize Target.CurrentRegion
.ListObjects("Tabla1").ShowTotals = True
.Protect
End With
Application.EnableEvents = True

Exit Sub

RestoreEvents:
Application.EnableEvents = True

End Sub

Al moverte con TAB, la fila del total desaparece; puedes moverte a la nueva fila con la flecha con lo que aparecerá luna nueva fila en la tabla y el total.

Hugo Ojeda 02 julio, 2014 06:32  

Felicitaciones por el Post, al principio no me funcionaba el código de ninguna manera, luego advertí que en la celda A1 de mi hoja de cálculo tenía un texto que hacía de título de mi tabla, eliminé esa fila y desde ese momento empezó a funcionar. Gracias por el aporte.

Anónimo,  28 marzo, 2015 06:43  

Que tal que bueno tu sitio web!!! eres un genio!!! pero sabes la formula aunque sea la última ingresada ... tiene un detalle para que quede bien!!! si bajo una celda y me muevo dentro de la tabla desde otra columna que no sea la primera ... la hoja queda desprotegida y los usuarios pueden dañar las formulas etc. ... no habrá posibilidad de mirar el código? para que evalué a partir de la posición y que se encuentre desde la columna 1 hasta el fin o largo de la tabla? saludos!!!

Anónimo,  28 marzo, 2015 06:47  

Disculpa, en los ejemplos bajando por la tabla protegida, hasta la última fila, agrega correctamente una nueva, si baja 1 más tira error o como el último código publicado debajo, no hace nada "PERO" si al hacer con la flecha o cursor saltar hacia la derecha e ingresar a la tabla desde otra columna que no sea la primera, sigue desprotegida. Aparte si tengo una columna en medio de la tabla "bloqueada" por ejemplo la columna E y hago hacia abajo en la última fila "vacía" en vez de tirar error o no hacer nada me agrega una nueva desde la columna D y F ??

Jorge Dunkelman 28 marzo, 2015 13:26  

Para que el evento funcione cuando se agregue algún dato a la tabla en cualquier tendríamos que modificar el código del evento de esta manera

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error Resume Next

If Target.Count > 1 Then Exit Sub
If Target.Row = 1 Then Exit Sub
If Target.Column > 5 Then Exit Sub
With ActiveSheet
.Unprotect
.ListObjects("Tabla1").Resize Target.CurrentRegion
.Protect
End With

On Error GoTo 0

End Sub

donde 5 es la última columna de la tabla. La orden "On Error Resume Next" soluciona el problema mencionado en el segundo comentario.

Gouthamma 25 enero, 2016 23:28  

Buen Aporte pero tiene un par de problemas: 1. pide la contraseña cada ves que quiero ingresar un dato me pide la contraseña del bloqueo y al ingresar el dato lo bloquea sin contraseña. y la idea de proteger tu libro es 1 que no lo modifiquen y que no te plagien lo hecho

Jorge Dunkelman 26 enero, 2016 18:46  

Estimado,

si la hoja está protegida con contraseña hay que modificar levemente el código; donde aparece Unprotect debe ponerse Unprotect "xxx" donde xxx es la contraseña. Lo mismo al volver a proteger (Portect "xxx").
En cuanto a las contraseñas, las de Excel son muy débiles. En la Web hay cientos de aplicaciones gratis para romper contraseñas de Excel en fracciones de segundos. La idea de la contraseña es evitar que se hagan combios involuntarios en la hoja. Para evitar plagios tendrías que usar otras herramientas.

Willy Salas 29 junio, 2016 18:05  

Muchas gracias...Felicitaciones muy buen trabajo

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP