Need help with MatchEntry on Combo box (Userforms)

[Thomas]

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

 

[Legacy 98055]

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

 

[Thomas]

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 :wink:

~Thomas