User Friendly Code for Admission No Solved/Closed

Question

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
Configuration: Windows 7 / Chrome 47.0.2526.111

TrowaD · Answer

Isn't this the same question as this?:

https://ccm.net/forum/affich-861038-difficult-query#p861975

TrowaD · Answer

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

TrowaD · Answer

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

User Friendly Code for Admission No

3 responses

Similar discussions

Subscribe To Our Newsletter!