Applying a validation list on data that's been pasted in a column

[zookeepertx]

Hello there!

I don't know how to even start thinking about this, so I'm coming directly to the experts.

I have a coworker wanting a macro done for them, but first we have to figure out how to get the data organized correctly. He doesn't do any of the "hands on" work with the workbook; he uses the results, so anybody that needs to actually work on it has a chance to screw things up, LOL!

Two of the sheets in the book are called "WORKING LIST" and "COMPLETE". Data gets downloaded on WORKING LIST and then pasted over to COMPLETE. When the data comes in, column F says "Import Order" on every row. Then, after its been pasted onto COMPLETE someone updates the data on COMPLETE. So, "F" on a number of rows needs to be changed to match the current circumstances. Of course, with more than one person working in the book, what gets typed into that cell can vary. We'd like to have a validation list in column F that will just give them certain options to choose from.

The problem, of course, is that, if COMPLETE column F has validation, when the data is pasted from WORKING LIST to COMPLETE, the validation goes away.
That doesn't happen if we paste/special/values, but I believe there are other things on there that would "break" if we did that. (Besides, you can imagine how hard it would be to ensure that random people will ALWAYS paste correctly! )

Is there some way to "lock" the validation in COMPLETE column F so that pasting data in there wouldn't kill it? Or some other way to solve this? Maybe a worksheet change event?

Thanks!

Jenny

 

[Tom Urtis]

You have an entertaining way of presenting a question. I cannot understand what would "break" the existing data validation in column F if the data being transferred is copied over just as values. But because a macro is OK per your description, all you'd need to do is append the programming code that brings over the pasted values with code that establishes data validation in the occupied cells of column F. Have you tried that and it did not work, and if so, what exactly did not work as you would have expected?

 

[zookeepertx]

You have an entertaining way of presenting a question.

I'm just trying to keep y'all's lives interesting. No, really, I talk with my hands a lot, so, if you can't see my hands, I end up using WAYYY to many words, LOL! If I re-read my question a couple of days later, I think, "What in God's name was I trying to convey!?!?"

I cannot understand what would "break" the existing data validation in column F if the data being transferred is copied over just as values. But because a macro is OK per your description, all you'd need to do is append the programming code that brings over the pasted values with code that establishes data validation in the occupied cells of column F. Have you tried that and it did not work, and if so, what exactly did not work as you would have expected?

Oh, there isn't a macro yet. I'm just trying to establish an "outline" of what I'm going to need it to do and in what order.
When the data is imported/downloaded into the sheet called "Working List" (I'm not sure how it gets there), some adjustments are made to it. At this point all of column F says "Import Order". Then it gets manually pasted onto the sheet called "Complete" where data is manually updated by a few other employees. The problem with pasting just values is that there's absolutely no way I'm going to get random people to remember (or care enough) to paste correctly.

The man that requested the macro has said that he could just - after the data is pasted on the "Complete" tab - put the data validation in himself, but I keep thinking there has to be a way to get it to happen by itself. Sadly, I have no clue how I would write the data validation into macro code. Would it be very difficult? Or would a worksheet change event be possible? (So that, whenever data is pasted into a cell in column F, it triggered data validation to be applied to that cell on that row?) Or, some other idea you might have...

Thanks!

Jenny

 

[Tom Urtis]

So basically it's the Complete sheet in used cells of column F that need to be monitored for validation. I'd either establish a sheet change event like you said, or have a macro that when you execute it would re-set the validation to the used cells when you want it to, after the paste. Not difficult to do per your question, just a matter of getting the logic understood regarding what the validation should do.

 

[zookeepertx]

So basically it's the Complete sheet in used cells of column F that need to be monitored for validation.

Yes, that's correct.

I'd either establish a sheet change event like you said, or have a macro that when you execute it would re-set the validation to the used cells when you want it to, after the paste. Not difficult to do per your question, just a matter of getting the logic understood regarding what the validation should do.

When the data is pasted from "Working" to "Complete" the workbook is handed off to a different person to be evaluated. They need to have the validation in place so they can choose from the options. So, ideally, once the data is pasted onto "Complete", that would be when the validation feature needs to appear on cell F of that row of data. That way, when the next person goes to evaluate the data, the validation is already available to them. That's what made me think a sheet change event might do the job.

After that second person does their evaluation, THEN the macro (that I haven't created yet ) needs to be run. (Which suddenly causes me to worry about whether a macro will run on data chosen from a validation list. It will, right?)

Anyway, the validation information is located on a worksheet called (conveniently) "Validation lists", in cells A2:A14.

Jenny