Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 584
  • Last Modified:

Allow '123 456.7890 EXT 12345' to be pasted into txtboxPhone which has input mask of !\(999") "000\-0000;;_

The textboxPhone has an input mask because users like to see their data entered neatly like  (123) 456-7890.
But, often the users cuts a phone number like "123 456.7890 ext 12345" from an email and tries to paste it into text box.
Normally, a paste does not work because of the input mask stops it.

I "solved" that problem with the attached DoubleClick code, but I want control v to also work.  

I tried On Change event, but that does not even get fired when the clipboard has non-numeric data. Does anybody have any ideas?  
Private Sub Text0_DblClick(Cancel As Integer)

' text0 has input mask of !\(999") "000\-0000;;_"
' The user can copy "123 456.7890 extension 1-2345"
' from an email to clipboard
' then DoubleClick on textbox.  All 15 digits will be
' pasted and will appear in the text box.  The user can then cut the last digits
' and paste them into the extension text box.
' dataobject requires reference to Microsoft Forms 2.0 Object Library
' OPTIONALLY, dim y as regexp requires Microsoft VBScript Regular Expressions 5.5



Dim x As New dataobject
' Dim y As RegExp
Dim y As Object
Set y = CreateObject("vbscript.regexp")
x.GetFromClipboard
y.Pattern = "[^\d]*"
y.Global = True
digits = y.Replace(x.GetText, "")
If Len(digits) = 7 Then digits = "000" & digits
Text0.Value = digits
End Sub

Open in new window

0
rberke
Asked:
rberke
  • 3
1 Solution
 
rberkeAuthor Commented:
I think keydown and key up may solve the problem
0
 
SantiagoACommented:
Instead of setting an input mask to be able to see the numbers like you want, set better the "format" property with the same setting as the input mask. leave the input mask empty.
That way you can enter anything, but will display the way you want.
0
 
rberkeAuthor Commented:
Good suggestion, and I may still do that but I would have to run it by 5 picky users who get annoyed when I change things. They are used to keying things one way, and Format works slightly differently.

Plus, I would still have to add code in the lostfocus event to ensure that the phone number is valid.


And one other point is that the attached code seems to work pretty well,  although it is a little ugly.

Does anybody have any additional solutions that allow the input mask to stay unchanged?
Option Compare Database
Dim downControl17 As Boolean
Dim downLetterV86 As Boolean
Dim JustPasted As Boolean

Private Sub Text0_AfterUpdate()

End Sub



Private Sub Text0_KeyDown(KeyCode As Integer, Shift As Integer)

' If JustPasted Then Stop
Select Case KeyCode
Case 17: downControl17 = True
Case 86: downLetterV86 = True
End Select
If Not JustPasted And (downControl17 And downLetterV86) Then
    JustPasted = True
    ' text0 has input mask of !\(999") "000\-0000;;_"
    ' The user can copy "123 456.7890 extension 1-2345"
    ' from an email to clipboard
    ' then DoubleClick on textbox.  All 15 digits will be
    ' pasted and will appear in the text box.  The user can then cut the last digits
    ' and paste them into the extension text box.
    ' dataobject requires reference to Microsoft Forms 2.0 Object Library
    ' OPTIONALLY, dim y as regexp requires Microsoft VBScript Regular Expressions 5.5
    
    
    
    Dim x As New dataobject
    ' Dim y As RegExp
    Dim y As Object
    Set y = CreateObject("vbscript.regexp")
    x.GetFromClipboard
    y.Pattern = "[^\d]*"
    y.Global = True
    digits = y.Replace(x.GetText, "")
    If Len(digits) = 7 Then digits = "000" & digits
    x.SetText Text:=digits 'Clear
    x.PutInClipboard
    Text0.Value = digits
Else
    JustPasted = False
End If
End Sub

Private Sub Text0_KeyUp(KeyCode As Integer, Shift As Integer)
Select Case KeyCode
Case 17: downControl17 = False
Case 86: downLetter = False
End Select
End Sub

Open in new window

0
 
rberkeAuthor Commented:
I ended up using Lostfocus.

But, because I had the keydown Ctrl V solution working pretty well, I am posting it here in case I ever need the technique.

The users liked it pretty well, but there was a problem which could not be resolved.

I will describe that in the next post.
Option Compare Database




Public Sub CheckTelephoneTextboxForCtrl_v(objThisControl As Control, KeyCode As Integer, Shift As Integer)


    ' The user wants to copy "123 456.7890 extension 1-2345" from an email to the clipboard
    ' then use ctrl v to paste it into a telephone textbox on a form.
    ' if a text box has an input mask of !\(999") "000\-0000;;_"
    ' the ctrl v will not normally work because the input mask only allows numerics to be keyed.
    '
    ' this routine will ctrl v to work.
    '
    ' First, the routine will remove non numerics from the clipboard,
    ' then the resulting numbers will be pasted into the control
    ' if the clipboard contains exactly 7 digits, 000 will be pre-pended as an area code
    '
    
    ' Instruction:
    ' 1) put this subroutine into a regular module
    ' 2) put the following code into yourControl_keydown event
    '    sub yourControl_keydown(eyCode As Integer, Shift As Integer)
    '       call CheckTelephoneTextboxForCtrl_v(yourcontrol, keyCode, Shift)
    '    end sub
    '
    '
    ' ----------- technical notes ------------------
    ' To avoid the need for a keyup event, this routine "cheats" a little
    ' control down and up followed by v down acts as if ctrl and v were down together.
    ' a ctro
    '
    ' this technique will be save here since phone number fields only get numbers.
    '
    ' OPTIONALLY you could use <dim objRegExp as regexp but if you use tools > references >  Microsoft VBScript Regular Expressions 5.5
    
    Static ControlKeyIsPressed As Boolean
    Dim objClipChanger As New dataobject  ' use a data object to manipulate the clipboard
    Dim objRegExp As Object ' use regular expressions to strip non numeric from clipboard
    

Select Case KeyCode
Case 17  ' Control key pressed
        ControlKeyIsPressed = True
Case 86 ' lower case v pressed
' =============== strip non-numerics off clipboard =========================
    ControlKeyIsPressed = False
    Set objRegExp = CreateObject("vbscript.regexp")
    objClipChanger.GetFromClipboard
    objRegExp.Pattern = "[^\d]*"
    objRegExp.Global = True
    digits = objRegExp.Replace(objClipChanger.GetText, "")
    If Len(digits) = 7 Then digits = "000" & digits
    objClipChanger.SetText Text:=digits 'Clear
    objClipChanger.PutInClipboard   ' you cannot skip the putinclipboard step
' ======= the clipboard now only has digits ========
' at this point in the cycle the target textbox STILL retains its previous value
' and the clipboard contains only numeric data, but possibly much more that 9 digits.

' upon exit from this routine, Access will finish the paste operation.
' since the input mask limits the textbox to only 9 digits, any remaining digits will be truncated
' by Access
Case Else
    ControlKeyIsPressed = False
End Select
End Sub

Open in new window

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now