SOLVED

VBA to hide set of columns is in conflict with protecting the sheet

%3CLINGO-SUB%20id%3D%22lingo-sub-1311063%22%20slang%3D%22en-US%22%3EVBA%20to%20hide%20set%20of%20columns%20is%20in%20conflict%20with%20protecting%20the%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1311063%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3Ei%20created%20VBA%20to%20hide%20a%20set%20of%20columns.%20But%20when%20i%20try%20to%20protect%26nbsp%3B%20certain%20cells%20in%26nbsp%3B%20sheet%20including%20the%20columns%20that%20will%20be%20hiden%2C%20i%20get%20the%20error%20message%22%20Run%20time%20error1004%2C%26nbsp%3B%20Unable%20to%20set%20the%20hidden%20property%20of%20the%20Range%20class%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20not%20sure%20how%20to%20fix%20it%20or%20if%20there%20is%20a%20better%20way%20to%20do%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3ESam%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1311063%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1312081%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20to%20hide%20set%20of%20columns%20is%20in%20conflict%20with%20protecting%20the%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1312081%22%20slang%3D%22en-US%22%3EWhen%20you%20protect%20a%20worksheet%2C%20one%20of%20the%20options%20is%20to%20allow%20%22Format%20Columns%22.%20If%20you%20check%20that%20box%20you%20can%20hide%20and%20unhide%20columns%20on%20a%20protected%20sheet.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1312693%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20to%20hide%20set%20of%20columns%20is%20in%20conflict%20with%20protecting%20the%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1312693%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%20Thank%20you%20Jan!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1762252%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20to%20hide%20set%20of%20columns%20is%20in%20conflict%20with%20protecting%20the%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1762252%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%20-%20When%20i%20select%20that%20setting%20feature%20it%20works%20while%20i%20am%20in%20the%20document.%20Even%20after%20saving%2C%20i%20open%20the%20document%20again%20the%20setting%20default%20back.%20I%20can%20not%20unhide%20and%20hide%20columns%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hello,

i created VBA to hide a set of columns. But when i try to protect  certain cells in  sheet including the columns that will be hiden, i get the error message" Run time error1004,  Unable to set the hidden property of the Range class".

 

I am not sure how to fix it or if there is a better way to do it.

 

Regards,

Sam

3 Replies
Highlighted
Best Response confirmed by SamFares40 (Occasional Contributor)
Solution
When you protect a worksheet, one of the options is to allow "Format Columns". If you check that box you can hide and unhide columns on a protected sheet.
Highlighted

@Jan Karel Pieterse  - When i select that setting feature it works while i am in the document. Even after saving, i open the document again the setting default back. I can not unhide and hide columns