Paste Event Check

[Guest]

I have the following code that is intended to prevent the user from entering
certain characters. It works fine if the user goes cell by cell and enters
values. The problem occurs when the user copies values into a range of
cells. Is there a way to work around this?

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim blnIsOk As Boolean

blnIsOk = True
If Target.Column = 7 Then
If InStr(1, Target.Value, """") Then
blnIsOk = False
ElseIf InStr(1, Target.Value, ",") Then
blnIsOk = False
ElseIf InStr(1, Target.Value, "&") Then
blnIsOk = False
End If
End If

If Not blnIsOk Then
MsgBox "Commas, double quotes, and ampersands (the & symbol) are not
allowed in product descriptions. Please refer to the Product Description
Policy. Thank You."
Application.Undo
End If

End Sub

 

[johnhildreth]

I don't know why you don't want those characters. Depending on your
reasoning, you could let them enter such characters and check column 7
before some event. For example, before printing, saving, or exiting.
You would do that by looping through the cells in the column.

HTH,
John

 

[PaulD]

: I have the following code that is intended to prevent the user from
entering
: certain characters. It works fine if the user goes cell by cell and
enters
: values. The problem occurs when the user copies values into a range of
: cells. Is there a way to work around this?
:
: Code:
:
: Private Sub Worksheet_Change(ByVal Target As Range)
: Dim blnIsOk As Boolean
:
: blnIsOk = True
: If Target.Column = 7 Then
: If InStr(1, Target.Value, """") Then
: blnIsOk = False
: ElseIf InStr(1, Target.Value, ",") Then
: blnIsOk = False
: ElseIf InStr(1, Target.Value, "&") Then
: blnIsOk = False
: End If
: End If
:
: If Not blnIsOk Then
: MsgBox "Commas, double quotes, and ampersands (the & symbol) are
not
: allowed in product descriptions. Please refer to the Product Description
: Policy. Thank You."
: Application.Undo
: End If
:
: End Sub


You need to wrap the target check in a for loop, something like this

blnIsOk = True
For Each c In Target
If c.Column = 7 Then
If InStr(1, c.Value, """") + _
InStr(1, c.Value, ",") + _
InStr(1, c.Value, "&") > 0 Then
blnIsOk = False
End If
End If
Next c

If Not blnIsOk Then...

Paul D