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
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
I know I don't know anything (Socrates)
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.
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.
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
May 04 2022 05:43 AM
You do need VBA for this, sorry!
May 04 2022 08:07 AM
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
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