Apr 29 2022 07:54 AM
Apr 29 2022 07:54 AM
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?
Apr 30 2022 06:42 AM
May 02 2022 07:51 AM - edited May 02 2022 07:57 AM
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.
- 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.
May 03 2022 09:43 AM
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
I know I don't know anything (Socrates)
May 03 2022 09:51 AM
May 03 2022 10:07 AM
May 04 2022 05:26 AM
Does anybody else have any thoughts?
May 04 2022 05:43 AM
You do need VBA for this, sorry!
May 04 2022 07:46 AM
May 04 2022 08:07 AM
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
May 04 2022 11:48 AM
May 04 2022 12:03 PM
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
May 04 2022 12:21 PM
May 04 2022 12:32 PM - edited May 04 2022 12:34 PM
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:
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
May 04 2022 12:47 PM
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).
May 04 2022 12:49 PM
May 04 2022 01:06 PM
You can remove the Beep line and change Range("A1").Select to Range("E" & Target.Row).Select
May 04 2022 01:13 PM
May 04 2022 01:14 PM