Solved

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

Posted on 2010-08-25
4
579 Views
Last Modified: 2012-05-10
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
Comment
Question by:rberke
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 5

Author Comment

by:rberke
ID: 33526563
I think keydown and key up may solve the problem
0
 
LVL 3

Accepted Solution

by:
SantiagoA earned 500 total points
ID: 33526589
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
 
LVL 5

Author Comment

by:rberke
ID: 33526767
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
 
LVL 5

Author Comment

by:rberke
ID: 33545290
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question