Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Need help with MatchEntry on Combo box (Userforms)

  1. #1
    Board Regular
    Join Date
    May 2002
    Location
    Dallas
    Posts
    366
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok...I've searched the board looking for a similar quesiton on this issue, but cannot seem to find the answer, so here goes:

    I have a user form that allows either the new entry or update of a user's information (Employee ID, phone number, e-mail, etc.; each into different designated text boxes). The way it is currently set up, the combo box references a named range; if the user begins to type in a known user's name, all of the rest of the information auto-fills into the text boxes. The problem is as such:

    Say for example that user 'Joe Blow' is in the named range. If *any* portion of this name (in correct order) is typed into the combo box, the rest of the information will auto-fill (ex: oe.... or blo...), which is causing a problem. I need the auto fill to hapen only when a known user's name is typed in, starting with the first letter, not from anywhere within the name.

    VBA code-wise, this is how I've handled it thus far:

    Private Sub ComboBox1_Change()
    UserChoice = UserForm7.ComboBox1.Value
    With Worksheets("Users").Range("PE_Team")
    Set c = .Find(UserChoice, LookIn:=xlValues)
    If Not c Is Nothing Then
    UserForm7.TextBox1.Value = c.Offset(0, 1).Value
    UserForm7.TextBox2.Value = c.Offset(0, 2).Value
    UserForm7.TextBox3.Value = c.Offset(0, 3).Value
    UserForm7.TextBox4.Value = c.Offset(0, 4).Value
    End If
    ' If not matched to known user, clear all entries:
    If c Is Nothing Then
    UserForm7.TextBox1.Cut
    UserForm7.TextBox2.Cut
    UserForm7.TextBox3.Cut
    UserForm7.TextBox4.Cut
    End If

    End With

    End Sub

    ///////
    Can anyone help me with this one? I tried using ComboBox1_AfterUpdate as well, and this didn't seem to do the trick.

    Much thanks in advance
    ~Thomas

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    There might be a prettier way, but how about placing a button on your form such as "New"?
    or "Add"?
    This button would set a boolean flag to exit your change event at the beginning of the procedure.

    Tom


  3. #3
    Board Regular
    Join Date
    May 2002
    Location
    Dallas
    Posts
    366
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That's a good idea, but the hope is to create something with as little possibility for error as possible. With the number of people that will be using this, if there is a possibility of error; I'm sure it will be inadvertently found. With no offence to my co-workers, I need this as 'idiot-proof' as possible

    ~Thomas

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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