Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Jun 2001
    Location
    Vancouver, Br. Columbia
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Masking Data in Dialog Box Controls (VBA Word)

    Does anyone know if there is a way to mask a control in a dialog box in Word VBA? For example, if I want a textbox to only accept numbers in the format of '###.###.####', or mask the input in a textbox control for a postal code 'a#a #a#'?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Masking Data in Dialog Box Controls (VBA Word)

    The MSForms text box (I assume you are talking about userforms) does have an input mask property. If you have Office Developer Edition, you can use the Microsoft Masked Edit Control, but you would have to distribute this to anyone using your document or template, for it is not part of a standard installation. The alternative would be to write code in the On Key Down event of the text box to monitor keystrokes, and to cancel them by changing the key code to 0 if they don't fit the correct pattern.

  3. #3
    Star Lounger
    Join Date
    Jun 2001
    Location
    Vancouver, Br. Columbia
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Masking Data in Dialog Box Controls (VBA Word)

    Thanks so much for the answer. We don't have the option of installing Office Developer Edition, but it's good to know and gives us a good excuse as to why we didn't mask those fields!

  4. #4
    Lounger
    Join Date
    Jul 2003
    Location
    Burnaby, Br. Columbia, Canada
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Masking Data in Dialog Box Controls (VBA Word)

    I thought we were out of the woods on this one, but apparently not. We don't have the option of installing the Microsoft Masked Edit Control to all our users, so it looks like I have to use the KeyDown event in Word 2003 VBA in order to mask a phone number field to ###.###.#### I've never used this event and don't really know where to start. Does anyone have any code snippets to help me understand this?

    Much appreciated, as always!

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Masking Data in Dialog Box Controls (VBA Word)

    Writing a complete masked edit control is a lot of work. Here is a more simplistic solution. The KeyPress event code ensures that the text box TextBox1 will only accept digits and periods. In the After Update event, the value is checked against the pattern ###.###.####:

    Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Dim i As Integer
    If Len(TextBox1) = 0 Then Exit Sub
    If Not Len(TextBox1) = 12 Then
    MsgBox "Entry does not have the required length.", vbExclamation
    Cancel = True
    Exit Sub
    End If
    If Not Mid(TextBox1, 4, 1) = "." Then
    MsgBox "There must be a period in position 4.", vbExclamation
    Cancel = True
    Exit Sub
    End If
    If Not Mid(TextBox1, 8, 1) = "." Then
    MsgBox "There must be a period in position 8.", vbExclamation
    Cancel = True
    Exit Sub
    End If
    For i = 1 To 12
    If Not (i = 4 Or i = 8) Then
    If Not IsNumeric(Mid(TextBox1, i, 1)) Then
    MsgBox "There must be a digit in position" & i & ".", vbExclamation
    Cancel = True
    Exit Sub
    End If
    End If
    Next i
    End Sub

    Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Select Case KeyAscii
    Case vbKeyBack, vbKey0 To vbKey9, Asc(".")
    Case Else
    KeyAscii = 0
    End Select
    End Sub

  6. #6
    Lounger
    Join Date
    Jul 2003
    Location
    Burnaby, Br. Columbia, Canada
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Masking Data in Dialog Box Controls (VBA Word)

    WOW!!! Thanks so much Hans! I tried out this code and it works wonderfully. For me it did one kinda strange thing, which is that it generally displays the MsgBox twice in a row. I'm wondering if maybe there is a property I have set that would be causing this to happen to my code, but not yours. I copied the code exactly, and just modified the name of the textbox...

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Masking Data in Dialog Box Controls (VBA Word)

    The message box will appear twice if you try to close the userform immediately after entering something in the text box. The Before Update event occurs when the focus leaves the text box. If the entry does not fit the pattern, the update is canceled. Closing the userform makes the Before Update event fire again. You can get around it by putting a "Close" command button next to or below the text box, and disabling closing the form through the x in the upper right corner. See the attached example (zipped).

  8. #8
    Lounger
    Join Date
    Jul 2003
    Location
    Burnaby, Br. Columbia, Canada
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Masking Data in Dialog Box Controls (VBA Word)

    Thanks Hans, you're terrific!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •