Protected Excel: Insert Rows
dom14500
-
Le Pingou Posted messages 12375 Registration date Status Contributeur Last intervention -
Le Pingou Posted messages 12375 Registration date Status Contributeur 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 réponses
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
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
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?
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, 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
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
Hello everyone,
so a proposal by macro:
https://www.cjoint.com/?BFxhkbm1Ye3
eric
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
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
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.
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
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.
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'
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