SOLVED

How can I lock column width but still be able to edit cells?

%3CLINGO-SUB%20id%3D%22lingo-sub-2491358%22%20slang%3D%22en-US%22%3EHow%20can%20I%20lock%20column%20width%20but%20still%20be%20able%20to%20edit%20cells%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2491358%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20need%20help%20with%20editing%20protected%20areas.%20On%20the%20table%20%22Allow%20all%20users%20of%20this%20worksheet%20to%3B%22%2C%20I%20already%20ticked%20all%20the%20boxes%20except%20for%20%22format%20columns%22%20and%20%22format%20rows%22.%20The%20result%20is%20that%20I%20cannot%20change%20the%20column%20size%20(which%20is%20perfect)%2C%20but%20also%20cannot%20edit%20the%20cells'%20formula.%20Is%20there%20a%20way%20to%20lock%20column%20width%20but%20still%20keep%20the%20cells%20editable%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Tu_Nguyen_123_0-1624853172673.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F291861iC0F10715E88EB1DE%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Tu_Nguyen_123_0-1624853172673.png%22%20alt%3D%22Tu_Nguyen_123_0-1624853172673.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2491358%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2491512%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20lock%20column%20width%20but%20still%20be%20able%20to%20edit%20cells%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2491512%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1089537%22%20target%3D%22_blank%22%3E%40Tu_Nguyen_123%3C%2FA%3E%26nbsp%3BBy%20default%2C%20all%20cells%20are%20set-up%20to%20be%20locked.%20But%20that%20doesn't%20come%20into%20effect%20until%20you%20protect%20the%20sheet.%20So%2C%20unprotect%20the%20sheet%20first%20(if%20it%20still%20is%20protected)%2C%20select%20all%20cells%20that%20the%20user%20should%20be%20able%20to%20edit%2C%20go%20to%20%3CEM%3EFormat%20Cells%3C%2FEM%3E%2C%20%3CEM%3EProtection%3C%2FEM%3E%20and%20remove%20the%20check%20mark%20before%20the%20%22Locked%22%20option.%20Now%20protect%20the%20sheet%20again%20with%20the%20options%20you%20showed%20in%20the%20picture.%20Users%20should%20now%20be%20able%20to%20edit%20the%20specified%20cells%2C%20but%20not%20format%20rows%20and%20columns.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2491525%22%20slang%3D%22en-US%22%3ERe%3A%20How%20can%20I%20lock%20column%20width%20but%20still%20be%20able%20to%20edit%20cells%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2491525%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%3A%20it%20works%20now!%20thank%20you%20so%20much%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello,

I need help with editing protected areas. On the table "Allow all users of this worksheet to;", I already ticked all the boxes except for "format columns" and "format rows". The result is that I cannot change the column size (which is perfect), but also cannot edit the cells' formula. Is there a way to lock column width but still keep the cells editable?

 

Tu_Nguyen_123_0-1624853172673.png

Thanks.

 

3 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@Tu_Nguyen_123 By default, all cells are set-up to be locked. But that doesn't come into effect until you protect the sheet. So, unprotect the sheet first (if it still is protected), select all cells that the user should be able to edit, go to Format Cells, Protection and remove the check mark before the "Locked" option. Now protect the sheet again with the options you showed in the picture. Users should now be able to edit the specified cells, but not format rows and columns.

@Riny_van_Eekelen: it works now! thank you so much

@Tu_Nguyen_123 

Here three (3) examples, two (2) of them with VBA and one where you click "Edit Opjects" in your procedure.


You will find all examples in the Excel files.

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.