Previous Topic Next Topic

User Friendly Code for Admission No

Solved/Closed
smuneeb Posts 77 Registration date Saturday September 5, 2015 Status Member Last seen March 8, 2017 - Jan 18, 2016 at 08:22 AM
smuneeb Posts 77 Registration date Saturday September 5, 2015 Status Member Last seen March 8, 2017 - Jan 26, 2016 at 10:33 PM
Hello,

I would like to request to Code for Admission No
Range is started from B11 to B510
If user enter a123456789 or a-123456789 it always converted into
A-123456789
[A-Z] {1} First Character must be any alphabet in UPPER CASE between A to Z and max strength is 1
[-] {1} After first character Hyphen symbol must be shown and max strength is 1
[0-9] {1 to 9} after hyphen symbol any numeric character between 0 to 9 where min strength could be 7 and max strength could be 9.

Thanks & Regards

3 replies

Answer 1 / 3
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 545
Jan 19, 2016 at 11:49 AM
Isn't this the same question as this?:

https://ccm.net/forum/affich-861038-difficult-query#p861975
1
smuneeb Posts 77 Registration date Saturday September 5, 2015 Status Member Last seen March 8, 2017 1
Jan 20, 2016 at 07:29 AM
Hi TrowaD
It is similar but the solution is not user friendly its a formula, and its not valuable when user is enter in B column where range is fixed B11 to B 510.
I am not demotivating your solution, you create it with your concept which is appreciable.

Thanks
0
Answer 2 / 3
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 545
Jan 26, 2016 at 12:00 PM
Hi Smuneeb,

Good to see you find this user friendly.

How about this code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B11:B510")) Is Nothing Then Exit Sub
If Target.Value = vbNullString Then Exit Sub

If InStr(1, Target.Value, "-") = 2 And Len(Target.Value) >= 9 And Len(Target.Value) <= 11 Then
    If StrComp(Left(Target.Value, 1), UCase(Left(Target.Value, 1)), vbBinaryCompare) = 0 Then Exit Sub
    Target = UCase(Target.Value)
    Exit Sub
ElseIf InStr(1, Target.Value, "-") <> 2 And Len(Target.Value) >= 8 And Len(Target.Value) <= 10 Then
    Target = UCase(Left(Target.Value, 1)) & "-" & Right(Target.Value, Len(Target.Value) - 1)
Else
    Target.Value = vbNullString
    MsgBox "This is not a valid entry."
End If
End Sub


Best regards,
Trowa
1
smuneeb Posts 77 Registration date Saturday September 5, 2015 Status Member Last seen March 8, 2017 1
Jan 26, 2016 at 10:33 PM
Hi Trowa
You are great thanks for cooperation and excellent job
Best Regards
Muneeb
0
Answer 3 / 3
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 545
Jan 21, 2016 at 11:41 AM
Hi Smuneeb,

Then why didn't you let me know in the other post?

You can try the following code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B11:B510")) Is Nothing Then Exit Sub

If InStr(1, Target.Value, "-") = 2 Then
    If StrComp(strFirst, UCase(strFirst), vbBinaryCompare) = 0 Then Exit Sub
    Target = UCase(Target.Value)
    Exit Sub
Else
    Target = UCase(Left(Target.Value, 1)) & "-" & Right(Target.Value, Len(Target.Value) - 1)
End If
End Sub


Best regards,
Trowa
0
smuneeb Posts 77 Registration date Saturday September 5, 2015 Status Member Last seen March 8, 2017 1
Jan 23, 2016 at 03:17 PM
Hi Trowa
Your code is user friendly but please make one more addition as per my request in posted query that after hyphen symbol if the length of numeric character length (not alpha character length i.e. no alpha character is applied after hyphen symbol) is between 7 and 9 then it changes to requirement else it shows an error message.

Thanks
0