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