valadation malfunction

[Curt]

Don't if I am blind or just can't see. Something has happened and the
valadtrion is not acting as it did. I moved the paste to see if that was it
get error

..Add Type:=xlValidateTextLength, AlertStyle:=xlValidAlertStop, _
Operator:=xlLessEqual, Formula1:="232"

this line is yellowed out on debug. will enclose complete module. Have left
in my changes till I get it right. You can see the(').
Thanks in Advance
heres the procedure

Option Explicit
Dim cellLength As Integer
'Puts descripition into cell shrink to fit cell
Sub active_offset()
Worksheets("desc").Select
Range("A2").Select
cellLength = Len(ActiveCell)
If msgbox(cellLength & vbNewLine & "If Number above over 232 click Yes As
will not show in print out", _
vbYesNo) = vbYes Then
Exit Sub
Else
Dim Data As Worksheet
Range("a2").Select
Selection.Copy
Sheets("Data").Select
'ActiveSheet.Paste
With Selection.Validation
.Add Type:=xlValidateTextLength, AlertStyle:=xlValidAlertStop, _
Operator:=xlLessEqual, Formula1:="232"
.IgnoreBlank = False
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
ActiveSheet.Paste
' Range("B2").Select
End If
Worksheets("Desc").Select
Range("A2").Select
Selection.ClearContents
Worksheets("Data").Select
Range("A4").Select
' End If
End Sub

 

[Dave Peterson]

I think I'd try deleting any existing Data|Validation rules.

With Selection.Validation
.Delete '<-- added
....

 

[Curt]

if I understand correctly this would inform user that entry is over 232 and
then delete entry. I will do as suggested if this is the way it works I am ok
with it.
Thanks Much

 

[Dave Peterson]

No.

It won't delete the cell (or clear the contents of the cell). It'll delete any
existing data|validation rules for that cell.

 

[Curt]

I put in the .Delete as suggested still had same reaction. If I (') out all
validation no error. Would like to stop entry error by user. If to long do
not allow entry. Do not understand why this quit working as it was ok. Did
not get error befor. Trying to limit so announcer will not have to much to
read. This is a BONO program. To help Veterans Day Parade. Sometimes it is
nice to know what its for FYI. Also I have noticed a large difference in font
area. Up to 30 space difference in same font size. Example D= 198 E =228
wqithin same area. Am I missing something or is this normal
Thanks for your assistance.

 

[Dave Peterson]

Sorry.

I don't have another guess.

Maybe you could drop the whole code and put a warning in an adjacent cell:
=if(len(a1)<=232,"","This is a long comment!"

I put in the .Delete as suggested still had same reaction. If I (') out all
validation no error. Would like to stop entry error by user. If to long do
not allow entry. Do not understand why this quit working as it was ok. Did
not get error befor. Trying to limit so announcer will not have to much to
read. This is a BONO program. To help Veterans Day Parade. Sometimes it is
nice to know what its for FYI. Also I have noticed a large difference in font
area. Up to 30 space difference in same font size. Example D= 198 E =228
wqithin same area. Am I missing something or is this normal
Thanks for your assistance.

 

[Curt]

will take a different approach
Thanks much for input