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
Highlighted
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!

0 Replies