Unwanted Trigger of Userformn Control

[Ark68]

I am having trouble working with a particular textbox in my userform.

The user enters 10 digits into a textbox. This represents the 10 digits of a telephone number excluding any special characters.
The code below tests and manipulates the entry the user has made.

Private Sub p_tn2_AfterUpdate()
    Stop
    Dim tendigit As String
    'Stop
    With p_tn2
        tendigit = .Text
        If IsValid(tendigit) = False Then
            MsgBox "Please enter telephone number as 10 numbers, no special characters (##########)."
            .Text = ""
            .SetFocus
            Exit Sub
        End If
        ptn2 = CDbl(.Text)
        ac = Left(ptn2, 3)
        ec = Right(ptn2, 4)
        mc = Mid(ptn2, 4, 3)
        ptn2str = ac & "." & mc & "." & ec
        .Text = ptn2str
        p_tn2_h.Enabled = True 'checkbox
        p_tn2_c.Enabled = True 'checkbox
        p_tn2_b.Enabled = True 'checkbox
        p_tn3.Enabled = True
        frm_submit.Visible = True
        btn_submit.Enabled = True
    End With
End Sub

This works wonderfully when the user enters the number initially, but as soon as the user selects another control to use (for instance one of the three checkboxes that are enabled), this code triggers again. Since the value had been formatted as part of the code that previously ran, the IsValid function fails. This shouldn't happen.

I'm unsure what I must do to avoid this behaviour.

 

[jasonb75]

How about using a public boolean variable to skip the code once it has been validated and formatted.

In a regular module

Public RunOnce As Boolean

Private Sub p_tn2_AfterUpdate()
If RunOnce = True Then Exit Sub
    Stop
    Dim tendigit As String
    'Stop
    With p_tn2
        tendigit = .Text
        If IsValid(tendigit) = False Then
            MsgBox "Please enter telephone number as 10 numbers, no special characters (##########)."
            .Text = ""
            .SetFocus
            Exit Sub
        End If
        ptn2 = CDbl(.Text)
        ac = Left(ptn2, 3)
        ec = Right(ptn2, 4)
        mc = Mid(ptn2, 4, 3)
        ptn2str = ac & "." & mc & "." & ec
        .Text = ptn2str
        p_tn2_h.Enabled = True 'checkbox
        p_tn2_c.Enabled = True 'checkbox
        p_tn2_b.Enabled = True 'checkbox
        p_tn3.Enabled = True
        frm_submit.Visible = True
        btn_submit.Enabled = True
    End With
RunOnce = True
End Sub

You will need to add the line

RunOnce = False

to your code at the point where the content of the textbox it to be cleared ready for re-use.

 

[dmt32]

Hi,
try using the Exit Event & see if that helps

Private Sub p_tn2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim tendigit As Variant
    
    With p_tn2
        tendigit = .Text
        If Len(tendigit) > 0 Then
            If Not tendigit Like "##########" Then
                MsgBox "Please enter telephone number as 10 numbers, no special characters (##########)."
                Cancel = True
            Else
                tendigit = CDbl(.Text)
                .Text = Left(tendigit, 3) & "." & Mid(tendigit, 4, 3) & "." & Right(tendigit, 4)
            End If
        End If
    End With
    
    p_tn2_h.Enabled = Not Cancel 'checkbox
    p_tn2_c.Enabled = Not Cancel 'checkbox
    p_tn2_b.Enabled = Not Cancel 'checkbox
    p_tn3.Enabled = Not Cancel
    frm_submit.Visible = Not Cancel
    btn_submit.Enabled = Not Cancel
End Sub

Dave

 

[Ark68]

Thank you both for your help.

Dave, I tried your suggestion, but unless I did something wrong in adapating my exisiting code, the result was the same.
jasonb, so far your suggestion appears to be working. I will have to do the same to 4 other similar fields.

 

[dmt32]

Thank you both for your help.

Dave, I tried your suggestion, but unless I did something wrong in adapating my exisiting code, the result was the same.

no worries if have a solution but did you delete your old code & try suggestion unaltered?

Dave