Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Validation with Macro to count characters
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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation - Precede with LR and between 6 and 9 characters long. | Excel Discussion (Misc queries) | |||
Data Validation - Only Allowing certain characters | Excel Worksheet Functions | |||
Data Validation - Accept Only Certain Numbers/Characters | Excel Programming | |||
Use Validation to force specific entry of first 3 characters in st | Excel Worksheet Functions | |||
Data validation, alpha or numeric characters | Excel Programming |