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
572 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
  • 3
4 Comments
 
LVL 5

Author Comment

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

Accepted Solution

by:
SantiagoA earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now