Checking

[nathaniel.polky]

I can't figure out the syntax to my problem. I have a column that
varies in length for each use. It contains product descriptions and
once entered in excel, it will be uploaded into our inventory
management system. The system has a few constraints for a Product
Description:

1 - Product Descriptions can have no more than 32 characters (I
protected against that with cell validation)
2 - Product Descriptions cannot have the characters - commas, double
quotes and ampersands

I have a macro for when a user enters a value cell by cell, but I can't
seem to tweak it to force the user to change those characters if they
copied a range of cells into the column. Any thoughts?

Thanks

Nathaniel W. Polky



'My Current Macro
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

 

[Dave Peterson]

And copy|paste will destroy the data|validation, too.

How about something like:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim InvChars As Variant
Dim iCtr As Long
Dim myCell As Range
Dim myRng As Range
Dim myStr As String

InvChars = Array(Chr(34), ",", "&")
Set myRng = Intersect(Target, Me.Range("G:G"))

If myRng Is Nothing Then
Exit Sub
End If

For Each myCell In myRng.Cells
With myCell
myStr = Left(.Text, 32)
For iCtr = LBound(InvChars) To UBound(InvChars)
myStr = Replace(myStr, InvChars(iCtr), "_")
Next iCtr

If myStr = .Text Then
'no change
Else
Application.EnableEvents = False
.Value = myStr
Application.EnableEvents = True
MsgBox .Address(0, 0) & " Has been changed!"
End If
End With
Next myCell

End Sub