SOLVED

Adding rows in worksheet with locked columns

%3CLINGO-SUB%20id%3D%22lingo-sub-1388823%22%20slang%3D%22en-US%22%3EAdding%20rows%20in%20worksheet%20with%20locked%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1388823%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20worksheet%20in%20which%20two%20columns%20are%20locked%20for%20editing%20with%20%22protect%20worksheet%22.%20The%20problem%20is%20that%20I%20can't%20add%20any%20rows%20to%20the%20sheet%20now%2C%20even%20though%20the%20option%20to%20add%20rows%20is%20selected.%20When%20I%20select%20the%20option%20to%20select%20locked%20cells%2C%20then%20I%20can%20add%20rows%20again.%20But%20as%20you%20understand%2C%20this%20removes%20the%20purpose%20of%20the%20locked%20columns.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20resolve%20this%20problem%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20help%20is%20much%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20regards%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1388823%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1396758%22%20slang%3D%22en-US%22%3ERe%3A%20Adding%20rows%20in%20worksheet%20with%20locked%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1396758%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F398332%22%20target%3D%22_blank%22%3E%40CSMDKU%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20share%20your%20workbook%20so%20I%20can%20look%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20example%20the%20attached%20(blank)%20workbook%20has%202%20columns%20protected%20(no%20PW)%20highlighted%20in%20yellow.%20You%20can%20not%20enter%20data%20into%20the%20columns%20(as%20expected)%20nor%20can%20you%20insert%20rows%20(also%20as%20expected).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20attach%20your%20file%20we%20can%20look%20to%20see%20what%20is%20happening.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPeter%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1415544%22%20slang%3D%22en-US%22%3ERe%3A%20Adding%20rows%20in%20worksheet%20with%20locked%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1415544%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F667968%22%20target%3D%22_blank%22%3E%40peteryac60%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttached%20is%20a%20empty%20copy%20of%20the%20workbook%20I%20want%20to%20use.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20put%20comments%20in%20the%20columns%2Frows%20that%20must%20be%20locked%20for%20editing%2C%20these%20contain%20formulas.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1415638%22%20slang%3D%22en-US%22%3ERe%3A%20Adding%20rows%20in%20worksheet%20with%20locked%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1415638%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F398332%22%20target%3D%22_blank%22%3E%40CSMDKU%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20attached%20the%20document%20with%20columns%20U%20%26amp%3B%20V%20protected.%20Is%20this%20what%20you%20wanted%3F%3C%2FP%3E%3CP%3EPeter%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1415649%22%20slang%3D%22en-US%22%3ERe%3A%20Adding%20rows%20in%20worksheet%20with%20locked%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1415649%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F398332%22%20target%3D%22_blank%22%3E%40CSMDKU%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Esorry%20-%20I%20misunderstood%20your%20problem.%20I%20think%20this%20might%20do%20what%20you%20want.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1415745%22%20slang%3D%22en-US%22%3ERe%3A%20Adding%20rows%20in%20worksheet%20with%20locked%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1415745%22%20slang%3D%22en-US%22%3EHi%2C%3CBR%20%2F%3E%3CBR%20%2F%3EThis%20is%20exactly%20what%20I%20want%2C%20thanks!%3CBR%20%2F%3E%3CBR%20%2F%3ECan%20you%20explain%20to%20me%20what%20you%20have%20done%20to%20accomplish%20this%20so%20I%20can%20do%20it%20myself%3F%3CBR%20%2F%3E%3CBR%20%2F%3EMany%20thanks!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1415797%22%20slang%3D%22en-US%22%3ERe%3A%20Adding%20rows%20in%20worksheet%20with%20locked%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1415797%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F398332%22%20target%3D%22_blank%22%3E%40CSMDKU%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewhen%20you%20protected%20the%20sheet%20you%20had%20not%20checked%20the%20very%20first%20option%20-%20Select%20Locked%20Cells.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20was%20all!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20grateful%20if%20you%20would%20mark%20this%20solution%20as%20complete!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Emany%20thanks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPeter%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1415917%22%20slang%3D%22en-US%22%3ERe%3A%20Adding%20rows%20in%20worksheet%20with%20locked%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1415917%22%20slang%3D%22en-US%22%3EOh%20wow!%20Never%20guessed%20that%20would%20be%20the%20solution.%3CBR%20%2F%3E%3CBR%20%2F%3EAgain%2C%20many%20thanks%20for%20your%20help!%3C%2FLINGO-BODY%3E
New Contributor

Hi all,

 

I have a worksheet in which two columns are locked for editing with "protect worksheet". The problem is that I can't add any rows to the sheet now, even though the option to add rows is selected. When I select the option to select locked cells, then I can add rows again. But as you understand, this removes the purpose of the locked columns.

 

How can I resolve this problem?

 

Your help is much appreciated!

 

Kind regards

8 Replies

@CSMDKU 

 

Can you share your workbook so I can look ?

 

As example the attached (blank) workbook has 2 columns protected (no PW) highlighted in yellow. You can not enter data into the columns (as expected) nor can you insert rows (also as expected).

 

If you attach your file we can look to see what is happening.

 

 

 

Peter

@peteryac60 

 

Attached is a empty copy of the workbook I want to use.

 

I have put comments in the columns/rows that must be locked for editing, these contain formulas. 

@CSMDKU 

 

Hi 

 

I attached the document with columns U & V protected. Is this what you wanted?

Peter

@CSMDKU 

 

sorry - I misunderstood your problem. I think this might do what you want.

 

 

Hi,

This is exactly what I want, thanks!

Can you explain to me what you have done to accomplish this so I can do it myself?

Many thanks!
best response confirmed by CSMDKU (New Contributor)
Solution

@CSMDKU 

 

Hi 

 

when you protected the sheet you had not checked the very first option - Select Locked Cells.

 

That was all!

 

I would be grateful if you would mark this solution as complete!

 

many thanks,

 

Peter

 

 

 

Oh wow! Never guessed that would be the solution.

Again, many thanks for your help!

Hi Peter

 

I tried replicating this but in my own sheet this doesn't work. I still can't add rows if the cells are locked. Does this have anything to do with the fact that it's my first columns that are locked or not?

@peteryac60 

 

PS I uploaded my file