Validation with Macro to count characters

  • Thread starter Thread starter Eladamri
  • Start date Start date
E

Eladamri

Hi Guys,

Can you help me out.

I want to come up with a validation on Cell A1. The total number o
characters that will be entered should be exactly 11. If the number o
the characters that I entered is not exactly 11 it will be great i
there is a macro program that will show a message box that will promp
me how many characters am I off.

any help is greatly appreciated. :
 
Try this in the Worksheet's code module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address(True, True) = "$A$1" Then
If Len(Target.Value) <> 11 And Target.Value <> Empty Then _
MsgBox "You have entered " & Abs(11 - Len(Target.Value)) & " too " _
& IIf(Len(Target.Value) > 11, "many", "few") & " characters"
[a1].Select
End If
End Sub


Co
 
Eladamri,
You can use Data>Validation for this. No code necessary.
Although it will not tell you how many characters you are off, only that it
is NOT 11.

If you really need the number chars off then:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D1")) Is Nothing Then
If Len(Target.Text) <> 11 Then
MsgBox "Not 11 chars: Length= " & Len(Target.Text) - 11
Target.Select
End If
End If
End Sub

Obviously changing "D1" to the range in question.

NickHK
 
colofnature said:
Try this in the Worksheet's code module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address(True, True) = "$A$1" Then
If Len(Target.Value) <> 11 And Target.Value <> Empty Then _
MsgBox "You have entered " & Abs(11 - Len(Target.Value)) & " too " _
& IIf(Len(Target.Value) > 11, "many", "few") & " characters"
[a1].Select
End If
End Sub


Col
Click to expand...

Hi Colofnature,

I received an error message saying that Object Required in
If Target.Address(True, True) = "$A$1" Then
would you know how to correct this?
anyway im just using the visual basic editor in MS Excel
 
Er, no... I can't replicate the error so I'm not sure what's causing it
:confused: I'll try to work out what I'm doing wrong (or not doin
wrong, if you see what I mean...) and get back to you.
 
Back
Top