Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA with userform in excel
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!.... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA with userform in excel
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 " wrote: 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!.... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA with userform in excel
On Jul 11, 3:42 pm, Toppers wrote:
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 " wrote: 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!....- Hide quoted text - - 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA with userform in excel
VBA is rusty ....
try Application.vlookup(.....) I'm signing off for today (late UK-time!) " wrote: On Jul 11, 3:42 pm, Toppers wrote: 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 " wrote: 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!....- Hide quoted text - - 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA with userform in excel
On Jul 11, 4:32 pm, Toppers wrote:
VBA is rusty .... try Application.vlookup(.....) I'm signing off for today (late UK-time!) " wrote: On Jul 11, 3:42 pm, Toppers wrote: 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 " wrote: 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!....- Hide quoted text - - 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?- Hide quoted text - - Show quoted text - Thanks, have a good night then. I did try adding the application.vlookup, but same result...! Thanks again! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA with userform in excel
... 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 " wrote: On Jul 11, 4:32 pm, Toppers wrote: VBA is rusty .... try Application.vlookup(.....) I'm signing off for today (late UK-time!) " wrote: On Jul 11, 3:42 pm, Toppers wrote: 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 " wrote: 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!....- Hide quoted text - - 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?- Hide quoted text - - Show quoted text - Thanks, have a good night then. I did try adding the application.vlookup, but same result...! Thanks again! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
display status bar value in excel userform? | Excel Discussion (Misc queries) | |||
Userform in excel | Excel Discussion (Misc queries) | |||
Userform | Excel Discussion (Misc queries) | |||
Userform | Excel Discussion (Misc queries) | |||
UserForm Help | Excel Discussion (Misc queries) |