I have a user who has created a spreadsheet used for data entry. He has placed some "VLookup" formulas in several of the input cells to create drop down picklists. However, when a user enters and saves a new value that is not in the picklist, the "VLookup" formula is lost and overwritten by the new cell value. Is there any way to protect the formula value from being overwritten? I have written some code in VBA to preserve the formulas when the user clicks a "New" command button and clears the form. However, I don't know how to protect the formulas in this situation. Thanks in advance.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Preserving cell formulas from user input
- Thread starter BluByU
- Start date
WalkerEvans
Technical User
Have your user place the input into a different cell, then use that cell as a reference in the Lookup. As a general rule, any spreadsheet that is going to be used by anyone other than the creator should have all of the formula cells locked to prevent them being overwritten. On the toolbar, select Format, then Cells, then Protection. Check the "Locked" box and then protect the sheet.
This should solve the problem
This should solve the problem
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Solved