Data Validation deleted on Copy & Paste

[LeafyShroom]

Hi there,

I have set up data validation that works perfectly if you type in the data. The problem is the Sheet must be able to validate successfully when Copy & Paste is used as well.

I have tried some VBA code samples on various sites, but they completely prevent data from being inserted if it does not have any validation.

I need a way to force copied data with no validation to be pasted without deleting the existing validation rules in the cell. I cannot avoid using copy paste, so that is not an option...

Maybe one can use code that converts any normal 'Copy & Paste' into 'Paste Special' or something like that...?

I will greatly appreciate any help!

Thanks

[AlphaFrog]

One approach is to use the Worksheet_Change event. It triggers when there is a change (e.g.; Paste) to the worksheet. You could have vba code to re-apply the DV rule to the cells you want to maintain.

Describe what cells have data validation and what is their DV formula, or better yet, attach a desensitized example workbook.

[Winon]

Hello LeafyShroom,

Welcome to the Forum!

Have you considered using a Named Range for your Data Validation?

If you don't know how to do that, then please attach a sample workbook.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Regards.

[LeafyShroom]

Hi There,

I have 2 culumns H & I. H containing a start-time value and I containing an end-time value. I have the following validation rules applied:

Rule for H column:
=OR(TEXT($H129,"hh-mm-ss")="NA",TEXT($H129,"hh-mm-ss")="Aborted",TEXT($H129,"hh-mm-ss")="Skipped",NOT(ISERROR(TIMEVALUE(TEXT($H129,"hh:mm:ss")))))

Rule for I column:
=OR(AND(OR($I129="NA",$I129="Aborted",$I129="Skipped"),$H129=$I129),AND(NOT(ISERROR(TIMEVALUE(TEXT($I129,"hh:mm:ss")))),NOT(ISERROR(TIMEVALUE(TEXT($H129,"hh:mm:ss")))),$H129<>$I129))

The range of cells that have validation is H3 - H188 and I3 - I188

I won't be able to write the VBA code though

[LeafyShroom]

Hi There,

I have attached a simple example sheet with the same data and rules to illustrate what I mean.

[AlphaFrog]

This this does the validation within the code. You could remove the Data Validation from the sheet.

To Install the code:

• Right-click on the sheet tab
• Select View Code from the pop-up context menu
• Paste the code from below in the worksheet's code module

Please Login or Register  to view this content.

[LeafyShroom]

Thank you, AlphaFrog, for your reply. I really appreciate it

I have inserted the code you provided. As soon as I try and paste anything within on of the cells, I get an error saying: "Compile Error: Sub or Function not defined" and it then highlights the word/function: 'isTime'

Do you perhaps know how I could fix that?

Thanks!

error.PNG

[AlphaFrog]

Sorry. My Bad. I forgot to give you the IsTime custom function.

Please Login or Register  to view this content.

[LeafyShroom]

I am trying to reply, but the site is blocking my reply.

[LeafyShroom]

Hi AlphaFrog,

Thank you for your reply.

I am not getting the error anymore. I still have a problem, though.

No matter what I type into column I it always results in an error.

[LeafyShroom]

I need the following to be able to happen:

1. If text is entered, it may only be "NA" or "Skipped" or "Aborted" in either column H or I or both.
2. If time is entered, it must be in format hhmmss and the end-time cannot be earler or equal to the start-time.
Anything else must result in an error which should read:
Please Insert Only: "NA" / "Skipped" / "Aborted". Make sure that the end-time is not earlier than or equal to the end-time.

If the OK button of the message box is clicked then the edited cell must return to its original default value.

[LeafyShroom]

Hi There,

I have tried to play around with some of the coding to try and figure something out. At this point I'm not getting any errors, but the code is not doing anything.

I don't think I understand what I'm doing exactly (first time I ever try VBA), but I tried to use different pieces of coding from different forums to try and puzzle something together.

Could someone please have a look and maybe modify and correct errors so it'll work if possible?

This really is a tough one to solve...

I'll greatly appreciate the help!

[AlphaFrog]

This seems to work for me.

[LeafyShroom]

Hi AlphaFrog,

Thanks for the prompt reply.

I had a look and yes, it works, but only if I type in the values. When I Copy and Paste a time value, it still doesn't seem to work.

Are you able to Copy and Paste and it works?

Thanks

[AlphaFrog]

You're right. It doesn't trigger with paste. I'll work on it.

[LeafyShroom]

Thanks a lot. I really appreciate the help.

[AlphaFrog]

Try this...