VBA with userform in excel

[jeff.white]

This is a second post, trying to 'word' it better than my earlier
post. My excel file: contains columns A to U with about 1600 rows of
data. The data is all employee information, including Employee ID,
Name, Supervisor Name, Hire Date, Salary...etc.

What I have so far is a user form with a combobox1 and a textbox1.

combobox1 properties include the row source to equal: A2:A1600

Column A is the employee ID. This works fine, the ID show up in the
drop down.

For the Textbox1 I would like the value from Column B (employee name)
to be displayed where the ID is shown in the combobox1.

I'm very new to VBA and userforms, here is what I have so far....

Private Sub ComboBox1_Change()
Dim ID

ID = ComboBox1.Value

If ComboBox1.Value <> "" Then
TextBox1.Text = ActiveCell.Offset(0, 1).Value
End If

End Sub

I know this is way wrong, but what shows up in the Textbox is the
Column Heading 'Name' from column B. I'm looking for, of course, the
name of the employee. For example, if cell A50's value is 5300 and
B50 is John Smith...it is John Smith I'd like to see in textbox1. I
hope this is making sense....as suggested from other postings, I'm
trying to explain the problem as well as give examples of what I
tried...thanks for those that respond!....

 

[Guest]

Try:

Change Sheet1 to you sheet containing your data


Private Sub ComboBox1_Change()
Dim ID

ID = ComboBox1.Value

If ComboBox1.Value <> "" Then
TextBox1.Text = VLOOKUP(ID,Sheet1!A:B,2,0)
end if
End Sub

 

[jeff.white]

Try:

Change Sheet1 to you sheet containing your data

Private Sub ComboBox1_Change()
Dim ID

ID = ComboBox1.Value

If ComboBox1.Value <> "" Then
TextBox1.Text = VLOOKUP(ID,Sheet1!A:B,2,0)
end if
End Sub















- Show quoted text -

Toppers - thanks, but when I try your suggestion I get what looks like
a syntax error. 'expecting a seperater or )'. Any ideas?

 

[Guest]

VBA is rusty ....
try

Application.vlookup(.....)

I'm signing off for today (late UK-time!)

 

[jeff.white]

VBA is rusty ....
try

Application.vlookup(.....)

I'm signing off for today (late UK-time!)






- Show quoted text -

Thanks, have a good night then. I did try adding the
application.vlookup, but same result...! Thanks again!

 

[Guest]

... must get new brain ....


Private Sub ComboBox1_Change()

Dim ID
Dim rng As Range
ID = ComboBox1.Value
Set ws1 = Worksheets("Sheet1")
Set rng = ws1.Range("A1:B100")
If ComboBox1.Value <> "" Then
TextBox1.Text = Application.VLookup(ID, rng, 2, 0)
End If
End Sub