[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 498
  • Last Modified:

Protect drop-down list in Excel 2007 and allow to paste data and keep data validation

I created a drop-down list in Excel 2007 but every time I paste data into the cell the Data Validation is removed. How do I protect the cell so I can paste data into it and keep the data validation at the same time?
0
Carlos_Felipe
Asked:
Carlos_Felipe
2 Solutions
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

data validation cells are meant to evaluate manual data input and then either allow or reject the entry, based on the validation rules.

Pasting a cell over a data validated cell will overwrite the whole cell, including any data validation. You can avoid that by pasting values only: use Paste Special > Values, then the data validation will be retained.

cheers, teylyn
0
 
dlmilleCommented:
Depending on the extent you want to go through managing workbooks and preventing data validation INVALIDATION, here is my most recent related solution that  macros to manage the process.  Just scroll to the last post and download validationTest-r2.xlsm, where there's already an example and try it out.  
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27237351.html

This solution you can build into any of your workbooks, and it will prevent the user from pasting and invalidating a validation range.  It works for the entire workbook, and for new data validation ranges, as they are created.  

Please let me know how well this works for you!  If you have any difficulties, upload a working mock-up of your workbook and I'll assist (re: don't upload any sensitive data).

Cheers,

Dave
0
 
Carlos_FelipeAuthor Commented:
What I really needed was a way to allow me to paste data while keeping the data validation. If the pasted data did not match the data validation, should throw an error. I thought it would be possible.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
dlmilleCommented:
That IS possible with macros.  The post I provided demonstrated that it is possible.  Would you like assistance with this approach?

Dave
0
 
dlmilleCommented:
Here's another solution in my recent past that was the genesis for the work I did.  Perhaps you'll align with the concept more readily as the words on the initial question are of a similar nature to your request:  http:/Q_27145944.html

Download the last post with the file checkValidationRange -R4.xlsm and test copy and pasting into the yellow validation cells...

Let me know if you can take it from there or would like to proceed with assistance.


Dave
0
 
dlmilleCommented:
Is this one dead, teylyn?
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
It was. but just brought it back to life with that post. Otherwise, it would have come up in the Cleanup tool when someone other than me does cleanup.
0
 
dlmilleCommented:
Right - it was 90 days I thought cleanup was 21.  Ok, lesson learned.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Questions get put into the cleanup queue after 21 days. That is correct. It does not mean that they will be cleaned after 21 days, though. A CV needs to go and do the work. Volunteers, remember?
There are not many CVs for the Excel zone and CVs don't clean every day. I only see Qs that I have not participated in.
0
 
dlmilleCommented:
Is it a lot of work?  How much time do you estimate you spend/week on it?
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
I don't get around to doing regular sessions. I just do a batch when I feel it's due. Email AnnieMod if you want to join.
0
 
TracyVBA DeveloperCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now