SOLVED

Excel Macro

%3CLINGO-SUB%20id%3D%22lingo-sub-2343840%22%20slang%3D%22en-US%22%3EExcel%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2343840%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20created%202%20Name%20Managers%20i.e%20opex%20(blue%20colour)%20and%20mtd%20(green).%20Using%20a%20macro%2C%20I%20would%20like%20to%20unlock%20these%20areas%20(Name%20manager)%20and%20lock%20the%20rest%20(other%20cells)%20so%20that%20users%20can%20key%20in%20their%20data%20in%20the%20unlocked%20cells.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPreviously%2C%20I%20recorded%20a%20macro%20(object%20based%20using%20mouse)%20for%20implementation%20for%2050%20Excel%20files%20and%20had%20some%20inconsistent%20results.%20Sometime%2C%20in%20using%20the%20macro%2C%20I%20find%20these%20cells%20are%20either%20fully%20locked%2C%20partially%20locked%20or%20unlocked.%20Is%20there%20another%20way%20to%20do%20this%2C%20consistently%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2343840%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%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2344068%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2344068%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F928871%22%20target%3D%22_blank%22%3E%40Chan_Tze_Leong%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20example%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20UnlockNamedRanges()%0A%20%20%20%20Cells.Locked%20%3D%20True%0A%20%20%20%20Range(%22opex%22).Locked%20%3D%20False%0A%20%20%20%20Range(%22mtd%22).Locked%20%3D%20False%0A%20%20%20%20ActiveSheet.Protect%20'%20Password%3A%3D%22secret%22%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2344596%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Macro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2344596%22%20slang%3D%22en-US%22%3EThanks%2C%20Hans.%20Appreciate%20the%20help.%3C%2FLINGO-BODY%3E
Contributor

I have created 2 Name Managers i.e opex (blue colour) and mtd (green). Using a macro, I would like to unlock these areas (Name manager) and lock the rest (other cells) so that users can key in their data in the unlocked cells.

 

Previously, I recorded a macro (object based using mouse) for implementation for 50 Excel files and had some inconsistent results. Sometime, in using the macro, I find these cells are either fully locked, partially locked or unlocked. Is there another way to do this, consistently?

2 Replies
best response confirmed by Chan_Tze_Leong (Contributor)
Solution

@Chan_Tze_Leong 

For example:

Sub UnlockNamedRanges()
    Cells.Locked = True
    Range("opex").Locked = False
    Range("mtd").Locked = False
    ActiveSheet.Protect ' Password:="secret"
End Sub
Thanks, Hans. Appreciate the help.