PDA

View Full Version : Solved: Mask in form



ndendrinos
05-06-2007, 04:31 PM
Hello,
Looked at "properties" on my form and cannot find a way to input a mask to a text box.
I use the form to search for a value in sheet2 from sheet1 and the value (phone number) is
let us say (905) 111-1111 ... I need to be able to input on the form 9051111111 instead of
(905) 111-1111 and find the data on sheet2
Is this possible ?
Here is Malcom's code :
Option Explicit
Option Compare Text

Private Sub CommandButton2_Click()
UserForm1.Hide
End Sub

Private Sub CommandButton1_Click()
Dim Cel As Range
Set Cel = Sheets("Sheet2").Columns(1).Find(TextBox1)
Cel.Resize(, 12).Copy Sheets("Sheet1").Range("A2")
End Sub Initially the code was ment to search by company name and now I need the additional option to search by telephone number.
Thank you

ndendrinos
05-06-2007, 05:13 PM
Solved this one by changing the format on Sheet2 to "telephone" .
Still interested in introducing the "mask" in the code in case the user were to type
i.e. 9051111111 OR 905 111 1111 or any other variation of same.

tstom
05-07-2007, 12:04 AM
You could write some tricky code to enforce the mask yourself. An easier way is to use the MaskedEdit control. This one comes installed with Access. You will need Access on the developers machine and on the users machine unless you have a license to distribute the code such as with Office dev or some other MS ide...

geekgirlau
05-07-2007, 12:49 AM
Generally in Excel you need to write code in the AfterUpdate event of the control to change the format of the text - not the cleanest option, but it works. You can do something like this:


Private Sub txtPhone_AfterUpdate()
Dim strPhone As String

' strip characters from the phone number
strPhone = Replace(Me.txtPhone, " ", "")
strPhone = Replace(strPhone, "(", "")
strPhone = Replace(strPhone, ")", "")
strPhone = Replace(strPhone, "-", "")

' set formatting
strPhone = "(" & Left(strPhone, 3) & ") " & _
Mid(strPhone, 4, 3) & "-" & _
Mid(strPhone, 7, 4)

Me.txtPhone = strPhone
End Sub

Bob Phillips
05-07-2007, 05:21 AM
Big failing in VB/VBA. Should be there but isn't.

ndendrinos
05-07-2007, 05:25 AM
Hello and thank you for the replies.
I'm trying geekgirlau's solution and get an error message
"Method or Data Member not found"
Line highlighted = Me.txtPhone,
I attach file for possible editing of code

Bob Phillips
05-07-2007, 05:45 AM
That is because the textbox is called TextBox1 not TxtPhone.

ndendrinos
05-07-2007, 06:02 AM
With XLD's remark I have changed the code to :

Private Sub CommandButton1_Click()
Dim Cel As Range
Set Cel = Sheets("Sheet2").Columns(1).Find(TextBox1)
Cel.Resize(, 12).Copy Sheets("Sheet1").Range("A2")
End Sub

Private Sub TextBox1_AfterUpdate()
Dim strPhone As String

' strip characters from the phone number"
strPhone = Replace(Me.TextBox1, " ", "")
strPhone = Replace(strPhone, "(", "")
strPhone = Replace(strPhone, ")", "")
strPhone = Replace(strPhone, "-", "")

' set formatting
strPhone = "(" & Left(strPhone, 3) & ") " & _
Mid(strPhone, 4, 3) & "-" & _
Mid(strPhone, 7, 4)

Me.TextBox1 = strPhone
End Sub
and now get an error "91 " / object variable or with block variable not set
and line highlighted is : Cel.Resize(, 12).Copy Sheets("Sheet1").Range("A2")

geekgirlau
05-07-2007, 06:36 PM
If you look at Sheet2, the phone number is displayed with the brackets and spaces etc., however the text in the formula bar does not contain any of those formatting items. Therefore you should be searching for the phone number stripped of all formatting, rather than applying the input mask.

The error occurred because the phone number was not found.



Private Sub CommandButton1_Click()
Dim Cel As Range
Set Cel = Sheets("Sheet2").Columns(1).Find(TextBox1)

If Not Cel Is Nothing Then
Cel.Resize(, 12).Copy Sheets("Sheet1").Range("A2")
Else
MsgBox "The phone number was not found", vbInformation
End If
End Sub

Private Sub TextBox1_AfterUpdate()
Dim strPhone As String

' strip characters from the phone number
strPhone = Replace(Me.TextBox1, " ", "")
strPhone = Replace(strPhone, "(", "")
strPhone = Replace(strPhone, ")", "")
strPhone = Replace(strPhone, "-", "")

Me.TextBox1 = strPhone
End Sub

jammer6_9
05-07-2007, 11:42 PM
Why not use MasEditBox instead of text box... Find attache...

geekgirlau
05-08-2007, 12:14 AM
The issue is one of distributing a non-standard control. In many cases, an environment may be locked down, making it difficult (or impossible) for users to utilise these controls (and yep, the attachment failed for me at work because the object was not available). ndendrinos has stated that they are a beginner at VBA, so I think this represents a level of complexity that is unnecessary at this point.

ndendrinos
05-08-2007, 05:21 AM
Thank you geekgirlau. Works great.
Thank you ALL for your help
Regards, Nick