Validation with Macro to count characters

[Eladamri]

Hi Guys,

Can you help me out.

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

any help is greatly appreciated.

[colofnature]

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

[NickHK]

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

"Eladamri" <[email protected]> wrote in
message news:[email protected]...
>
> Hi Guys,
>
> Can you help me out.
>
> I want to come up with a validation on Cell A1. The total number of
> characters that will be entered should be exactly 11. If the number of
> the characters that I entered is not exactly 11 it will be great if
> there is a macro program that will show a message box that will prompt
> me how many characters am I off.
>
> any help is greatly appreciated.
>
>
> --
> Eladamri
> ------------------------------------------------------------------------
> Eladamri's Profile:
http://www.excelforum.com/member.php...o&userid=35099
> View this thread: http://www.excelforum.com/showthread...hreadid=562783
>

[Eladamri]

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

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.

[colofnature]

Er, no... I can't replicate the error so I'm not sure what's causing it. I'll try to work out what I'm doing wrong (or not doing wrong, if you see what I mean...) and get back to you.

C