Results 1 to 14 of 14
  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Perhaps a "mask" (XL2000 9.0.3821 SR-1)

    There should be, and perhaps there is, a way to force the user to enter information into a user-form that meets certain requirements. This is driving me nuts and costing me hours of frustration.

    Examples:
    1. In a textbox, I would need the user to enter a date. Can't use a listbox as any date would be okay.

    2. In a textbox, I would need the user to enter a social security number. There should be a way to force the user to follow the proper format ###-##-####.

    3. How about textboxes where currency is the expected response: $###.##. Even if the user entered "3", the form should automatically adjust the entry to $3.00

    I've got a stack of Excel VBA reference material and I've often tried Excel's Help screens but I cannot find solutions to what should be a simple undertaking. I'm not looking to have all my problems solved but perhaps someone would know where I could look for solutions?? As you can probably tell from previous posts, I'm using userforms to populate a database. If the correct information is not put into the database, then it causes huge problems.
    - Ricky

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Perhaps a "mask" (XL2000 9.0.3821 SR-1)

    I must say that I don't have much experience with this, but you can add more controls to the Control ToolBox. There exists a Microsoft Masked Edit Control version 6.0, which you can add to your control toolbox. Just right-click the control toolbox on a place where there are no controls and select the 'Extra ...' (The word is in Dutch in my version and I don't know what will be it in English, maybe something like 'Extra Controls'. Anyhow, you should get a list of available extra controls (at least if you have installed that option). Just give it a try, this is maybe what you need for your masks.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Perhaps a "mask" (XL2000 9.0.3821 SR-1)

    You can use the textbox's events to do enforce any kind of requirements or formatting you want. The Change or Enter events are probably the ones that you want.

    If you don't want to write your own code, then you will need to add additional controls that implement what you want.
    Legare Coleman

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Perhaps a "mask" (XL2000 9.0.3821 SR-1)

    Hans: I can't seem to find the MS Masked Edit Control you're speaking of, nor can I find any documentation or mention of it in the MS knowledge base. I must have missed something during the installation?
    - Ricky

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Perhaps a "mask" (XL2000 9.0.3821 SR-1)

    For the Maked Edit Control, you have to select Tools >> References in the Visual Basic Editor. Then you should see a list of available references (and ActiveX controls, dlls, etc.). Check the box at the left to make it available during runtime. If the file you are looking for is not in the list, you can try to find it using the Browse button or you have to make sure that it has been installed, of course.

    However, there is another possibility to check if a string matches a pattern. In the code below, you can find an example of using the 'like' command in VBA:

    <pre>Function MatchPattern(text As String, pattern As String) As Boolean
    If text Like pattern Then
    MatchPattern = True
    Else
    MatchPattern = False
    End If
    End Function
    </pre>


    <pre>Sub EnterData()
    Dim ans As String
    Dim pattern As String
    pattern = "???-###-???"
    ans = Application.InputBox("Enter Data:", "Data Entry Trial", , , , , , 2)
    MsgBox MatchPattern(ans, pattern)
    End Sub
    </pre>


    In the pattern you can use
    ? for any single character
    * zero or more characters
    # any single digit
    [charlist] any single character in charlist
    [!charlist] any single character not in charlist

    This is a very simple - but yet - very powerful tool for validating data that is being entered.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Perhaps a "mask" (XL2000 9.0.3821 SR-1)

    Hans,

    I don't think that Masked Edit control is freely available, and may only be provided with VB as opposed to VBA, unless perhaps in the Developers edition of Office. Ether way, I do not think that you can develop applicatuons using it unless it licenced in your environment. It may be present on your PC as part of some installed software, but not available for development. That is my understanding of the Masked Edit control, and some others are in the same boat.

    Andrew C

  7. #7
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Perhaps a "mask" (XL2000 9.0.3821 SR-1)

    Andrew,

    You are right. It must have been installed with my VB 6.0 version. I didn't know that these extra controls became available in the list of Microsoft Excel's controls for VBA. I thought it was part of an extensive installation of Office 2000. So, the second part of my last post should help validate the entered data. Maybe the mask can be put in as default, or in the prompt or title to show the user what form of data is expected to be entered.

    Moreover, I was even wrong saying that it could be found under tools >> references. It is available (at least for me) when right-clicking the control toolbox when a userform is edited. (extra controls ...).

  8. #8
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Perhaps a "mask" (XL2000 9.0.3821 SR-1)

    The "Masked Edit Control" shows up on my list (that is, right-click the controls toolbar in the VBE), but when you attempt to show it on the toolbar, it says it is not properly licensed. It shows up on the list in Excel 2000 VBE even though I don't have VB installed. It is apparently part of the Office 2000 Standard installation, but not part of the license.

  9. #9
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Perhaps a "mask" (XL2000 9.0.3821 SR-1)

    See attached for samples of how to do this...
    Attached Files Attached Files

  10. #10
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Perhaps a "mask" (XL2000 9.0.3821 SR-1)

    Here is my approach. It has one quirk which I didn't untangle, that is, if you close the dialog with a "bad" SS# in the text box, the Exit event is triggered, and you get the error MsgBox. Anyway, advantages to this approach:

    1) Non-digits automatically disregarded
    2) You can tab out of the text box to the next control
    3) User can leave the entry blank (if they start typing, then realize they don't know the actual SS#, you don't want to force them to finish entering something.)
    4) Message box gives feedback when invalid entry is attempted, with two options: leave it blank for now, or fix the entry.

    I only accounted for the SS# case, but you can extrapolate to dates, etc.

    Here is the code, for those who don't want to look at the file itself:

    <pre>Option Explicit


    Private Sub txtSS_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    Dim MsgText As String, MsgType As Integer, _
    MsgTitle As String

    With txtSS
    Select Case Len(.Text)
    Case 11, 0
    ' Either entry is valid, or there is no entry, don't bother

    Case Else ' Entry is not valid
    MsgText = "The entry is not a valid SS#." & vbCrLf & vbCrLf & _
    "Choose OK to re-enter, or Cancel" & vbCrLf & _
    "to continue without entering a SS#."

    MsgType = vbOKCancel + vbExclamation + vbDefaultButton1
    MsgTitle = "Your App"
    If MsgBox(MsgText, MsgType, MsgTitle) = vbOK Then ' User chose OK
    .SelStart = 0
    .SelLength = Len(.Text)
    Cancel = True ' Keep focus
    Exit Sub

    Else ' User chose Cancel
    .Text = ""
    Cancel = False ' Let focus go to next in form tab order
    Exit Sub

    End If ' MsgBox(MsgText, MsgType, MsgTitle) = vbOK

    End Select ' Case Len(.Text)

    End With ' txtSS

    End Sub ' txtSS_Exit(ByVal Cancel As MSForms.ReturnBoolean)


    Private Sub txtSS_Change()

    Dim LastChar As String

    With txtSS
    Select Case Len(.Text)
    Case 1, 2, 4, 5, 7 To 11 ' When entry is this long, _
    ' last character should be numeric.
    ' (Ignore "-" added by event code).
    LastChar = Right(.Text, 1)
    If Not IsNumeric(LastChar) And LastChar <> "-" Then
    ' Pop the last character off the end
    .Text = Left(.Text, Len(.Text) - 1)

    End If ' Not IsNumeric(LastChar) And LastChar <> "-"

    Case 3, 6 ' When entry is this long, add the "-" separator

    If Len(.Text) > .Tag Then ' This If prevents the "-" from being
    ' added again if the user is changing
    ' the length by using the backspace.
    ' Otherwise, they would not be able to
    ' backspace over the existing "-". Try
    ' commenting out the If, End If, to see.
    .Text = .Text & "-"

    End If ' Len(.Text) > .Tag

    Case Is > 11 ' When entry is more than 11 characters long, +
    ' truncate the extras.
    .Text = Left(.Text, 11)

    End Select ' Case Len(.Text)

    .Tag = Len(.Text) ' Record the final length in the tag

    End With ' txtSS

    End Sub ' txtSS_Change()
    </pre>


    Enjoy!

  11. #11
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Perhaps a "mask" (XL2000 9.0.3821 SR-1)

    <P ID="nt"><font size=-1>(No Text)</font>
    Attached Files Attached Files

  12. #12
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Perhaps a "mask" (XL2000 9.0.3821 SR-1)

    Jim,

    If you obtained an application that incorporated the Masked Edit Control, then the control is installed on your system but is not licensed for development work. It will function in the application(s) in which it was included, but you cannot develop applications using it.

    It is not included with Office 2000, except perhaps in the Developers edition.

    Andrew

  13. #13
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Perhaps a "mask" (XL2000 9.0.3821 SR-1)

    That makes sense, thanks!

  14. #14
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Perhaps a "mask" (XL2000 9.0.3821 SR-1)

    A couple comments about the code I posted earlier:

    1) If you type a letter for the third or fifth SS# digit, it will be allowed (which is not proper). The code needs another Not IsNumeric check in the Case 3, 6 block.

    2) A more elegant solution for dates might be, allow any entry, then in the TextBox_Exit event, check if the entry can be interpreted as a date with IsDate, and if it can, format it to the desired mask using the Format function. (I adapted that code to a text box for date entry, and it allowed 31/01/2001 as an entry, interpreting it as first of January, 2001, but doesn't reformat it. The more elegant suggestion will reformat it to 01/31/2001, and allow for any user's preferred format for typing dates.)

Posting Permissions

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