Forum Discussion

kvetrivel's avatar
kvetrivel
Copper Contributor
Feb 08, 2023

How to edit table (Table format) when its locked?

I am trying to develop the table mainly because it will automatically add the formula when I have entered the value in one cell.

But the problem is I am not able to edit when the sheet is protected.

Need solution for following things,

1. I want to lock the column formula because the user shouldn't change it.

2. If I lock the column I am not able to add rows and columns which affects the actual purpose of the Table format.

Kindly give a solution for the above issue It would be very helpful.

 

excel
Formulas and Functions

1 Reply

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Feb 08, 2023

    kvetrivel 

    You could do it with VBA, here's an example

     

    Sub Workbook_Open()
With Sheets("YourSheet")
.Protect userinterfaceonly:=True, Password:="YourPassword", AllowInsertingColumns:=True, AllowInsertingRows:=True
.EnableOutlining = True 'for outline
.EnableAutoFilter = True 'for Autofilter
End With
End Sub

     

    or you can do it the traditional (manual 🙂 way, see attached link.

    Lock or unlock specific areas of a protected worksheet

     

    Hope I could help you with these information / links.

     

    NikolinoDE

    I know I don't know anything (Socrates)

Resources