Text Box With Input Mask

[brandon032190]

Need an ActiveX text box that supports Format masks (i.e.: Date and/or Currency). Apparently, MS-Excel 2000 does not have one. Any ideas?

 

[Jaafar Tribak]

Maybe you should try placing this code in the following textbox event:

Private Sub TextBox1_LostFocus()

If TextBox1.Text Like "??[/-]??[/-]??" Then

TextBox1.Text = Format(TextBox1.Text, "long date")
Exit Sub


Jaafar.

End If

TextBox1.Text = Format(TextBox1.Text, "currency")

End Sub

 

[Jaafar Tribak]

Sorry, I don't know how the hell my name "Jaafar" got inside the code!!!

Please remove it before pasting the code.

Jaafar.

 

[brandon032190]

Thank you for your reply. What I am looking for is a way to "mask" the input befor information is entered. This can be done with MS-Access text boxs as they have an "Input Mask" property. I am looking for an text box control that has a mask property or similar functionality.


Thanks again for your help.

 

[brandon032190]

Thank you for your reply. What I am looking for is a way to "mask" the input befor information is entered. This can be done with MS-Access text boxs as they have an "Input Mask" property. I am looking for an text box control that has a mask property or similar functionality.


Thanks again for your help.

 

[Jaafar Tribak]

Hi,

To mask the users's input as the data in being input,you need to set the "Passwordchar" property of the textbox.

Display the the control toolbox toolbar
Click the Ruler Icon.This is to enter Design Mode.

Doubleclick the textbox.This will bring up the VBEditor.

In the properties window,scroll down until you reach the "Passwordchar" and enter a character of your choice as a character mask.For eg "*".This should be entered in the empty field to the right.

Now just exit Design Mode by cliking again on the ruler icon on the controlbox toolbar.

You are done.


This however will not display any input character holders.It just behaves like a Password entry box.

Hope this helps.

Jaafar.

 

[parry]

Hi Brandon, you are using a form but there is cell validation (but no input mask) available in Excel. See this link...

http://www.utoronto.ca/ams/news/99/html/99-2.htm

I wouldnt mind an input mask myself and didnt have any joy when I looked some time ago for a control/addin. Jaafars idea of using the password mask is an interesting one.

 

[martinely95]

As I didn't find and input mask for dates on the Internet, I created this code:

Dim NewString, MyString, mask As String 
Dim position, pos As Variant  
Private Sub TextBox1_Change() 
If IsNumeric(Right(TextBox1.Text, 2)) And Len(TextBox1.Text) >= 11 Then 
    TextBox1.Text = Left(TextBox1.Text, Len(TextBox1.Text) - 1) 
Else 
    position = TextBox1.SelStart MyString = TextBox1.Text 
    pos = InStr(1, MyString, "_") 
If pos > 0 Then 
    NewString = Left(MyString, pos - 1) 
Else
    NewString = MyString 
End If
If Len(NewString) < 11 Then
    TextBox1.Text = NewString & Right(mask, Len(mask) - Len(NewString)) 
    TextBox1.SelStart = Len(NewString) 
End If 
End If
If Len(TextBox1.Text) >= 11 Then
     TextBox1.Text = Left(TextBox1.Text, 10) 
End If
End Sub  
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) 
position = TextBox1.SelStart
If KeyCode = 8 Then 
    TextBox1.Text = mask 
End If 
End Sub  
Private Sub UserForm_Initialize() 
TextBox1.SelStart = 0 
mask = "__.__.____" 
TextBox1.Text = mask
End Sub

You have to put this on a Userform. Change the "TextBox1.Text" with the name of your textbox.You can easily adapt it for a textbox on a sheet.