Table formulas protection

[kakiebos]

Hi Everyone.

I need some help again.

I have a sheet for a health check history report on a machine. This is completed on a monthly basis to get some trends on the machine. Some of the inputs are then used to calculate the expected life of the machine to predict when components are to be replaced on a planned repair, instead of on a breakdown.

To do this I want to use a table and some of the columns will have formulas. The reason for the table is its useful functionality with regards to adding new rows and copying the formulas.



My issue now is to protect these formulas and possibly the sheet as well. If I protect the sheet, my formulas is protected, but I loose the table functionality with regards to adding new rows and copying the formulas.

Is there a workaround for this, either in Excel itself or with a VBA script?

Thank you in advance for your assistance.

 

[trunten]

You can do that in Excel with the sheet protection options.
check here: https://www.officetooltips.com/excel_2016/tips/protect_excel_spreadsheet_options.html

 

[kakiebos]

If I protect the sheet, I loose the table functionality. That is to automatically add new rows and copy the formulas in the new row. Even with all the check boxes ticked when I protect the sheet, I still don't have that functionality.

You can do that in Excel with the sheet protection options.
check here: https://www.officetooltips.com/excel_2016/tips/protect_excel_spreadsheet_options.html

 

[trunten]

hmmm.
Is a macro ok?
You could try this as a worksheet macro:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim v, r as Range
    If Target.Count = 1 Then
        Application.EnableEvents = False
        v = Target.Value
        Set r = Selection
        Application.Undo
        If Target.Formula = CStr(Target.Value) Then Target.Value = v
        Application.EnableEvents = True
        r.Select
    End If
End Sub