Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Event Check
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Event Check
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Event Check
"Nathaniel W. Polky" wrote
: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I create event-activated macros that check every cell? | Excel Programming | |||
Check File Name before Save Event | Excel Programming | |||
Check File Name before Save Event | Excel Programming | |||
check folder event | Excel Programming | |||
Change Event.....Spell Check | Excel Programming |