EXCEL: Data Validation [VBA]

[gaikwad.mm@gmail.com]

Hi

In excel file I’ have created several data validation list. Columns are having both merged and unmerged cells. I’ want to protect the data validation list from getting deleted. I’ have tried almost all the possible ways I’ know, but still I’ am getting bugs in the file.

Request you to please have a look to the below image for file understanding, and then go through my requirements below. Also I’ have mentioned the scenarios.

240899.jpg

Image is sample of my file. As mentioned in the above image, there are multiple levels of rows which contain data validation list i.e. 6 Merged Cells, 2 Merged Cells, Single Cells. Also there are few columns in between which are not having any validation, but having same kind of formats.

NOW WHAT IS MY REQUIREMENT?

There are few columns which should have feasibility to select multiple values from the data validation list.
Data Validation should not get deleted with following scenarios
Copy Paste same formatted cell which are without data validation. From same file or other file.
If I’ insert any row in middle, that range should also contain data validation list.
If the user copy entire row from different file which contains data without validation, then it should not paste. But if the data is matching column to column then we can allow to paste it
Cell which is not having validation should not get overwritten on the data validation list cell.
IN SHORT: The code should protect the data validation list in any kind of scenario, we can not restrict process to the end user.
ONCE I WILL GET THIS CODE I WILL CHECK THE SAME WITH MY EXCEL FILE AND LET YOU KNOW FOR ANY BUGS.

PLEASE HELP ME OUT OF THIS

Thanks in Advance

MILIND

[lilsnoop]

Not sure if this is what you are trying to do...but see attached sample. This might have some vba issues that can be fixed for you by someone far better than I, but I believe it will get you started for what I believe you are seeking to do. Best of luck!

[gaikwad.mm@gmail.com]

Dear Lilsnoop

I' am so glad that I' have received reply on the raised concern. But still the requirement is pending and not yet full filled with your solution which I' already tried. Please find the attached file and have a look to the requirement below, I' hope that I'll get a solution for this.

In the file you will get 3 highlighted columns, first 2 with validation and the third without validation but with same format.

My Need.

1) Column with Alphabets should allow multiple selection but not the other number column.
2) No one should destroy the data validation cell with any kind of copy paste.
3) If I' copy the cell which contains same content as available in the Data validation list, then the paste should work but without destroying validation. and if the the contents mismatches it should not allow.
4) Don't want to disable cut copy paste drag etc functions the protection should work without disabling any functionality.
5) I' should be able to copy paste the entire row for same sheet or any other file or sheet, if the contents are as per the data validation.

In Short: Data Validation should not be destroyed without hampering the Excel's normal behave.

Thanks & Regards
Milind

[gaikwad.mm@gmail.com]

Dear Experts on the Forum, Could you please help me on the logged query?