How can i protect a calculated column in a table from being edited but allowing users to add rows?

Copper Contributor

Hi, I have a simple example of a data entry form (on an excel table) with 4 columns for entry of quarterly financial data. i have a 5th column that adds up the 4 quarters into an annual cost per line. this is a calculated column in the table so adding rows automatically adds the calculation to sum up the annual costs.

 

I need to be able to lock the annual column to stop editing while allowing new rows to be added. This is because i need to distribute the entry across a number of users and need to ensure they dont edit calculated columns.

 

When i protect the column and lock the sheet, i get an error adding rows saying i need to unlock.

 

Any advice on what im doing wrong please?

18 Replies

@TonyCrabb 

Please keep in mind that no one can see what you see

In this link you will find some more information about it:

Welcome to your Excel discussion space!

 

Thank you for your understanding and patience

 

NikolinoDE

I know I don't know anything (Socrates)

Some additional context....
Windows 10 PC
MIcrosoft 365 Apps for Enterprise
Excel Version: Excel for Microsoft 365 MSO (16.0.13127.21886) 32-bit

In the attached file, i have a simple table with one calculated column.

I have unprotected the date entry columns (title, Q1-Q4 and left the annual caculated column locked.

I have then turned on sheet protection.

 

What I want to happen:

- a user can add lines, delete lines, re-sort lines with the calculated column being locked and automatically added when new lines are added.

 

What happens:

- i get an error saying the sheet needs to be unlocked.

 

It is currently protected so if you try to add a row you will see the error. If you unprotect the sheet (no password) you can add and delete rows fine but can edit the annual total which is what i need to avoid.

 

Any help appreciated.

@TonyCrabb 

Here is a solution with data validation.

This presented procedure protects the cells from accidentally overwriting the cells.

However, the locked cell can still be deleted with the Delete key.

Protected cells can also be overwritten using the copy and paste function.

The function presented does not represent particularly good protection,

but it is sufficient in practice in many cases.

Otherwise it would have to be done with VBA, but it takes time,

which I unfortunately don't have available.

 

Hope I was able to help you

 

NikolinoDE

I know I don't know anything (Socrates)

I am looking for something more robust than that.

Is there really no inbuilt way in Excel to be able to instert a row into a table while protecting a calculated column? That seems such a basic need?
I don't know of any other approach without VBA, but this doesn't have to be the case, it may be, I don't know, that there could be other ways, but these are beyond my knowledge.

@TonyCrabb 

For example:

Sub AddRow()
    Dim strPassword As String
    strPassword = "secret"
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect Password:=strPassword
    ActiveSheet.ListObjects(1).ListRows.Add AlwaysInsert:=False
    ActiveSheet.Protect Password:=strPassword
    Application.ScreenUpdating = True
End Sub
Thanks @Hans Vogelaar i have set this up and added a button to activate and it does now add a row and then relock which is great! But it raises a few new questions:
1. the row always adds at the bottom of the table. can it add based on the row the cursor is on?
2. in my example, i had enabled things like sort and filter etc on the protect options but the macro resets all of those so it doesnt allow me to filter after inserting
3. is there a way to link the macro to the standard row insert instead of me adding a new button?

@TonyCrabb 

This version will try to insert a new row in the row of the active cell, so you need only one button to call the macro.

Check out the wsh.Protect part and change the True/False arguments as needed.

 

Sub AddRow()
    Dim wsh As Worksheet
    Dim tbl As ListObject
    Dim pwd As String
    Dim pos As Long
    Application.ScreenUpdating = False
    pwd = "secret"
    Set wsh = ActiveSheet
    wsh.Unprotect Password:=pwd
    Set tbl = ActiveSheet.ListObjects(1)
    pos = ActiveCell.Row - tbl.Range.Row
    tbl.ListRows.Add Position:=pos, AlwaysInsert:=False
    wsh.Protect Password:=pwd, DrawingObjects:=False, _
                Contents:=True, Scenarios:=False, _
                AllowFormattingCells:=True, AllowFormattingColumns:=True, _
                AllowFormattingRows:=True, AllowInsertingColumns:=True, _
                AllowInsertingRows:=True, AllowInsertingHyperlinks:=True, _
                AllowDeletingColumns:=True, AllowDeletingRows:=True, _
                AllowSorting:=True, AllowFiltering:=True, _
                AllowUsingPivotTables:=True
    Application.ScreenUpdating = True
End Sub
Thanks @Hans Vogelaar. This is great and so powerful but we are still having to re-create a massive amount of features just to protect a calculated column (eg i need to solve this for deleting, sorting etc as well).

Coming at this a slightly different way, is it possible to fire a macro when i enter a column so that it automatically jumps over that column? so in my calculated column example, could i leave the sheet unprotected but if i try to enter the calculated columm via a click or a tab or an arrow ( eg some sort of on Select) could it automatically move me to the next or previous column?

@TonyCrabb 

That is too difficult (we don't know how the user arrived there), but you can do something like this if you really want to irritate your users:

  • Right-click the sheet tab.
  • Select View Code from the context menu.
  • Copy the code listed below into the worksheet module.
  • Change Range("F:F") to the column you want to make inaccessible.
  • If you ever want to edit the column, type the following in the Immediate window of the Visual Basic Editor and press Enter:

    Application.EnableEvents = False


  • When you're done, do the same but with

    Application.EnableEvents = True


  • Don't forget this, otherwise the worksheet won't react to events anymore.

 

 

 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Range("F:F"), Target) Is Nothing Then
        Application.EnableEvents = False
        Beep
        Range("A1").Select
        Application.EnableEvents = True
    End If
End Sub

 

 

@TonyCrabb 

 

This may be a silly solution, but I wondered if--unless you're worried that people could intentionally or maliciously override your calculations--you couldn't just move the calculating column off to the right and hide it to prevent inadvertent entries. (Or, to be more extreme, put the calculations on a separate sheet, and hide the sheet).

Thanks @Hans Vogelaar. instead of the beep and going to A1, can you find current row and just move the cursor automatically to Col E on current row?

@TonyCrabb 

You can remove the Beep line and change Range("A1").Select to Range("E" & Target.Row).Select

Thanks @Hans Vogelaar - you have been very helpful. I need to sleep on this one and have a think about where to go next.
Thanks @mathetes but i need to keep the calculated column visible for the users.