Textbox needs to format telephone and more...

[FreakSister286]

VBA noob here! I still get stuck on the simplest of things.

EXPLAIN: This is a Textbox I drew on top of 2 merged cells named Telephone. 'Telephone' is also the Named Textbox as well:

I searched the forum and web but only see one answer or another. I need multiple things to happen in one Textbox (on Exit or Tab is OK instead of Instant Error).

1. Format 'Telephone' as "(000) 000-000"
2. Need error if it's not numeric or too many characters
3. 'Enter' key should clear the Error
Then it should go back to
4. Highlight all of the text in the Textbox so it can be re-entered in whole (instead of having to use your mouse/click/highlight, etc.)
5. Hitting Tab key should bring up a dialog box that shows the number entered, thereby reminding the Clerk to Verify the telephone number with the Customer
5. Once verified the Clerk can clear the Dialog Box and it automatically takes him to the next Textbox once 10 numbers are entered and verified.

Shew! Alot huh?

Any push in the right direction would be great and thanks in advance for your help!

-FS

 

[Warship]

Welcome to the board.

Maybe this will "push" you...

Private Sub Telephone_Change()
    Dim response As String
    
    If Len(Telephone) = 0 Then Exit Sub
        
    If Asc(Right(Telephone, 1)) < 48 Or Asc(Right(Telephone, 1)) > 57 Then
        Beep
        Telephone = Left(Telephone, Len(Telephone) - 1)
    End If
    
    If Len(Telephone) = 10 Then
        response = MsgBox("Is this number correct?" & vbLf _
            & Format(Telephone, "(###) ###-####"), vbYesNo, "Verify Number with Customer")
        If response = vbYes Then
            Telephone = Format(Telephone, "(###) ###-####")
            NextTextBox.Activate
        Else
            Telephone = ""
        End If
    End If
    
End Sub

You may need to tweek if you'll be editing the textbox later.

 

[FreakSister286]

WOW! Works perfectly! Thank you WS!!