PDA

View Full Version : [SOLVED] Macro to only allow data to be copy pasted based on data validation criteria



forea
06-17-2016, 10:52 AM
Hi everyone.
I am trying to find a macro that will only allow data to be copy pasted based on data validation criteria.
The user can only enter the following English characters - alphabets (upper and lower), numbers, period, comma, forward slash, hyphen and apostrophe. And with the help of the forum members, I managed to get this formula.
My next task is finding a code where the user is able to copy paste data which satisfies all the above mentioned criteria. If it doesn't, is it possible to have a macro do this? Also, I need the macro to run when the sheet is opened, or force the user to have macros enabled.
I have attached a sample file, with the data validation criteria. (The data validation applies only to rows A8: F207.) I have tried various codes but nothing seems to work. I can't prevent the user from copying and pasting completely, as the sheet usually consists of 5,000 rows worth of information, which will be an absolute pain to type out.
Thank you for any help you can provide.

mdmackillop
06-17-2016, 11:40 AM
Welcome to VBAX
Thanks for the sample file, but why does it not contain any sample data against which we can run your code?

forea
06-20-2016, 05:37 AM
Hi mdmackillop. I apologise for not including any data - my bad.
I have attached a revised file mentioning some samples.
I would appreciate any help you or other members can provide.

mdmackillop
06-20-2016, 09:10 AM
Your intentions are not clear
Do you wish to copy only cells which a) have data validation and b) contain valid data.
Where do these get pasted. Is anything done to cells with no validation or do not meet the criteria?
or
Are you looking to prevent pasting into this sheet of invalid data.
You mention a macro; to do what exactly?

forea
06-21-2016, 02:10 AM
Hi mdmackillop. I would like the user to be able to paste data on the cells which have data validation, only if it meets the criteria. In the sample sheet, this would mean that rows 8, 9, 10 and 12 would not get pasted because of the invalid data in cells D8, D9, A10, B10, A12 and B12. Rows 11 and 13 through 16 would get pasted as this meets all the criteria. (Does this make sense?)
I mentioned a macro because I don't see any other way of putting in this restriction. I am open to all suggestions, especially if there is an easier way, considering the time and effort it takes to write a macro.

snb
06-21-2016, 02:41 AM
Is this meant to provide SAP with to ansi restricted data ?

Please tell us what your purpose is.

forea
06-21-2016, 04:59 AM
Hi snb. I do not understand your query. What is SAP and ansi restricted data? And what do you mean by "Please tell us what your purpose is."?

mdmackillop
06-21-2016, 11:26 AM
Modify this to suit your needs


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:F" & ActiveSheet.Rows.Count)) Is Nothing Then
ColourValidationCells
End If
End Sub




Sub ColourValidationCells()
'http://www.contextures.com/tiptech.html
'If validation value for the cell is false,
'colour the cell red
Dim rng As Range
Dim c As Range


On Error GoTo errhandler
Set rng = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo 0


For Each c In rng
If Not c.Validation.Value Then
c.Interior.ColorIndex = 3
c.ClearContents '<== Added to clear invalid cells
Else
c.Interior.ColorIndex = 6
End If


Next
Exit Sub


errhandler:
MsgBox "No cells with data validation"


End Sub

forea
06-24-2016, 04:29 AM
Hi mdmackillop. Thank you so much for this. It does not work exactly the way I would like it to, but I can definitely work with this and I am in a much better position that when I started. Thank you once again.