Here is some code for you to test out. It would be best if you did that in a
new UserForm (not your current one) until you are sure it does what you
want; then you can incorporate it into your main UserForm. Place a TextBox
on the UserForm and name it txtPhone, then copy/paste all of the code below
into the UserForm's code window. That is it; now, run the UserForm and try
different types of entry and editing into the TextBox (try non-numbers,
deleting digits, and whatnot). I think the code's execution is clean and I
believe I caught and accounted for all the invalid operations a user might
do (of course, if you spot something I missed, let me know and I will try to
patch the code).
'*************** START OF CODE ***************
Dim LastPosition As Long
Private Sub txtPhone_Change()
Dim Cursor As Long
Static LastText As String
Static SecondTime As Boolean
If Not SecondTime Then
SecondTime = True
With txtPhone
If Left(.Text, 1) <> "(" Then .Text = "(" & .Text
If Mid(.Text, 2) Like "*[!0-9)-]*" Then
Beep
SecondTime = True
.Text = LastText
.SelStart = LastPosition
Else
Cursor = .SelStart
Do While InStr(.Text, "-")
.SelStart = InStr(.Text, "-") - 1
.SelLength = 1
.SelText = ""
Cursor = Cursor - 1
Loop
Do While InStr(.Text, ")")
.SelStart = InStr(.Text, ")") - 1
.SelLength = 1
.SelText = ""
Cursor = Cursor - 1
Loop
If Len(.Text) > 4 Then
.SelStart = 4
.SelText = ")"
Cursor = Cursor + 1
End If
If Len(.Text) > 8 Then
.SelStart = 8
.SelText = "-"
Cursor = Cursor + 1
End If
.SelStart = IIf(Cursor < 0, 0, Cursor)
LastText = .Text
LastPosition = Cursor
End If
End With
SecondTime = False
End If
End Sub
Private Sub txtPhone_Enter()
With txtPhone
.MaxLength = 13
If Len(.Text) = 0 Then
.Text = "("
.SelStart = 1
End If
End With
End Sub
Private Sub txtPhone_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
With txtPhone
LastPosition = .SelStart
End With
End Sub
Private Sub txtPhone_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
With txtPhone
If .SelStart = 0 Then
.SelStart = 1
End If
End With
End Sub
Private Sub txtPhone_MouseDown(ByVal Button As Integer, ByVal Shift As
Integer, ByVal X As Single, ByVal Y As Single)
With txtPhone
If .SelStart = 0 Then .SelStart = LastPosition
LastPosition = .SelStart
End With
End Sub
'*************** END OF CODE ***************
--
Rick (MVP - Excel)
JacyErdelt said:
It is in a Userform called frmEnterData.