Proteger Fórmulas de una hoja sin bloquear la tabla

Pues... hay muchísimos foros pidiendo que Excel corrija esto...

https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/16452913-get-tables-working-on-protected-sheets-add-rows

Y tu petición es perfectamente válida...

Se me ocurrió (esos momentos de inspiración que ocurren a veces)... que se podía quitar la protección y aprovechar el evento Change para volver a escribir el valor y hacer que Excel cambie la tabla... luego de lo cual se reactiva la protección...

Entonces... aquí va la sugerencia...

1) Configura la protección de celdas en la hoja, desbloquea las columnas en la hoja que dejes escribir y bloquea aquellas en donde tienes las fórmulas

2) Copia el código de abajo en el MÓDULO DE HOJA.

Nota importante1: Solo copia el código después de que hayas configurado todas las fórmulas de la tabla...

3) Debes hacer que se active el evento y proteja la hoja por primera vez, para esto ve a una celda con algún valor editable y vuelve a escribirlo en la misma celda.

Codigo:

Private Sub Worksheet_Change(ByVal Target As Range)

' Exit Sub
Dim dato
Application.DisplayAlerts = False
dato = Target.Value
ActiveSheet.Unprotect Password:="abc"
Application.EnableEvents = False
Target.Value = dato
Application.EnableEvents = True
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=False, AllowInsertingColumns:=False, AllowInsertingRows _
:=False, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=False, _
AllowDeletingRows:=False, AllowSorting:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True, Password:="abc"
ActiveSheet.EnableSelection = xlNoRestrictions
End Sub

Nota importante2: Si en un futuro necesitas cambiar una fórmula.. Borra el apostrofo del Exit Sub (el la segunda linea), asi bloqueas temporalmente el evento. Quita la protección de la hoja, modifica la fórmula, vuelve a poner el apostrofo y re-escribe una celda como lo hiciste la primera vez .

Creo que es lo mejor que se puede hacer a pesar de las limitaciones de Excel.

Saludos,

Jaime Segura

PD: No olvides valorar la respuesta.

Hola, muchas gracias por tu ayuda, las celdas con fórmulas ahora están bloqueadas y a medida que inserto datos en la celda siguiente del fin de la tabla se extiende la tabla (valga la redundancia)

Ahora tengo otro problema, las fórmulas se extienden con la tabla pero no en todas las celdas... EJEMPLO: en cada fila de las columnas X, Y, Z de la tabla tengo fórmulas que devuelven un porcentaje en base al calculo que se hace en cada fila de las columnas J, K, L, M. el problema está en que cuando ingreso datos debajo de la tabla, aveces se copian las fórmulas pero, aveces no.

¿Alguna idea de como mantener la misma fórmula en toda la columna de la tabla, independientemente del numero de filas que tenga y que estas fórmulas queden bloqueadas?

De verdad muchas gracias!