Where I had Textbox1 it was to show the name associated with the employee #.
Rename that to the name of the textbox on the form that has the Employee
Name. The combobox has two columns. Employee # & Employee Name. It looks
like you are adding the combobox and then trying to assign your existing
textbox (TextEmployeeNumber) the value of the combobox. Combobox1.Value is
whatever employee # is typed in the combobox or selected. The expression
=ComboBox1.Column(1) is actually the second field which is the employee name.
It should be the name of the textbox where the name is being shown. Don't
put anything in the ControlSource in the Properties, the
VB code puts the
value in the cell. If you need to put the name in a cell next to the number
then use Range("D3") = ComboBox1.Column(1).
And, if TextEmployeeNumber is not the name of the existing textbox,
VB is
thinking you created a variable name. You said you had three textboxes. I
misread and I thought one was employee #, one was employee name and a third
was date.
You could add another textbox on the form and the employee name will show
right there.
"TimN" wrote:
Rich,
I understand now what you are saying and I like the idea. I implemented the
code as you suggested and I get a compile error Variable not defined on the
4th line of code below ...TextEmployeeNumber = ComboBox1.Column(1)
Here is my code with my specific names:
Private Sub UserForm_Initialize()
If Not IsEmpty(Range("C3")) Then
ComboBox1 = Range("C3")
TextEmployeeNumber = ComboBox1.Column(1)
End If
End Sub
Private Sub ComboBox1_Change()
On Error Resume Next
Range("C3") = ComboBox1.Value
TextEmployeeNumber = ComboBox1.Column(1)
End Sub
What am I doing wrong??
"Rich J" wrote:
if they start typing, the number will fill in automatically so you would have
the best of both.
I put error check in and seems to work well typing in the number.
in module attached to whatever control calls your userform
Sub EmployeeForm()
On Error Resume Next
UserForm1.Show
End Sub
in userform code
Private Sub UserForm_Initialize()
If Not IsEmpty(Range("C5")) Then
ComboBox1 = Range("C5")
TextBox1 = ComboBox1.Column(1)
End If
End Sub
Private Sub ComboBox1_Change()
On Error Resume Next
Range("C5") = ComboBox1.Value
TextBox1 = ComboBox1.Column(1)
End Sub
"TimN" wrote:
Thanks for the ideas. I had thought about the list box. My hesitation is
there are 1,800 employees and all I am interested are those who are out on
Short Term Disability, maybe just 10 or so at any given time. And further,
this tool would be used by a department admin. who would typically only have
one or maybe two people out from his/her department at any given time. It
would be a big list to look through to find one or two people.
Might still be a good option though. Thanks again for the help.
"TimN" wrote:
I have a user form that has three text boxes along with an OK command and a
Cancel command. The first textbox is an employee ID number, the next two are
dates. The Employee ID is entered and then a VLOOKUP formula goes to another
worksheet and finds the employee name and other data. If it doesn't find a
name I have written into the formula to enter "Invalid Employee Number" in
the cell.
Here is the situation:
If an invalid employee number is entered and the user doesn't notice bofore
clicking OK, the "invalid" data is copied to another hidden worksheet and it
is just a sheet of errors since the employee number wasn't valid.
OR
If the user enters a valid number, but doesn't notice until he/she hits OK
that the wrong employee number was entered, then that wrong employee data is
copied to the hidden worksheet.
What is the best way to prevent either of these from getting copied before
the user is certain the correct employee number is entered?
I attempted to create a MsgBox at the first Input box asking "Is this the
Correct Employee Number?". However the message box appeared after a single
digit was entered. For example if the employee number is 1554, the message
box popped up when you type 1, not at the end of typing 1554.
What do you experts suggest????