Protected Excel: Insert Rows
dom14500
-
Le Pingou Posted messages 12273 Registration date Status Contributor Last intervention -
Le Pingou Posted messages 12273 Registration date Status Contributor Last intervention -
Hello,
We are facing the following problem:
on a password-protected Excel sheet, we are unable to insert rows and perform a selective sort.
However, when enabling protection, the options
- insert rows
- use sorting are checked.
Could you please let us know where the mistake is?
Thank you in advance
Configuration: Windows 7 / Internet Explorer 9.0
We are facing the following problem:
on a password-protected Excel sheet, we are unable to insert rows and perform a selective sort.
However, when enabling protection, the options
- insert rows
- use sorting are checked.
Could you please let us know where the mistake is?
Thank you in advance
Configuration: Windows 7 / Internet Explorer 9.0
6 answers
-
Hello
in principle, to be able to insert rows, it is also necessary that during protection, the options to select locked and unlocked cells are checked, otherwise you cannot select the rows.
Good luck
best regards
--
are we not the only ones to understand what we are explaining?-
Hello,
Indeed! If we unlock the cells, it works.
We are now faced with a problem because we have cells containing formulas that need to be locked.
Is there a way, without unlocking the cells, to get a sort from A to Z, so we can add new patients at the end of the list and then sort them by file number? This would ultimately be the same as an insertion.
Thank you for your response. -
Hello,
Just passing by, you said this: Indeed! If we unlock the cells, it works.
However, Vaucluse (best regards) clearly mentioned:
the options to select locked and unlocked cells should be checked
which is different from removing the protection.
--
Best regards.
The Penguin -
Hello my friend Le Pingou
It must be acknowledged that hidden comments in the CMM display are not always a good idea.
I'm referring you to this one because the response you read from the requester was not addressed to my proposal
https://forums.commentcamarche.net/forum/affich-25434041-excel-protege-inserer-des-lignes#7
That said, if you read everything maybe you will have something to suggest for the remaining issue mentioned in this series of messages, namely copying formulas to the inserted row, or inserting rows with formulas into a protected sheet.
As for me, apart from a macro, I can't see any other solution.
Best regards. -
Hello everyone,
Strange, it doesn't refuse the insertion of a row.
All cells are locked except in column B and there are formulas in C (protected)
It refuses deletion and sorting yes (although checked during protection)
Did I forget a condition?
That it refuses to enter data in a protected cell is normal, and I can enter in B.
https://www.cjoint.com/?BFxgSoPlTkv
eric
edit: modified the file, cells in B unlocked
edit2: ok, I just saw further down that I need to remove 'select locked cells' -
Hi Eric
Well then, :-) ?
No one mentioned (at the end) that we couldn't insert a row in the protected sheet, as long as in the protection options, the selection of locked and unlocked cells is checked.
The remaining question is that we can't, in the inserted row, copy formulas to the new row or copy a row with a formula and insert it.
See messages 9 and 10
As for sorting, as mentioned earlier, it only works with the option if all the cells in the sorted field are unlocked.
See message 11
And have a great weekend my friend
Best regards
-
-
Hello
No issue with inserting in unprotected mode.
As soon as the document is protected again, insertion, deletion, and selective sorting become impossible.
We have also allowed
- selecting unlocked cells
- using the auto filter
these two options work in protected mode
Excel 2010
Thank you -
Hello,
There should be no false manipulation, try again after closing Excel...
Otherwise, you can upload your reduced (and anonymized) file to cjoint.com and paste the provided link here.
Eric -
Re
Note just in case and unless I'm mistaken:
in order to insert into the sheet with locked cells while checking "select unlocked cells", the cells must be unlocked along the entire row.
otherwise it works in all cases by also checking
"select locked cells"
If this option is not checked, everything behaves as if Excel does not have the right to select a row containing locked cells.
The option:
"select locked cells"
does not create significant risks since the cells remain unmodifiable
furthermore, regarding sorting: you say you cannot sort and you specify that you have allowed the filter??? have you allowed sorting?
good luck again
--
are we not the only ones to understand what we are explaining?-
First of all, thank you for all this information; in the absence of results, we are making progress.
- Indeed, the cells remain unmodifiable, and in this case, we can insert rows. One issue is that the formulas are not replicated in the inserted row; no copy-paste is possible and no stretching with +
- We have checked the filter and sort function
Sorry for being so useless!
Thanks again. -
Indeed, you will not have a solution to copy and paste on an inserted line since it also contains, in copy of the format above, protected cells.
I don't think it's worth looking any further
Unless you go through a macro including password and protection.
(which you'll have to ask better specialists than me!)
Regards -
Small supplement, I haven't tested but you never know:
rather than inserting a row and copying and pasting the formulas, try:
_copy the row with the formula
_select the insertion point and
_"insert the copied row"
but I really think it's the last resort
best regards -
-
I stay hooked to the thread, as the solution interests me... if there is one!
Apparently it doesn’t work, even with all the options from the protection list allowed
.
But I believe the conclusion is:
it only works if there are no protected cells in the range to sort
Good luck / Safe travels
cheers
-
-
Hello everyone,
so a proposal by macro:Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim col As Long If Target.Column <> 1 Then Exit Sub Cancel = True ActiveSheet.Unprotect ("toto") Rows(Target.Row).Copy Rows(Target.Row).Insert Shift:=xlDown Rows(Target.Row).SpecialCells(xlCellTypeConstants, 7).ClearContents Application.CutCopyMode = False ActiveSheet.Protect ("toto") End Sub
https://www.cjoint.com/?BFxhkbm1Ye3
eric-
-
Re,
Just right-click on the name of the relevant tab and select 'view code.'
In the window that opens, copy and paste the code from sub to end sub inclusive.
The first time, you may need to adjust Excel to allow macros. The process depends on the version.
That's it...
Then a simple double-click will insert the line with the formulas.
Have you at least tried it on the example file I provided?
eric
-
-
Hello
In unprotected mode, can you insert rows?
If the answer is no, go to the last line of the Excel sheet and delete that line; there must be a character preventing you from inserting rows.
I just did the same manipulation you did with the same parameters, and I can insert rows in Excel 2010.
