Validate issue

[Guest]

I am using cell validation. I have noticed if I copy a cell from a
different area on the sheet that does not have validation it will take the
validation away from my original cell.

Why is this? How can I make it so a user cannot wipeout the validation.

Thank you for your help.

Steven

 

[Bernie Deitrick]

Steven,

You can use the selection change event for the sheet. For example, the code
below will protect the Data Validation in cells B2:B100. Copy the code,
right-click the sheet tab, select "View Code" and paste the code in the
window that appears. Note that you may want to let the users know why they
can't paste after selecting one of those cells....

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("B2:B100")) Is Nothing Then Exit Sub
Application.CutCopyMode = False
End Sub

 

[Guest]

Brilliant! Thank you.

Steven,

You can use the selection change event for the sheet. For example, the code
below will protect the Data Validation in cells B2:B100. Copy the code,
right-click the sheet tab, select "View Code" and paste the code in the
window that appears. Note that you may want to let the users know why they
can't paste after selecting one of those cells....

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("B2:B100")) Is Nothing Then Exit Sub
Application.CutCopyMode = False
End Sub

 

[Guest]

This is very nearly what I'm looking for!

The code below will just prevent a user pasting into a cell that has has
validation rules applied to it. I would like to allow a user to paste a
value as long as it meets my data validation rules.

I only want users to be able to enter a number which is 11 digits long and
it must start with "07". I'm sure this is possible but I've not yet found
anyone able to help.

please please please try to offer me a solution - I'm getting desperate!
Thanks

 

[Bernie Deitrick]

Pablo,

Try this: the 13 (rather than 11) is due to extra control characters in the text string returned
from the clipboard.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim SomeVar As String
Dim MyDataObj As New DataObject

If Intersect(Target, Range("B2:B100")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Application.CutCopyMode = False Then Exit Sub

MyDataObj.GetFromClipboard
SomeVar = MyDataObj.GetText()

If Left(SomeVar, 2) = "07" And Len(SomeVar) = 13 Then Exit Sub

Application.CutCopyMode = False

End Sub

 

[Bernie Deitrick]

Oooops...... I forgot to mention that this requires a reference to MS Forms 2.0 Object Library....

HTH,
Bernie
MS Excel MVP

 

[Guest]

Hi Bernie,

This is looking good so far but I'm not sure what you mean about the MS
Forms 2.0 Object Library?? How do I make that bit work?

 

[Bernie Deitrick]

Pablo,

In the VBE, with the project active, use Tools / References, and look for "Microsoft Forms 2.0
Object Library", and put a check next to it.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Bernie - Thats great!

The next enhancement to this would be to allow users to paste more than one
value at a time. If a user has a list containing 5 values and they all meet
the validation rule then allow the paste. If one of the values is incorrect
then just cutcopy= false will be fine.

If you are bored with this now I'll fully understand! Your help this far is
greatly appreciated!

Thanks

 

[Bernie Deitrick]

Pablo,

Try the version below.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim SomeVar As String
Dim MyDataObj As New DataObject
Dim AllVar As Variant
Dim i As Integer

If Intersect(Target, Range("B2:B100")) Is Nothing Then Exit Sub
If Application.CutCopyMode = False Then Exit Sub

MyDataObj.GetFromClipboard
SomeVar = MyDataObj.GetText()

AllVar = Split(SomeVar, Chr(13) & Chr(10))
For i = LBound(AllVar) To UBound(AllVar)
If AllVar(i) <> "" Then
If Left(AllVar(i), 2) <> "07" Or Len(AllVar(i)) <> 11 Then
Application.CutCopyMode = False
End If
End If
Next i

End Sub

 

[Guest]

Oh, you're good!

Thanks for this.

Pablo,

Try the version below.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim SomeVar As String
Dim MyDataObj As New DataObject
Dim AllVar As Variant
Dim i As Integer

If Intersect(Target, Range("B2:B100")) Is Nothing Then Exit Sub
If Application.CutCopyMode = False Then Exit Sub

MyDataObj.GetFromClipboard
SomeVar = MyDataObj.GetText()

AllVar = Split(SomeVar, Chr(13) & Chr(10))
For i = LBound(AllVar) To UBound(AllVar)
If AllVar(i) <> "" Then
If Left(AllVar(i), 2) <> "07" Or Len(AllVar(i)) <> 11 Then
Application.CutCopyMode = False
End If
End If
Next i

End Sub