Highlighting Userform Textbox Text To Type-Over

[Ark68]

I have this code which accepts a telephone number entry in a userform textbox (xtended_ci.p_tn1) from the user.

       With p_tn1
            .Enabled = True
            .Locked = False
            .BackColor = clr_blue
            .Text = "###.###.####"
            .SetFocus
            .SelStart = 0
            .SelLength = 12
        End With

The code opens up the field for editing, highlights the field by applying a backcolor, and provides a mask of the data to be entered. I would like this default text to be overwritten by the user's entry without the user having to manually highlight the text. What I have doesn't appear to be the solution.

Open to improvements to make this work.

 

[NoSparks]

Issue is focus moving away from p_tn1 after mask is written to the textbox.
Use Cancel=True in p_tn1_BeforeUpdate to prevent this.

Changed EnterFieldBehavior to 1 and use p_tn1_Change to select individual characters of phone number.
download sample file here

Hope this helps

 

[Ark68]

You're a saviour NoSparks. I really appreciate the extra effort. I like the added feature of the selection being limited only to the character in the mask being written to. Even skips the periods! Nice! Somewhere in that feature, I see the opportunity to ensure that the user only enters numbers.

The one issue now that has cropped up that I'm working through, is when the p_tn1 afterupdate determines the value entered by the user isn't a valid telephone number (ie incorrect number of characters, not numeric), the backcolor turns red to highlight the erroneous control, and as it does after p_name updates, puts the ###.###.#### mask back into the control. I can't seem to mimic the behaviour of your code the the control is reset. I would have thought that resetting the control would do the same as when the mask was set initially. Using Cancel = True in p_tn1_before update I thought would prevent the focus from moving away fro p+tn1 after the mask is written to the textbox as what was identified as the problem doing the same initially.

Private Sub p_tn1_afterupdate()
    Stop
    If Not mbevents Then Exit Sub
    str_p_tn1 = p_tn1.Value
    a1 = Left(str_p_tn1, 3)
    a2 = Mid(str_p_tn1, 4, 3)
    a3 = Right(str_p_tn1, 4)
    'mbevents = False
    
    If Len(str_p_tn1) <> 12 Then
        MsgBox "Please enter a proper telephone number." & Chr(13) & "{###.###.####}", vbInformation, "ERROR: Telephone Format"
        'mbevents = False
        p_tn1.BackColor = clr_red
        p_tn1.Value = "###.###.####"
        'mbevents = True
        Exit Sub
    ElseIf Mid(str_p_tn1, 4, 1) <> "." Or Mid(str_p_tn1, 8, 1) <> "." Then
        MsgBox "Please enter a proper telephone number." & Chr(13) & "{###.###.####}", vbInformation, "ERROR: Telephone Format"
        'mbevents = False
        p_tn1.BackColor = clr_red
        p_tn1.Value = "###.###.####"
        'mbevents = True
        Exit Sub
    ElseIf IsNumeric(a1) = False Or IsNumeric(a2) = False Or IsNumeric(a3) = False Then
        MsgBox "Please enter a proper telephone number." & Chr(13) & "{###.###.####}", vbInformation, "ERROR: Telephone Format"
        p_tn1.BackColor = clr_red
        'mbevents = False
        p_tn1.Value = "###.###.####"
        'mbevents = True
        Exit Sub
    Else
        p_tn1.BackColor = vbWhite
        p_email.Enabled = True
        p_tn1_c.Enabled = True
        p_tn1_b.Enabled = True
        p_tn1_h.Enabled = True
        
        chk_cfc
    End If
End Sub

 

[NoSparks]

Same kind of issue... control loosing focus.

Validation of things should be done in _beforeupdate event where using Cancel=True prevents continuation to _afterupdate and keeps the focus where it is.
Once you get to the _afterupdate event of a control focus will be moving on from that control.

Move validation to p_tn1_BeforeUpdate and eliminate p_tn1_afterupdate, the p_tn1_Change event will continue to look after the character selection.

Private Sub p_tn1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
   
    With p_tn1
        'check for default mask
        If InStr(.Text, "#") = 1 Then
            Cancel = True
            .SelStart = 0
            .SelLength = 1  'Len(.Text)
            .SetFocus
            Exit Sub
        Else
            'validation of whats in textbox
            str_p_tn1 = .Value
            a1 = Left(str_p_tn1, 3)
            a2 = Mid(str_p_tn1, 5, 3)
            a3 = Right(str_p_tn1, 4)
           
            If Len(str_p_tn1) <> 12 Then
                Cancel = True
                MsgBox "Please enter a proper telephone number." & Chr(13) & "{###.###.####}", vbInformation, "ERROR: Telephone Format"
                .BackColor = clr_red
                .Value = "###.###.####"
                Exit Sub
            ElseIf Mid(str_p_tn1, 4, 1) <> "." Or Mid(str_p_tn1, 8, 1) <> "." Then
                Cancel = True
                MsgBox "Please enter a proper telephone number." & Chr(13) & "{###.###.####}", vbInformation, "ERROR: Telephone Format"
                .BackColor = clr_red
                .Value = "###.###.####"
                Exit Sub
            ElseIf IsNumeric(a1) = False Or IsNumeric(a2) = False Or IsNumeric(a3) = False Then
                Cancel = True
                MsgBox "Please enter a proper telephone number." & Chr(13) & "{###.###.####}", vbInformation, "ERROR: Telephone Format"
                .BackColor = clr_red
                .Value = "###.###.####"
                Exit Sub
            Else
                .BackColor = vbWhite
                p_email.Enabled = True
                p_tn1_c.Enabled = True
                p_tn1_b.Enabled = True
                p_tn1_h.Enabled = True
               
                chk_cfc
            End If
        End If
    End With
End Sub

ps: your third validation is questionable. If some one attempting to enter an area code of 235 was to enter 2.5 variable a1 would still be numeric.
also changed the starting character for a2

 

[NoSparks]

for what it's worth...
If this were my project, I wouldn't rely on (or force) the user to do the telephone formatting for me.
I'd let them enter the phone number any way they want,
then in _beforeupdate extract just the digits they entered into a string, format the string the way I want it and write it back to the text box.
Only validation required would be that there are 10 digits.

 

[Ark68]

Hi NoSparks. Triple gold star double thumbs up is the best I can do to virtually show my appreciation. Withthe fear of jinxing myself, I don't want to admit too prematurely that everything is working LOL. If I could, I'd by a round of beers for all.
In addition to resolving my problems for me, you also :
1) inspired me to read up on the difference inthe use of _beforeupdate and _afterupdate. The two have always left me confused as to their difference in when to use, and how they work. The "fear" of using _beforeupdate has likely been the cause of me awkwardly struggling with the appropriate use of _afterupdate
2) that in the true spirit of Excel, there are more than one way to do one thing! (referring to post #24). I would feel terrible now to scrap all the efforts you and others have contributed to get success in this approach, to revert back to a more simple approach LOL. Luckily, my form will allow me an opportunity to revisit the approach later. I will most definately look at the approach you suggested in post 24 moving forward.
3) Reinforced how valuable and wonderful the volunteers here at Mr. Excel unselfishly donate there skill and knowledge to help others. Everyone is so patient, which makes Mr. Excel phenomenal when it comes to helping newbies learn.

Thank you all!!!

 

[NoSparks]

Thanks for the praise Ark68, glad I could assist.
Good luck with the rest of your project.