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í.
Buena info Jorge, justo ayer estuve lidiando con este tema y no pude resolverlo.
ResponderBorrarProbe con "Permitir que los usuarios modifiquen rangos" dentro de la pestaña "Revisar" pero no pude hacer que me tome un rango variable.
Gracias
Muy buen aporte y un tema de gran utilidad
ResponderBorrarGracias
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)?
ResponderBorrar¿Dónde estás escribiendo el código?
ResponderBorrarpues 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)...
ResponderBorrarHola, 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.
ResponderBorrarRespecto 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.
Hola Jesús,
ResponderBorrareso 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.
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??
ResponderBorrarHola 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í
ResponderBorrarPrivate 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
Gracias por el aporte, pero que sucede cuando la tabla tiene totales
ResponderBorrarPuedes probar con este código
ResponderBorrarPrivate 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.
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.
ResponderBorrarQue 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!!!
ResponderBorrarDisculpa, 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 ??
ResponderBorrarPara 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
ResponderBorrarPrivate 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.
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
ResponderBorrarEstimado,
ResponderBorrarsi 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.
Muchas gracias...Felicitaciones muy buen trabajo
ResponderBorrarBuenas tardes Jorge,
ResponderBorrarEl blog lo encontré de casualidad, me sirve el contenido porque tengo una tabla que debo bloquear para que no dañen el contenido a medida que la diligencia, el problema es que al bloquearla no se expande, como el primer caso que aparece relatado, una vez lei la información publicada y sin saber nada del tema en "ver código" coloque
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
y no me funciona, al bloquear la hoja sigue sin expandirse, será que me podrías ayudar.
Muchas gracias
Disculpas por la demora en responder. Fijate que el nombre de la tabla (Tabla1, en el ejemplo) corresponda al nombre de la tabla en tu cuaderno.
ResponderBorrarhola como sería la macro si la tabla no empieza en A1 sino en B9, teniendo 8 columnas y quiero que al agregar un dato en la 2da columna se actualice la tabla
ResponderBorrarEn ese caso la columna tiene que ser 2 (la segunda), la primer fila de la tabla sería 9. Tenés que ajustar las variables de acuerdo a esos valores. En cuanto a la fila
ResponderBorrar.ListObjects("Tabla1").Resize Target.CurrentRegion
en lugar de "Tabla1" tenés quescribir el nombre de la tabla en tu hoja.
Hola
ResponderBorrarMuy útil la información, realmente te agradezco el aporte, estuve tratando de hacerlo con la opcion de "permitir que los usuarios modifiquen rangos" sin exito
En fin, coloqué el codigo (cambie el nombre de la tabla) y no me funciona, al principio me mandaba error sin importar la celda activa
La adicion de datos comienza en la fila 5 aunque la primera fila es la 4 (sin tomar en cuenta titulos)
No logro que se autoexpanda al insertar un nuevo dato justo debajo del ultimo, soy nueva en esto de las macros, si me puedes ayudar por favor sería maravilloso
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
Hay varios motivos posibles por lo que te sugiero que deshabilites On Error Resume Next para ver que es lo que está impidiendo correr a la macro. Otra sugerencia es que uses el método Application.EnableEvents=True por el caso que los eventos estén deshabilitados.
ResponderBorrarGracias, no sabía si responderias :D
ResponderBorrarEn serio soy muy nueva en esto aun no me acostumbro a la forma de programación en excel.
Deshabilite "On error resumenext" pero no sé exactamente como colocar el "Application.EnableEvents=true", lo hice como el ejemplo de comentarios arriba y me sale el error "no se ha definido Sub"
En fin, sin habilitar los eventos me manda error en el rango especificado
El nombre de la tabla es correcto y la localizacion de fila y columnas tambien lo es, no sé qué es lo que estoy haciendo mal
Gracias
Te sugiero que me envies el cuaderno para que pueda ver donde esta el error (fijate en el enlace ayuda, en la parte superior del blog).
ResponderBorrarEl código sirve si la tabla comienza con sus encabezados en la fila 6?, estoy intentado y me da este error: Se ha producido el error 1004 en tiempo de ejecución:
ResponderBorrarEste rango no funcionará porque no se alinea con la tabla existente. La tabla requiere que este en la misma fila y que incluyan al menos una fila de datos y el nuevo rango debe superponerse a la tabla existen
Hola Guillermo, el código se refiere al objeto Tabla1 (el nombre de la tabla). Parece ser que tu tabla tiene otro nombre. De ser así tendrías que reemplazar Tabla1 .ListObjects("Tabla1") en el código por el nombre de tu tabla.
Borrar