Home

How to restrict cell changes in excel at the same time enable the change source function?

%3CLINGO-SUB%20id%3D%22lingo-sub-1082910%22%20slang%3D%22en-US%22%3EHow%20to%20restrict%20cell%20changes%20in%20excel%20at%20the%20same%20time%20enable%20the%20change%20source%20function%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1082910%22%20slang%3D%22en-US%22%3E%3CP%3EOutcome%20to%20achieve%3A%20Lock%20the%20entire%20excel%2C%20restrict%20users%20from%20amending%20cells%20BUT%20each%20user%20needs%20to%20change%20source%20documents(under%20edit%20link%20function)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20out%20the%20three%20methods%20as%20follows%3A%3C%2FP%3E%3CP%3E1.%20Protect%20worksheet%3A%20Once%20the%20sheet%20is%20protected(irregardless%20of%20unlocked%20cells%20%26amp%3B%20%22allow%20users%20to%20edit%20ranges%22)%2C%20the%20change%20source%20function%20turns%20grey%20and%20disabled.%3C%2FP%3E%3CP%3E2.%20%26nbsp%3BData%20validation%3A%20Able%20to%20change%20source%20while%20locking%20the%20cells.%20BUT%20the%20downside%20is%20users%20can%20clear%20off%20the%20data%20validation%20rule%20and%20change%20the%20cell%20as%20they%20want.%20(No%20password%20protection)%3C%2FP%3E%3CP%3E3.%20Create%20the%20import%20data%20button%20by%20using%20VBA%20i%20guess%3A%20Not%20preferrable%20as%20my%20template%20is%20complicated%20(with%20formulas%20intercrossed)%20and%20made%20up%20of%20several%20source%20documents.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20solutions%20or%20recommendations%20to%20achieve%20the%20outcome%20as%20mentioned%3F%20Many%20thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1082910%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Kate_Siew
Occasional Visitor

Outcome to achieve: Lock the entire excel, restrict users from amending cells BUT each user needs to change source documents(under edit link function)

 

I have tried out the three methods as follows:

1. Protect worksheet: Once the sheet is protected(irregardless of unlocked cells & "allow users to edit ranges"), the change source function turns grey and disabled.

2.  Data validation: Able to change source while locking the cells. BUT the downside is users can clear off the data validation rule and change the cell as they want. (No password protection)

3. Create the import data button by using VBA i guess: Not preferrable as my template is complicated (with formulas intercrossed) and made up of several source documents.

 

Any solutions or recommendations to achieve the outcome as mentioned? Many thanks!

Related Conversations
Excel on iPad
Mattlewis in Excel on
2 Replies
Help with excel
Alexvez01098 in Excel on
10 Replies
Rotate data from single cell to vertical listings
WEI_Bob in Excel on
2 Replies
2 Seniority Lists
Aminam20 in Excel on
9 Replies
Conditional formatting with wildcard
Rajeev_Raghavan in Excel on
2 Replies