Validation with Macro to count characters
 

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. :

--
Eladamr
-----------------------------------------------------------------------
Eladamri's Profile: http://www.excelforum.com/member.php...fo&userid=3509
View this thread: http://www.excelforum.com/showthread.php?threadid=56278

Validation with Macro to count characters
 

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

--
colofnatur
-----------------------------------------------------------------------
colofnature's Profile: http://www.excelforum.com/member.php...fo&userid=3435
View this thread: http://www.excelforum.com/showthread.php?threadid=56278

Validation with Macro to count characters
 

Eladamri,
You can use DataValidation 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" wrote in
message ...

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

Validation with Macro to count characters
 

colofnature Wrote:
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

--
Eladamr
-----------------------------------------------------------------------
Eladamri's Profile: http://www.excelforum.com/member.php...fo&userid=3509
View this thread: http://www.excelforum.com/showthread.php?threadid=56278

Validation with Macro to count characters
 

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.



--
colofnatur
-----------------------------------------------------------------------
colofnature's Profile: http://www.excelforum.com/member.php...fo&userid=3435
View this thread: http://www.excelforum.com/showthread.php?threadid=56278