|
PLEASE HELP: Index and Match function in Userform to populate fiel
Hi All,
How can I use Inded and Match function on VBA to populate certain form fields? For eg: I have a userform where Users input: Roll No(Unique to every student): Last Name : First Name : Subject1 : Ph No: What I want to do is: Once Users Input Their Roll No, I want to populate their Last Name, First Name, Subject1 and Ph No. Also the roll number might not be in descending order, Hence I want to use somethign like Index and Match function, So once a student Inputs the roll number, It will match the roll number and populate other fields respectively The button to launch the Form is on Sheet1 and All the student data is in Sheet2. Hope I made it clear Thanks in Advance |
PLEASE HELP: Index and Match function in Userform to populate fiel
thuis should help yuo to get started. This is the code behiind the form
My form has one command button and four text boxes Option Explicit Private Sub cmdFetchData_Click() Dim rw As Long rw = GetRow(txtRollNum.Text) If rw = 0 Then MsgBox "No Matching ID" Else FetchData rw End If End Sub Function GetRow(RollNum As String) As Long On Error Resume Next Dim source As Range Set source = Worksheets("sheet2").Range("A:A") GetRow = WorksheetFunction.Match(RollNum, source, False) On Error GoTo 0 End Function Sub FetchData(rw As Long) With Worksheets("Sheet2") txtFirstName = .Cells(rw, "A").Value txtLastName = .Cells(rw, "B").Value txtSubject = .Cells(rw, "C").Value txtPhNum = .Cells(rw, "D").Value End With End Sub "sam" wrote: Hi All, How can I use Inded and Match function on VBA to populate certain form fields? For eg: I have a userform where Users input: Roll No(Unique to every student): Last Name : First Name : Subject1 : Ph No: What I want to do is: Once Users Input Their Roll No, I want to populate their Last Name, First Name, Subject1 and Ph No. Also the roll number might not be in descending order, Hence I want to use somethign like Index and Match function, So once a student Inputs the roll number, It will match the roll number and populate other fields respectively The button to launch the Form is on Sheet1 and All the student data is in Sheet2. Hope I made it clear Thanks in Advance |
PLEASE HELP: Index and Match function in Userform to populatefiel
On 16 Sep, 16:11, sam wrote:
Hi All, How can I use Inded and Match function on VBA to populate certain form fields? For eg: I have a userform where Users input: Roll No(Unique to every student): Last Name : First Name : Subject1 : Ph No: What I want to do is: Once Users Input Their Roll No, I want to populate their Last Name, First Name, Subject1 and Ph No. Also the roll number might not be in descending order, Hence I want to use somethign like Index and Match function, So once a student Inputs the roll number, It will match the roll number and populate other fields respectively The button to launch the Form is on Sheet1 and All the student data is in Sheet2. Hope I made it clear Thanks in Advance Use the Vlookup worksheet function. If the user enters their roll number into TextBox1 and presses enter then Excel will execute this code to fill TextBox2 with the corresponding value in column B on Sheet2 (assuming all the roll numbers are in column A on Sheet2) - something like this: Private Sub TextBox1_AfterUpdate() Me.TextBox2.Value = Application.WorksheetFunction.VLookup( _ Me.TextBox1.Value, ThisWorkbook.Sheets("Sheet2").Columns ("A:B"), 2, False) End Sub Copy this pattern to fill your other text boxes from the other columns with the other data. Chrisso |
PLEASE HELP: Index and Match function in Userform to populate fiel
Dim res as variant
with worksheets("sheet2") 'I'd change the name to something meaningful 'look for a match using a string res = application.match(me.textbox1.value,.range("A:A"), 0) if iserror(res) then 'look for a match using a real number res = application.match(clng(me.textbox1.value),.range(" A:a"),0) end if if iserror(res) then 'no match between textbox1 and column A of Sheet2 'what should happen beep msgbox "Invalid entry" exit sub '??? end if me.textbox2.value = .range("a:a")(res).offset(0,1).value 'column b me.textbox3.value = .range("a:a")(res).offset(0,5).value 'column F End with (Untested, uncompiled. Watch for typos.) sam wrote: Hi All, How can I use Inded and Match function on VBA to populate certain form fields? For eg: I have a userform where Users input: Roll No(Unique to every student): Last Name : First Name : Subject1 : Ph No: What I want to do is: Once Users Input Their Roll No, I want to populate their Last Name, First Name, Subject1 and Ph No. Also the roll number might not be in descending order, Hence I want to use somethign like Index and Match function, So once a student Inputs the roll number, It will match the roll number and populate other fields respectively The button to launch the Form is on Sheet1 and All the student data is in Sheet2. Hope I made it clear Thanks in Advance -- Dave Peterson |
PLEASE HELP: Index and Match function in Userform to populate fiel
Hi Sam,
i would just use Find Method. Paste code behind your form & adjust as needed. I am assuming you have a button on form to call procedure that student presses to search for RollNo? And textboxes are still named TextBox1, TextBox2 etc etc? Hope useful Sub GetStudentData() Dim WS2 As Worksheet Dim Search As String Dim Foundcell As Range Set WS2 = Worksheets("Sheet2") '<< change name as required 'searching for rollno Search = Me.TextBox1.Text Set Foundcell = WS2.Columns(1).Find(Search, _ LookIn:=xlValues, _ LookAt:=xlWhole) If Foundcell Is Nothing = False Then 'found search value For na = 2 To 5 Me.Controls("TextBox" & na).Text = _ Foundcell.Offset(0, na - 1).Value Next na Else 'did not find search value 'tell user msg = MsgBox(Search & " Not Found!", 16, "Search") End If End Sub -- jb "sam" wrote: Hi All, How can I use Inded and Match function on VBA to populate certain form fields? For eg: I have a userform where Users input: Roll No(Unique to every student): Last Name : First Name : Subject1 : Ph No: What I want to do is: Once Users Input Their Roll No, I want to populate their Last Name, First Name, Subject1 and Ph No. Also the roll number might not be in descending order, Hence I want to use somethign like Index and Match function, So once a student Inputs the roll number, It will match the roll number and populate other fields respectively The button to launch the Form is on Sheet1 and All the student data is in Sheet2. Hope I made it clear Thanks in Advance |
PLEASE HELP: Index and Match function in Userform to populate
Thanks a lot for you help Patrick,
One more thing I wanted in my form was a way to capture filter values from sheet2 into a Dropdown list in the user form. Is there a way to do this? For eg, I have a column "Tasks" on sheet2 which have values 1,2,3,4,5,6 and each student can have multiple "Tasks" such as.. John: 1,3 Jill: 1,5 jack: 3,5,6 Bill: 2,4,6 and so on... I have this column set as a filter(Behaves like a dropdown on excel sheet), so John will have a Dropdown Filter with values 1 and 3 in "Tasks" column So basically, I want these filter values to be displayed on the form as dropdown menu with 1 and 3 displayed for John. Hope I made it clear Thanks in advance "Patrick Molloy" wrote: thuis should help yuo to get started. This is the code behiind the form My form has one command button and four text boxes Option Explicit Private Sub cmdFetchData_Click() Dim rw As Long rw = GetRow(txtRollNum.Text) If rw = 0 Then MsgBox "No Matching ID" Else FetchData rw End If End Sub Function GetRow(RollNum As String) As Long On Error Resume Next Dim source As Range Set source = Worksheets("sheet2").Range("A:A") GetRow = WorksheetFunction.Match(RollNum, source, False) On Error GoTo 0 End Function Sub FetchData(rw As Long) With Worksheets("Sheet2") txtFirstName = .Cells(rw, "A").Value txtLastName = .Cells(rw, "B").Value txtSubject = .Cells(rw, "C").Value txtPhNum = .Cells(rw, "D").Value End With End Sub "sam" wrote: Hi All, How can I use Inded and Match function on VBA to populate certain form fields? For eg: I have a userform where Users input: Roll No(Unique to every student): Last Name : First Name : Subject1 : Ph No: What I want to do is: Once Users Input Their Roll No, I want to populate their Last Name, First Name, Subject1 and Ph No. Also the roll number might not be in descending order, Hence I want to use somethign like Index and Match function, So once a student Inputs the roll number, It will match the roll number and populate other fields respectively The button to launch the Form is on Sheet1 and All the student data is in Sheet2. Hope I made it clear Thanks in Advance |
PLEASE HELP: Index and Match function in Userform to populate
Thanks a lot for you help Dave,
One more thing I wanted in my form was a way to capture filter values from sheet2 into a Dropdown list in the user form. Is there a way to do this? For eg, I have a column "Tasks" on sheet2 which have values 1,2,3,4,5,6 and each student can have multiple "Tasks" such as.. John: 1,3 Jill: 1,5 jack: 3,5,6 Bill: 2,4,6 and so on... I have this column set as a filter(Behaves like a dropdown on excel sheet), so John will have a Dropdown Filter with values 1 and 3 in "Tasks" column So basically, I want these filter values to be displayed on the form as dropdown menu with 1 and 3 displayed for John. Hope I made it clear Thanks in advance "Dave Peterson" wrote: Dim res as variant with worksheets("sheet2") 'I'd change the name to something meaningful 'look for a match using a string res = application.match(me.textbox1.value,.range("A:A"), 0) if iserror(res) then 'look for a match using a real number res = application.match(clng(me.textbox1.value),.range(" A:a"),0) end if if iserror(res) then 'no match between textbox1 and column A of Sheet2 'what should happen beep msgbox "Invalid entry" exit sub '??? end if me.textbox2.value = .range("a:a")(res).offset(0,1).value 'column b me.textbox3.value = .range("a:a")(res).offset(0,5).value 'column F End with (Untested, uncompiled. Watch for typos.) sam wrote: Hi All, How can I use Inded and Match function on VBA to populate certain form fields? For eg: I have a userform where Users input: Roll No(Unique to every student): Last Name : First Name : Subject1 : Ph No: What I want to do is: Once Users Input Their Roll No, I want to populate their Last Name, First Name, Subject1 and Ph No. Also the roll number might not be in descending order, Hence I want to use somethign like Index and Match function, So once a student Inputs the roll number, It will match the roll number and populate other fields respectively The button to launch the Form is on Sheet1 and All the student data is in Sheet2. Hope I made it clear Thanks in Advance -- Dave Peterson |
PLEASE HELP: Index and Match function in Userform to populate
Once you find the cell that holds the name, you can use VBA's split command (if
you're using xl2k or higher) to parse that data into an array. Then use that array as the combobox's .list. Dim myStr As String Dim myArr As Variant myStr = "1,2,3" 'how ever you find that cell would go here myArr = Split(myStr, ",") With Me.ComboBox1 .Clear .List = myArr End With sam wrote: Thanks a lot for you help Dave, One more thing I wanted in my form was a way to capture filter values from sheet2 into a Dropdown list in the user form. Is there a way to do this? For eg, I have a column "Tasks" on sheet2 which have values 1,2,3,4,5,6 and each student can have multiple "Tasks" such as.. John: 1,3 Jill: 1,5 jack: 3,5,6 Bill: 2,4,6 and so on... I have this column set as a filter(Behaves like a dropdown on excel sheet), so John will have a Dropdown Filter with values 1 and 3 in "Tasks" column So basically, I want these filter values to be displayed on the form as dropdown menu with 1 and 3 displayed for John. Hope I made it clear Thanks in advance "Dave Peterson" wrote: Dim res as variant with worksheets("sheet2") 'I'd change the name to something meaningful 'look for a match using a string res = application.match(me.textbox1.value,.range("A:A"), 0) if iserror(res) then 'look for a match using a real number res = application.match(clng(me.textbox1.value),.range(" A:a"),0) end if if iserror(res) then 'no match between textbox1 and column A of Sheet2 'what should happen beep msgbox "Invalid entry" exit sub '??? end if me.textbox2.value = .range("a:a")(res).offset(0,1).value 'column b me.textbox3.value = .range("a:a")(res).offset(0,5).value 'column F End with (Untested, uncompiled. Watch for typos.) sam wrote: Hi All, How can I use Inded and Match function on VBA to populate certain form fields? For eg: I have a userform where Users input: Roll No(Unique to every student): Last Name : First Name : Subject1 : Ph No: What I want to do is: Once Users Input Their Roll No, I want to populate their Last Name, First Name, Subject1 and Ph No. Also the roll number might not be in descending order, Hence I want to use somethign like Index and Match function, So once a student Inputs the roll number, It will match the roll number and populate other fields respectively The button to launch the Form is on Sheet1 and All the student data is in Sheet2. Hope I made it clear Thanks in Advance -- Dave Peterson -- Dave Peterson |
PLEASE HELP: Index and Match function in Userform to populate
Thanks for your help dave,
I have a lot of students and Taks in list, so with this approach I will have to input every tasks individually? or am I missing something here? Please help Thanks in advance "Dave Peterson" wrote: Once you find the cell that holds the name, you can use VBA's split command (if you're using xl2k or higher) to parse that data into an array. Then use that array as the combobox's .list. Dim myStr As String Dim myArr As Variant myStr = "1,2,3" 'how ever you find that cell would go here myArr = Split(myStr, ",") With Me.ComboBox1 .Clear .List = myArr End With sam wrote: Thanks a lot for you help Dave, One more thing I wanted in my form was a way to capture filter values from sheet2 into a Dropdown list in the user form. Is there a way to do this? For eg, I have a column "Tasks" on sheet2 which have values 1,2,3,4,5,6 and each student can have multiple "Tasks" such as.. John: 1,3 Jill: 1,5 jack: 3,5,6 Bill: 2,4,6 and so on... I have this column set as a filter(Behaves like a dropdown on excel sheet), so John will have a Dropdown Filter with values 1 and 3 in "Tasks" column So basically, I want these filter values to be displayed on the form as dropdown menu with 1 and 3 displayed for John. Hope I made it clear Thanks in advance "Dave Peterson" wrote: Dim res as variant with worksheets("sheet2") 'I'd change the name to something meaningful 'look for a match using a string res = application.match(me.textbox1.value,.range("A:A"), 0) if iserror(res) then 'look for a match using a real number res = application.match(clng(me.textbox1.value),.range(" A:a"),0) end if if iserror(res) then 'no match between textbox1 and column A of Sheet2 'what should happen beep msgbox "Invalid entry" exit sub '??? end if me.textbox2.value = .range("a:a")(res).offset(0,1).value 'column b me.textbox3.value = .range("a:a")(res).offset(0,5).value 'column F End with (Untested, uncompiled. Watch for typos.) sam wrote: Hi All, How can I use Inded and Match function on VBA to populate certain form fields? For eg: I have a userform where Users input: Roll No(Unique to every student): Last Name : First Name : Subject1 : Ph No: What I want to do is: Once Users Input Their Roll No, I want to populate their Last Name, First Name, Subject1 and Ph No. Also the roll number might not be in descending order, Hence I want to use somethign like Index and Match function, So once a student Inputs the roll number, It will match the roll number and populate other fields respectively The button to launch the Form is on Sheet1 and All the student data is in Sheet2. Hope I made it clear Thanks in Advance -- Dave Peterson -- Dave Peterson |
PLEASE HELP: Index and Match function in Userform to populate
Hey Dave, to make it clear. Here is and eg of how the data looks on sheet
Tasks Roll_No 3 11 5 11 4 12 6 12 2 13 5 13 3 14 4 14 1 15 2 15 1 16 4 16 So lets say you select "12" from Roll_No column filter then it will display Tasks Roll_No(filter) 4 12 6 12 So I want this 4 and 6 to be displayed in the dropdown list on userform too Is there a way to capture filter values(4 and 6) from a sheet to a userfrom. So when I input "12" on userform textbox I se 4 and 6 for my Tasks Dropdown on the userform. Hope I made it clear "Dave Peterson" wrote: Once you find the cell that holds the name, you can use VBA's split command (if you're using xl2k or higher) to parse that data into an array. Then use that array as the combobox's .list. Dim myStr As String Dim myArr As Variant myStr = "1,2,3" 'how ever you find that cell would go here myArr = Split(myStr, ",") With Me.ComboBox1 .Clear .List = myArr End With sam wrote: Thanks a lot for you help Dave, One more thing I wanted in my form was a way to capture filter values from sheet2 into a Dropdown list in the user form. Is there a way to do this? For eg, I have a column "Tasks" on sheet2 which have values 1,2,3,4,5,6 and each student can have multiple "Tasks" such as.. John: 1,3 Jill: 1,5 jack: 3,5,6 Bill: 2,4,6 and so on... I have this column set as a filter(Behaves like a dropdown on excel sheet), so John will have a Dropdown Filter with values 1 and 3 in "Tasks" column So basically, I want these filter values to be displayed on the form as dropdown menu with 1 and 3 displayed for John. Hope I made it clear Thanks in advance "Dave Peterson" wrote: Dim res as variant with worksheets("sheet2") 'I'd change the name to something meaningful 'look for a match using a string res = application.match(me.textbox1.value,.range("A:A"), 0) if iserror(res) then 'look for a match using a real number res = application.match(clng(me.textbox1.value),.range(" A:a"),0) end if if iserror(res) then 'no match between textbox1 and column A of Sheet2 'what should happen beep msgbox "Invalid entry" exit sub '??? end if me.textbox2.value = .range("a:a")(res).offset(0,1).value 'column b me.textbox3.value = .range("a:a")(res).offset(0,5).value 'column F End with (Untested, uncompiled. Watch for typos.) sam wrote: Hi All, How can I use Inded and Match function on VBA to populate certain form fields? For eg: I have a userform where Users input: Roll No(Unique to every student): Last Name : First Name : Subject1 : Ph No: What I want to do is: Once Users Input Their Roll No, I want to populate their Last Name, First Name, Subject1 and Ph No. Also the roll number might not be in descending order, Hence I want to use somethign like Index and Match function, So once a student Inputs the roll number, It will match the roll number and populate other fields respectively The button to launch the Form is on Sheet1 and All the student data is in Sheet2. Hope I made it clear Thanks in Advance -- Dave Peterson -- Dave Peterson |
PLEASE HELP: Index and Match function in Userform to populate
Nope. I didn't know how you were getting that string, I thought it was two
columns. But that was wrong. sam wrote: Thanks for your help dave, I have a lot of students and Taks in list, so with this approach I will have to input every tasks individually? or am I missing something here? Please help Thanks in advance "Dave Peterson" wrote: Once you find the cell that holds the name, you can use VBA's split command (if you're using xl2k or higher) to parse that data into an array. Then use that array as the combobox's .list. Dim myStr As String Dim myArr As Variant myStr = "1,2,3" 'how ever you find that cell would go here myArr = Split(myStr, ",") With Me.ComboBox1 .Clear .List = myArr End With sam wrote: Thanks a lot for you help Dave, One more thing I wanted in my form was a way to capture filter values from sheet2 into a Dropdown list in the user form. Is there a way to do this? For eg, I have a column "Tasks" on sheet2 which have values 1,2,3,4,5,6 and each student can have multiple "Tasks" such as.. John: 1,3 Jill: 1,5 jack: 3,5,6 Bill: 2,4,6 and so on... I have this column set as a filter(Behaves like a dropdown on excel sheet), so John will have a Dropdown Filter with values 1 and 3 in "Tasks" column So basically, I want these filter values to be displayed on the form as dropdown menu with 1 and 3 displayed for John. Hope I made it clear Thanks in advance "Dave Peterson" wrote: Dim res as variant with worksheets("sheet2") 'I'd change the name to something meaningful 'look for a match using a string res = application.match(me.textbox1.value,.range("A:A"), 0) if iserror(res) then 'look for a match using a real number res = application.match(clng(me.textbox1.value),.range(" A:a"),0) end if if iserror(res) then 'no match between textbox1 and column A of Sheet2 'what should happen beep msgbox "Invalid entry" exit sub '??? end if me.textbox2.value = .range("a:a")(res).offset(0,1).value 'column b me.textbox3.value = .range("a:a")(res).offset(0,5).value 'column F End with (Untested, uncompiled. Watch for typos.) sam wrote: Hi All, How can I use Inded and Match function on VBA to populate certain form fields? For eg: I have a userform where Users input: Roll No(Unique to every student): Last Name : First Name : Subject1 : Ph No: What I want to do is: Once Users Input Their Roll No, I want to populate their Last Name, First Name, Subject1 and Ph No. Also the roll number might not be in descending order, Hence I want to use somethign like Index and Match function, So once a student Inputs the roll number, It will match the roll number and populate other fields respectively The button to launch the Form is on Sheet1 and All the student data is in Sheet2. Hope I made it clear Thanks in Advance -- Dave Peterson -- Dave Peterson -- Dave Peterson |
PLEASE HELP: Index and Match function in Userform to populate
You're not really filtering the worksheet based on the roll number, right?
If you're not... You can loop through the range. Dim myRng as range dim myCell as range With worksheets("Sheet2") set myrng = .range("B2",.cells(.rows.count,"B").end(xlup)) end with me.combobox1.clear for each mycell in myrng.cells if mycell.value = clng(me.textbox1.value) then me.combobox1.additem mycell.offset(0,-1).value end if next mycell sam wrote: Hey Dave, to make it clear. Here is and eg of how the data looks on sheet Tasks Roll_No 3 11 5 11 4 12 6 12 2 13 5 13 3 14 4 14 1 15 2 15 1 16 4 16 So lets say you select "12" from Roll_No column filter then it will display Tasks Roll_No(filter) 4 12 6 12 So I want this 4 and 6 to be displayed in the dropdown list on userform too Is there a way to capture filter values(4 and 6) from a sheet to a userfrom. So when I input "12" on userform textbox I se 4 and 6 for my Tasks Dropdown on the userform. Hope I made it clear "Dave Peterson" wrote: Once you find the cell that holds the name, you can use VBA's split command (if you're using xl2k or higher) to parse that data into an array. Then use that array as the combobox's .list. Dim myStr As String Dim myArr As Variant myStr = "1,2,3" 'how ever you find that cell would go here myArr = Split(myStr, ",") With Me.ComboBox1 .Clear .List = myArr End With sam wrote: Thanks a lot for you help Dave, One more thing I wanted in my form was a way to capture filter values from sheet2 into a Dropdown list in the user form. Is there a way to do this? For eg, I have a column "Tasks" on sheet2 which have values 1,2,3,4,5,6 and each student can have multiple "Tasks" such as.. John: 1,3 Jill: 1,5 jack: 3,5,6 Bill: 2,4,6 and so on... I have this column set as a filter(Behaves like a dropdown on excel sheet), so John will have a Dropdown Filter with values 1 and 3 in "Tasks" column So basically, I want these filter values to be displayed on the form as dropdown menu with 1 and 3 displayed for John. Hope I made it clear Thanks in advance "Dave Peterson" wrote: Dim res as variant with worksheets("sheet2") 'I'd change the name to something meaningful 'look for a match using a string res = application.match(me.textbox1.value,.range("A:A"), 0) if iserror(res) then 'look for a match using a real number res = application.match(clng(me.textbox1.value),.range(" A:a"),0) end if if iserror(res) then 'no match between textbox1 and column A of Sheet2 'what should happen beep msgbox "Invalid entry" exit sub '??? end if me.textbox2.value = .range("a:a")(res).offset(0,1).value 'column b me.textbox3.value = .range("a:a")(res).offset(0,5).value 'column F End with (Untested, uncompiled. Watch for typos.) sam wrote: Hi All, How can I use Inded and Match function on VBA to populate certain form fields? For eg: I have a userform where Users input: Roll No(Unique to every student): Last Name : First Name : Subject1 : Ph No: What I want to do is: Once Users Input Their Roll No, I want to populate their Last Name, First Name, Subject1 and Ph No. Also the roll number might not be in descending order, Hence I want to use somethign like Index and Match function, So once a student Inputs the roll number, It will match the roll number and populate other fields respectively The button to launch the Form is on Sheet1 and All the student data is in Sheet2. Hope I made it clear Thanks in Advance -- Dave Peterson -- Dave Peterson -- Dave Peterson |
PLEASE HELP: Index and Match function in Userform to populate
This worked out GREAT!! THANK YOU!
"Dave Peterson" wrote: You're not really filtering the worksheet based on the roll number, right? If you're not... You can loop through the range. Dim myRng as range dim myCell as range With worksheets("Sheet2") set myrng = .range("B2",.cells(.rows.count,"B").end(xlup)) end with me.combobox1.clear for each mycell in myrng.cells if mycell.value = clng(me.textbox1.value) then me.combobox1.additem mycell.offset(0,-1).value end if next mycell sam wrote: Hey Dave, to make it clear. Here is and eg of how the data looks on sheet Tasks Roll_No 3 11 5 11 4 12 6 12 2 13 5 13 3 14 4 14 1 15 2 15 1 16 4 16 So lets say you select "12" from Roll_No column filter then it will display Tasks Roll_No(filter) 4 12 6 12 So I want this 4 and 6 to be displayed in the dropdown list on userform too Is there a way to capture filter values(4 and 6) from a sheet to a userfrom. So when I input "12" on userform textbox I se 4 and 6 for my Tasks Dropdown on the userform. Hope I made it clear "Dave Peterson" wrote: Once you find the cell that holds the name, you can use VBA's split command (if you're using xl2k or higher) to parse that data into an array. Then use that array as the combobox's .list. Dim myStr As String Dim myArr As Variant myStr = "1,2,3" 'how ever you find that cell would go here myArr = Split(myStr, ",") With Me.ComboBox1 .Clear .List = myArr End With sam wrote: Thanks a lot for you help Dave, One more thing I wanted in my form was a way to capture filter values from sheet2 into a Dropdown list in the user form. Is there a way to do this? For eg, I have a column "Tasks" on sheet2 which have values 1,2,3,4,5,6 and each student can have multiple "Tasks" such as.. John: 1,3 Jill: 1,5 jack: 3,5,6 Bill: 2,4,6 and so on... I have this column set as a filter(Behaves like a dropdown on excel sheet), so John will have a Dropdown Filter with values 1 and 3 in "Tasks" column So basically, I want these filter values to be displayed on the form as dropdown menu with 1 and 3 displayed for John. Hope I made it clear Thanks in advance "Dave Peterson" wrote: Dim res as variant with worksheets("sheet2") 'I'd change the name to something meaningful 'look for a match using a string res = application.match(me.textbox1.value,.range("A:A"), 0) if iserror(res) then 'look for a match using a real number res = application.match(clng(me.textbox1.value),.range(" A:a"),0) end if if iserror(res) then 'no match between textbox1 and column A of Sheet2 'what should happen beep msgbox "Invalid entry" exit sub '??? end if me.textbox2.value = .range("a:a")(res).offset(0,1).value 'column b me.textbox3.value = .range("a:a")(res).offset(0,5).value 'column F End with (Untested, uncompiled. Watch for typos.) sam wrote: Hi All, How can I use Inded and Match function on VBA to populate certain form fields? For eg: I have a userform where Users input: Roll No(Unique to every student): Last Name : First Name : Subject1 : Ph No: What I want to do is: Once Users Input Their Roll No, I want to populate their Last Name, First Name, Subject1 and Ph No. Also the roll number might not be in descending order, Hence I want to use somethign like Index and Match function, So once a student Inputs the roll number, It will match the roll number and populate other fields respectively The button to launch the Form is on Sheet1 and All the student data is in Sheet2. Hope I made it clear Thanks in Advance -- Dave Peterson -- Dave Peterson -- Dave Peterson |
PLEASE HELP: Index and Match function in Userform to populate
Hey Dave, Thanks a lot for all you help in getting this to work.
I was working on this and I came across an issue where students input values in "Roll_No" field that are not present in the sheet. Is there a way where I can disable the "Tasks" dropdown if they input an invalid "Roll_No" or a "Roll_No" that is not present in the sheet? So basically enable the "Tasks" dropdown on form only if the "Roll_No" they input is from the list in the sheet. Thanks in advance "Dave Peterson" wrote: You're not really filtering the worksheet based on the roll number, right? If you're not... You can loop through the range. Dim myRng as range dim myCell as range With worksheets("Sheet2") set myrng = .range("B2",.cells(.rows.count,"B").end(xlup)) end with me.combobox1.clear for each mycell in myrng.cells if mycell.value = clng(me.textbox1.value) then me.combobox1.additem mycell.offset(0,-1).value end if next mycell sam wrote: Hey Dave, to make it clear. Here is and eg of how the data looks on sheet Tasks Roll_No 3 11 5 11 4 12 6 12 2 13 5 13 3 14 4 14 1 15 2 15 1 16 4 16 So lets say you select "12" from Roll_No column filter then it will display Tasks Roll_No(filter) 4 12 6 12 So I want this 4 and 6 to be displayed in the dropdown list on userform too Is there a way to capture filter values(4 and 6) from a sheet to a userfrom. So when I input "12" on userform textbox I se 4 and 6 for my Tasks Dropdown on the userform. Hope I made it clear "Dave Peterson" wrote: Once you find the cell that holds the name, you can use VBA's split command (if you're using xl2k or higher) to parse that data into an array. Then use that array as the combobox's .list. Dim myStr As String Dim myArr As Variant myStr = "1,2,3" 'how ever you find that cell would go here myArr = Split(myStr, ",") With Me.ComboBox1 .Clear .List = myArr End With sam wrote: Thanks a lot for you help Dave, One more thing I wanted in my form was a way to capture filter values from sheet2 into a Dropdown list in the user form. Is there a way to do this? For eg, I have a column "Tasks" on sheet2 which have values 1,2,3,4,5,6 and each student can have multiple "Tasks" such as.. John: 1,3 Jill: 1,5 jack: 3,5,6 Bill: 2,4,6 and so on... I have this column set as a filter(Behaves like a dropdown on excel sheet), so John will have a Dropdown Filter with values 1 and 3 in "Tasks" column So basically, I want these filter values to be displayed on the form as dropdown menu with 1 and 3 displayed for John. Hope I made it clear Thanks in advance "Dave Peterson" wrote: Dim res as variant with worksheets("sheet2") 'I'd change the name to something meaningful 'look for a match using a string res = application.match(me.textbox1.value,.range("A:A"), 0) if iserror(res) then 'look for a match using a real number res = application.match(clng(me.textbox1.value),.range(" A:a"),0) end if if iserror(res) then 'no match between textbox1 and column A of Sheet2 'what should happen beep msgbox "Invalid entry" exit sub '??? end if me.textbox2.value = .range("a:a")(res).offset(0,1).value 'column b me.textbox3.value = .range("a:a")(res).offset(0,5).value 'column F End with (Untested, uncompiled. Watch for typos.) sam wrote: Hi All, How can I use Inded and Match function on VBA to populate certain form fields? For eg: I have a userform where Users input: Roll No(Unique to every student): Last Name : First Name : Subject1 : Ph No: What I want to do is: Once Users Input Their Roll No, I want to populate their Last Name, First Name, Subject1 and Ph No. Also the roll number might not be in descending order, Hence I want to use somethign like Index and Match function, So once a student Inputs the roll number, It will match the roll number and populate other fields respectively The button to launch the Form is on Sheet1 and All the student data is in Sheet2. Hope I made it clear Thanks in Advance -- Dave Peterson -- Dave Peterson -- Dave Peterson |
PLEASE HELP: Index and Match function in Userform to populate
After this section of your code:
me.combobox1.clear for each mycell in myrng.cells if mycell.value = clng(me.textbox1.value) then me.combobox1.additem mycell.offset(0,-1).value end if next mycell if me.combobox1.listcount = 0 then 'nothing found me.combobox1.enabled = false else me.combobox1.enabled = true end if =================================== You could even check beforehand: With worksheets("Sheet2") set myrng = .range("B2",.cells(.rows.count,"B").end(xlup)) end with me.combobox1.clear 'no matter what if application.countif(myrng, me.textbox1.value) = 0 then me.combobox1.enabled = false else me.combobox1.enabled = true 'just in case it was ever false for each mycell in myrng.cells if mycell.value = clng(me.textbox1.value) then me.combobox1.additem mycell.offset(0,-1).value end if next mycell end if sam wrote: Hey Dave, Thanks a lot for all you help in getting this to work. I was working on this and I came across an issue where students input values in "Roll_No" field that are not present in the sheet. Is there a way where I can disable the "Tasks" dropdown if they input an invalid "Roll_No" or a "Roll_No" that is not present in the sheet? So basically enable the "Tasks" dropdown on form only if the "Roll_No" they input is from the list in the sheet. Thanks in advance "Dave Peterson" wrote: You're not really filtering the worksheet based on the roll number, right? If you're not... You can loop through the range. Dim myRng as range dim myCell as range With worksheets("Sheet2") set myrng = .range("B2",.cells(.rows.count,"B").end(xlup)) end with me.combobox1.clear for each mycell in myrng.cells if mycell.value = clng(me.textbox1.value) then me.combobox1.additem mycell.offset(0,-1).value end if next mycell sam wrote: Hey Dave, to make it clear. Here is and eg of how the data looks on sheet Tasks Roll_No 3 11 5 11 4 12 6 12 2 13 5 13 3 14 4 14 1 15 2 15 1 16 4 16 So lets say you select "12" from Roll_No column filter then it will display Tasks Roll_No(filter) 4 12 6 12 So I want this 4 and 6 to be displayed in the dropdown list on userform too Is there a way to capture filter values(4 and 6) from a sheet to a userfrom. So when I input "12" on userform textbox I se 4 and 6 for my Tasks Dropdown on the userform. Hope I made it clear "Dave Peterson" wrote: Once you find the cell that holds the name, you can use VBA's split command (if you're using xl2k or higher) to parse that data into an array. Then use that array as the combobox's .list. Dim myStr As String Dim myArr As Variant myStr = "1,2,3" 'how ever you find that cell would go here myArr = Split(myStr, ",") With Me.ComboBox1 .Clear .List = myArr End With sam wrote: Thanks a lot for you help Dave, One more thing I wanted in my form was a way to capture filter values from sheet2 into a Dropdown list in the user form. Is there a way to do this? For eg, I have a column "Tasks" on sheet2 which have values 1,2,3,4,5,6 and each student can have multiple "Tasks" such as.. John: 1,3 Jill: 1,5 jack: 3,5,6 Bill: 2,4,6 and so on... I have this column set as a filter(Behaves like a dropdown on excel sheet), so John will have a Dropdown Filter with values 1 and 3 in "Tasks" column So basically, I want these filter values to be displayed on the form as dropdown menu with 1 and 3 displayed for John. Hope I made it clear Thanks in advance "Dave Peterson" wrote: Dim res as variant with worksheets("sheet2") 'I'd change the name to something meaningful 'look for a match using a string res = application.match(me.textbox1.value,.range("A:A"), 0) if iserror(res) then 'look for a match using a real number res = application.match(clng(me.textbox1.value),.range(" A:a"),0) end if if iserror(res) then 'no match between textbox1 and column A of Sheet2 'what should happen beep msgbox "Invalid entry" exit sub '??? end if me.textbox2.value = .range("a:a")(res).offset(0,1).value 'column b me.textbox3.value = .range("a:a")(res).offset(0,5).value 'column F End with (Untested, uncompiled. Watch for typos.) sam wrote: Hi All, How can I use Inded and Match function on VBA to populate certain form fields? For eg: I have a userform where Users input: Roll No(Unique to every student): Last Name : First Name : Subject1 : Ph No: What I want to do is: Once Users Input Their Roll No, I want to populate their Last Name, First Name, Subject1 and Ph No. Also the roll number might not be in descending order, Hence I want to use somethign like Index and Match function, So once a student Inputs the roll number, It will match the roll number and populate other fields respectively The button to launch the Form is on Sheet1 and All the student data is in Sheet2. Hope I made it clear Thanks in Advance -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
PLEASE HELP: Index and Match function in Userform to populate
Before you go too far, as a user, I'd rather use a combobox to choose the id.
Then I don't have to do any typing. If you want to try: Create a new worksheet and name it "RollNumIDs". You can hide that sheet if you want. It's just gonna be used by the code to get a unique list of those id's (in a sorted order). Option Explicit Private Sub UserForm_Initialize() Dim TempWks As Worksheet Dim TableWks As Worksheet Dim RollNumRng As Range Set TableWks = Worksheets("Sheet1") 'sheet with the table Set TempWks = Worksheets("RollNumIDs") With TableWks Set RollNumRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)) End With TempWks.Cells.Clear 'clean up any existing data RollNumRng.AdvancedFilter _ Action:=xlFilterCopy, _ copytorange:=TempWks.Range("A1"), _ unique:=True With TempWks With .Columns(1) .Cells.Sort Key1:=.Columns(1), _ Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End With Me.ComboBox1.List = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Value End With End Sub Dave Peterson wrote: After this section of your code: me.combobox1.clear for each mycell in myrng.cells if mycell.value = clng(me.textbox1.value) then me.combobox1.additem mycell.offset(0,-1).value end if next mycell if me.combobox1.listcount = 0 then 'nothing found me.combobox1.enabled = false else me.combobox1.enabled = true end if =================================== You could even check beforehand: With worksheets("Sheet2") set myrng = .range("B2",.cells(.rows.count,"B").end(xlup)) end with me.combobox1.clear 'no matter what if application.countif(myrng, me.textbox1.value) = 0 then me.combobox1.enabled = false else me.combobox1.enabled = true 'just in case it was ever false for each mycell in myrng.cells if mycell.value = clng(me.textbox1.value) then me.combobox1.additem mycell.offset(0,-1).value end if next mycell end if sam wrote: Hey Dave, Thanks a lot for all you help in getting this to work. I was working on this and I came across an issue where students input values in "Roll_No" field that are not present in the sheet. Is there a way where I can disable the "Tasks" dropdown if they input an invalid "Roll_No" or a "Roll_No" that is not present in the sheet? So basically enable the "Tasks" dropdown on form only if the "Roll_No" they input is from the list in the sheet. Thanks in advance "Dave Peterson" wrote: You're not really filtering the worksheet based on the roll number, right? If you're not... You can loop through the range. Dim myRng as range dim myCell as range With worksheets("Sheet2") set myrng = .range("B2",.cells(.rows.count,"B").end(xlup)) end with me.combobox1.clear for each mycell in myrng.cells if mycell.value = clng(me.textbox1.value) then me.combobox1.additem mycell.offset(0,-1).value end if next mycell sam wrote: Hey Dave, to make it clear. Here is and eg of how the data looks on sheet Tasks Roll_No 3 11 5 11 4 12 6 12 2 13 5 13 3 14 4 14 1 15 2 15 1 16 4 16 So lets say you select "12" from Roll_No column filter then it will display Tasks Roll_No(filter) 4 12 6 12 So I want this 4 and 6 to be displayed in the dropdown list on userform too Is there a way to capture filter values(4 and 6) from a sheet to a userfrom. So when I input "12" on userform textbox I se 4 and 6 for my Tasks Dropdown on the userform. Hope I made it clear "Dave Peterson" wrote: Once you find the cell that holds the name, you can use VBA's split command (if you're using xl2k or higher) to parse that data into an array. Then use that array as the combobox's .list. Dim myStr As String Dim myArr As Variant myStr = "1,2,3" 'how ever you find that cell would go here myArr = Split(myStr, ",") With Me.ComboBox1 .Clear .List = myArr End With sam wrote: Thanks a lot for you help Dave, One more thing I wanted in my form was a way to capture filter values from sheet2 into a Dropdown list in the user form. Is there a way to do this? For eg, I have a column "Tasks" on sheet2 which have values 1,2,3,4,5,6 and each student can have multiple "Tasks" such as.. John: 1,3 Jill: 1,5 jack: 3,5,6 Bill: 2,4,6 and so on... I have this column set as a filter(Behaves like a dropdown on excel sheet), so John will have a Dropdown Filter with values 1 and 3 in "Tasks" column So basically, I want these filter values to be displayed on the form as dropdown menu with 1 and 3 displayed for John. Hope I made it clear Thanks in advance "Dave Peterson" wrote: Dim res as variant with worksheets("sheet2") 'I'd change the name to something meaningful 'look for a match using a string res = application.match(me.textbox1.value,.range("A:A"), 0) if iserror(res) then 'look for a match using a real number res = application.match(clng(me.textbox1.value),.range(" A:a"),0) end if if iserror(res) then 'no match between textbox1 and column A of Sheet2 'what should happen beep msgbox "Invalid entry" exit sub '??? end if me.textbox2.value = .range("a:a")(res).offset(0,1).value 'column b me.textbox3.value = .range("a:a")(res).offset(0,5).value 'column F End with (Untested, uncompiled. Watch for typos.) sam wrote: Hi All, How can I use Inded and Match function on VBA to populate certain form fields? For eg: I have a userform where Users input: Roll No(Unique to every student): Last Name : First Name : Subject1 : Ph No: What I want to do is: Once Users Input Their Roll No, I want to populate their Last Name, First Name, Subject1 and Ph No. Also the roll number might not be in descending order, Hence I want to use somethign like Index and Match function, So once a student Inputs the roll number, It will match the roll number and populate other fields respectively The button to launch the Form is on Sheet1 and All the student data is in Sheet2. Hope I made it clear Thanks in Advance -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
PLEASE HELP: Index and Match function in Userform to populate
Hey Dave, This is a good approach, But I have a lot of students and still
expecting more to come, So having a dropdown of abut 30-40 "Roll_No" would be too long and it will keep on growint.. But this approach is great! Thank you "Dave Peterson" wrote: Before you go too far, as a user, I'd rather use a combobox to choose the id. Then I don't have to do any typing. If you want to try: Create a new worksheet and name it "RollNumIDs". You can hide that sheet if you want. It's just gonna be used by the code to get a unique list of those id's (in a sorted order). Option Explicit Private Sub UserForm_Initialize() Dim TempWks As Worksheet Dim TableWks As Worksheet Dim RollNumRng As Range Set TableWks = Worksheets("Sheet1") 'sheet with the table Set TempWks = Worksheets("RollNumIDs") With TableWks Set RollNumRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)) End With TempWks.Cells.Clear 'clean up any existing data RollNumRng.AdvancedFilter _ Action:=xlFilterCopy, _ copytorange:=TempWks.Range("A1"), _ unique:=True With TempWks With .Columns(1) .Cells.Sort Key1:=.Columns(1), _ Order1:=xlAscending, Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End With Me.ComboBox1.List = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).Value End With End Sub Dave Peterson wrote: After this section of your code: me.combobox1.clear for each mycell in myrng.cells if mycell.value = clng(me.textbox1.value) then me.combobox1.additem mycell.offset(0,-1).value end if next mycell if me.combobox1.listcount = 0 then 'nothing found me.combobox1.enabled = false else me.combobox1.enabled = true end if =================================== You could even check beforehand: With worksheets("Sheet2") set myrng = .range("B2",.cells(.rows.count,"B").end(xlup)) end with me.combobox1.clear 'no matter what if application.countif(myrng, me.textbox1.value) = 0 then me.combobox1.enabled = false else me.combobox1.enabled = true 'just in case it was ever false for each mycell in myrng.cells if mycell.value = clng(me.textbox1.value) then me.combobox1.additem mycell.offset(0,-1).value end if next mycell end if sam wrote: Hey Dave, Thanks a lot for all you help in getting this to work. I was working on this and I came across an issue where students input values in "Roll_No" field that are not present in the sheet. Is there a way where I can disable the "Tasks" dropdown if they input an invalid "Roll_No" or a "Roll_No" that is not present in the sheet? So basically enable the "Tasks" dropdown on form only if the "Roll_No" they input is from the list in the sheet. Thanks in advance "Dave Peterson" wrote: You're not really filtering the worksheet based on the roll number, right? If you're not... You can loop through the range. Dim myRng as range dim myCell as range With worksheets("Sheet2") set myrng = .range("B2",.cells(.rows.count,"B").end(xlup)) end with me.combobox1.clear for each mycell in myrng.cells if mycell.value = clng(me.textbox1.value) then me.combobox1.additem mycell.offset(0,-1).value end if next mycell sam wrote: Hey Dave, to make it clear. Here is and eg of how the data looks on sheet Tasks Roll_No 3 11 5 11 4 12 6 12 2 13 5 13 3 14 4 14 1 15 2 15 1 16 4 16 So lets say you select "12" from Roll_No column filter then it will display Tasks Roll_No(filter) 4 12 6 12 So I want this 4 and 6 to be displayed in the dropdown list on userform too Is there a way to capture filter values(4 and 6) from a sheet to a userfrom. So when I input "12" on userform textbox I se 4 and 6 for my Tasks Dropdown on the userform. Hope I made it clear "Dave Peterson" wrote: Once you find the cell that holds the name, you can use VBA's split command (if you're using xl2k or higher) to parse that data into an array. Then use that array as the combobox's .list. Dim myStr As String Dim myArr As Variant myStr = "1,2,3" 'how ever you find that cell would go here myArr = Split(myStr, ",") With Me.ComboBox1 .Clear .List = myArr End With sam wrote: Thanks a lot for you help Dave, One more thing I wanted in my form was a way to capture filter values from sheet2 into a Dropdown list in the user form. Is there a way to do this? For eg, I have a column "Tasks" on sheet2 which have values 1,2,3,4,5,6 and each student can have multiple "Tasks" such as.. John: 1,3 Jill: 1,5 jack: 3,5,6 Bill: 2,4,6 and so on... I have this column set as a filter(Behaves like a dropdown on excel sheet), so John will have a Dropdown Filter with values 1 and 3 in "Tasks" column So basically, I want these filter values to be displayed on the form as dropdown menu with 1 and 3 displayed for John. Hope I made it clear Thanks in advance "Dave Peterson" wrote: Dim res as variant with worksheets("sheet2") 'I'd change the name to something meaningful 'look for a match using a string res = application.match(me.textbox1.value,.range("A:A"), 0) if iserror(res) then 'look for a match using a real number res = application.match(clng(me.textbox1.value),.range(" A:a"),0) end if if iserror(res) then 'no match between textbox1 and column A of Sheet2 'what should happen beep msgbox "Invalid entry" exit sub '??? end if me.textbox2.value = .range("a:a")(res).offset(0,1).value 'column b me.textbox3.value = .range("a:a")(res).offset(0,5).value 'column F End with (Untested, uncompiled. Watch for typos.) sam wrote: Hi All, How can I use Inded and Match function on VBA to populate certain form fields? For eg: I have a userform where Users input: Roll No(Unique to every student): Last Name : First Name : Subject1 : Ph No: What I want to do is: Once Users Input Their Roll No, I want to populate their Last Name, First Name, Subject1 and Ph No. Also the roll number might not be in descending order, Hence I want to use somethign like Index and Match function, So once a student Inputs the roll number, It will match the roll number and populate other fields respectively The button to launch the Form is on Sheet1 and All the student data is in Sheet2. Hope I made it clear Thanks in Advance -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
PLEASE HELP: Index and Match function in Userform to populate
Hey Dave, Thanks for this.. It worked out great!
I am trying to create a button Insert in the same form. What I want this button to do is: When I click Insert.. It Opens a new workbook, Inserts the current student details in first row(Roll_No, Task, ect.), SO basically Every time I hit this Insert button I want to populate current student detail(Roll_No, Task, ect.) in a new row. Is this possible? Thanks in advance "Dave Peterson" wrote: After this section of your code: me.combobox1.clear for each mycell in myrng.cells if mycell.value = clng(me.textbox1.value) then me.combobox1.additem mycell.offset(0,-1).value end if next mycell if me.combobox1.listcount = 0 then 'nothing found me.combobox1.enabled = false else me.combobox1.enabled = true end if =================================== You could even check beforehand: With worksheets("Sheet2") set myrng = .range("B2",.cells(.rows.count,"B").end(xlup)) end with me.combobox1.clear 'no matter what if application.countif(myrng, me.textbox1.value) = 0 then me.combobox1.enabled = false else me.combobox1.enabled = true 'just in case it was ever false for each mycell in myrng.cells if mycell.value = clng(me.textbox1.value) then me.combobox1.additem mycell.offset(0,-1).value end if next mycell end if sam wrote: Hey Dave, Thanks a lot for all you help in getting this to work. I was working on this and I came across an issue where students input values in "Roll_No" field that are not present in the sheet. Is there a way where I can disable the "Tasks" dropdown if they input an invalid "Roll_No" or a "Roll_No" that is not present in the sheet? So basically enable the "Tasks" dropdown on form only if the "Roll_No" they input is from the list in the sheet. Thanks in advance "Dave Peterson" wrote: You're not really filtering the worksheet based on the roll number, right? If you're not... You can loop through the range. Dim myRng as range dim myCell as range With worksheets("Sheet2") set myrng = .range("B2",.cells(.rows.count,"B").end(xlup)) end with me.combobox1.clear for each mycell in myrng.cells if mycell.value = clng(me.textbox1.value) then me.combobox1.additem mycell.offset(0,-1).value end if next mycell sam wrote: Hey Dave, to make it clear. Here is and eg of how the data looks on sheet Tasks Roll_No 3 11 5 11 4 12 6 12 2 13 5 13 3 14 4 14 1 15 2 15 1 16 4 16 So lets say you select "12" from Roll_No column filter then it will display Tasks Roll_No(filter) 4 12 6 12 So I want this 4 and 6 to be displayed in the dropdown list on userform too Is there a way to capture filter values(4 and 6) from a sheet to a userfrom. So when I input "12" on userform textbox I se 4 and 6 for my Tasks Dropdown on the userform. Hope I made it clear "Dave Peterson" wrote: Once you find the cell that holds the name, you can use VBA's split command (if you're using xl2k or higher) to parse that data into an array. Then use that array as the combobox's .list. Dim myStr As String Dim myArr As Variant myStr = "1,2,3" 'how ever you find that cell would go here myArr = Split(myStr, ",") With Me.ComboBox1 .Clear .List = myArr End With sam wrote: Thanks a lot for you help Dave, One more thing I wanted in my form was a way to capture filter values from sheet2 into a Dropdown list in the user form. Is there a way to do this? For eg, I have a column "Tasks" on sheet2 which have values 1,2,3,4,5,6 and each student can have multiple "Tasks" such as.. John: 1,3 Jill: 1,5 jack: 3,5,6 Bill: 2,4,6 and so on... I have this column set as a filter(Behaves like a dropdown on excel sheet), so John will have a Dropdown Filter with values 1 and 3 in "Tasks" column So basically, I want these filter values to be displayed on the form as dropdown menu with 1 and 3 displayed for John. Hope I made it clear Thanks in advance "Dave Peterson" wrote: Dim res as variant with worksheets("sheet2") 'I'd change the name to something meaningful 'look for a match using a string res = application.match(me.textbox1.value,.range("A:A"), 0) if iserror(res) then 'look for a match using a real number res = application.match(clng(me.textbox1.value),.range(" A:a"),0) end if if iserror(res) then 'no match between textbox1 and column A of Sheet2 'what should happen beep msgbox "Invalid entry" exit sub '??? end if me.textbox2.value = .range("a:a")(res).offset(0,1).value 'column b me.textbox3.value = .range("a:a")(res).offset(0,5).value 'column F End with (Untested, uncompiled. Watch for typos.) sam wrote: Hi All, How can I use Inded and Match function on VBA to populate certain form fields? For eg: I have a userform where Users input: Roll No(Unique to every student): Last Name : First Name : Subject1 : Ph No: What I want to do is: Once Users Input Their Roll No, I want to populate their Last Name, First Name, Subject1 and Ph No. Also the roll number might not be in descending order, Hence I want to use somethign like Index and Match function, So once a student Inputs the roll number, It will match the roll number and populate other fields respectively The button to launch the Form is on Sheet1 and All the student data is in Sheet2. Hope I made it clear Thanks in Advance -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
PLEASE HELP: Index and Match function in Userform to populate
dim newWks as worksheet
'new workbook with a single sheet set newwks = workbooks.add(1).worksheets(1) with newwks .range("A1").value = me.textbox1.value 'roll_no .range("B1").value = me.whatever..... End with with newwks.parent 'the new workbook .saveas _ filename:="C:\newfilename_" & format(now,"yyyymmdd_hhmmss") & ".xls", _ fileformat:=xlworkbooknormal .close savechanges:=false end with sam wrote: Hey Dave, Thanks for this.. It worked out great! I am trying to create a button Insert in the same form. What I want this button to do is: When I click Insert.. It Opens a new workbook, Inserts the current student details in first row(Roll_No, Task, ect.), SO basically Every time I hit this Insert button I want to populate current student detail(Roll_No, Task, ect.) in a new row. Is this possible? Thanks in advance "Dave Peterson" wrote: After this section of your code: me.combobox1.clear for each mycell in myrng.cells if mycell.value = clng(me.textbox1.value) then me.combobox1.additem mycell.offset(0,-1).value end if next mycell if me.combobox1.listcount = 0 then 'nothing found me.combobox1.enabled = false else me.combobox1.enabled = true end if =================================== You could even check beforehand: With worksheets("Sheet2") set myrng = .range("B2",.cells(.rows.count,"B").end(xlup)) end with me.combobox1.clear 'no matter what if application.countif(myrng, me.textbox1.value) = 0 then me.combobox1.enabled = false else me.combobox1.enabled = true 'just in case it was ever false for each mycell in myrng.cells if mycell.value = clng(me.textbox1.value) then me.combobox1.additem mycell.offset(0,-1).value end if next mycell end if sam wrote: Hey Dave, Thanks a lot for all you help in getting this to work. I was working on this and I came across an issue where students input values in "Roll_No" field that are not present in the sheet. Is there a way where I can disable the "Tasks" dropdown if they input an invalid "Roll_No" or a "Roll_No" that is not present in the sheet? So basically enable the "Tasks" dropdown on form only if the "Roll_No" they input is from the list in the sheet. Thanks in advance "Dave Peterson" wrote: You're not really filtering the worksheet based on the roll number, right? If you're not... You can loop through the range. Dim myRng as range dim myCell as range With worksheets("Sheet2") set myrng = .range("B2",.cells(.rows.count,"B").end(xlup)) end with me.combobox1.clear for each mycell in myrng.cells if mycell.value = clng(me.textbox1.value) then me.combobox1.additem mycell.offset(0,-1).value end if next mycell sam wrote: Hey Dave, to make it clear. Here is and eg of how the data looks on sheet Tasks Roll_No 3 11 5 11 4 12 6 12 2 13 5 13 3 14 4 14 1 15 2 15 1 16 4 16 So lets say you select "12" from Roll_No column filter then it will display Tasks Roll_No(filter) 4 12 6 12 So I want this 4 and 6 to be displayed in the dropdown list on userform too Is there a way to capture filter values(4 and 6) from a sheet to a userfrom. So when I input "12" on userform textbox I se 4 and 6 for my Tasks Dropdown on the userform. Hope I made it clear "Dave Peterson" wrote: Once you find the cell that holds the name, you can use VBA's split command (if you're using xl2k or higher) to parse that data into an array. Then use that array as the combobox's .list. Dim myStr As String Dim myArr As Variant myStr = "1,2,3" 'how ever you find that cell would go here myArr = Split(myStr, ",") With Me.ComboBox1 .Clear .List = myArr End With sam wrote: Thanks a lot for you help Dave, One more thing I wanted in my form was a way to capture filter values from sheet2 into a Dropdown list in the user form. Is there a way to do this? For eg, I have a column "Tasks" on sheet2 which have values 1,2,3,4,5,6 and each student can have multiple "Tasks" such as.. John: 1,3 Jill: 1,5 jack: 3,5,6 Bill: 2,4,6 and so on... I have this column set as a filter(Behaves like a dropdown on excel sheet), so John will have a Dropdown Filter with values 1 and 3 in "Tasks" column So basically, I want these filter values to be displayed on the form as dropdown menu with 1 and 3 displayed for John. Hope I made it clear Thanks in advance "Dave Peterson" wrote: Dim res as variant with worksheets("sheet2") 'I'd change the name to something meaningful 'look for a match using a string res = application.match(me.textbox1.value,.range("A:A"), 0) if iserror(res) then 'look for a match using a real number res = application.match(clng(me.textbox1.value),.range(" A:a"),0) end if if iserror(res) then 'no match between textbox1 and column A of Sheet2 'what should happen beep msgbox "Invalid entry" exit sub '??? end if me.textbox2.value = .range("a:a")(res).offset(0,1).value 'column b me.textbox3.value = .range("a:a")(res).offset(0,5).value 'column F End with (Untested, uncompiled. Watch for typos.) sam wrote: Hi All, How can I use Inded and Match function on VBA to populate certain form fields? For eg: I have a userform where Users input: Roll No(Unique to every student): Last Name : First Name : Subject1 : Ph No: What I want to do is: Once Users Input Their Roll No, I want to populate their Last Name, First Name, Subject1 and Ph No. Also the roll number might not be in descending order, Hence I want to use somethign like Index and Match function, So once a student Inputs the roll number, It will match the roll number and populate other fields respectively The button to launch the Form is on Sheet1 and All the student data is in Sheet2. Hope I made it clear Thanks in Advance -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
PLEASE HELP: Index and Match function in Userform to populate
Hey Dave, Rite now with this code it is saving seperate copies on clicking
"Insert" I want to loop it through the same worksheet and save the data on the same sheet but new row every time I hit "Insert" SO when I click "Insert" after selecting a Task from the dropdown... I want that Task and ROll no to populate in Row1, then If I input a different roll no and select a Task for that and click "Insert" I want this to populate in Row2 and so on.. Untill I manually save the workbook Thanks in advance "Dave Peterson" wrote: dim newWks as worksheet 'new workbook with a single sheet set newwks = workbooks.add(1).worksheets(1) with newwks .range("A1").value = me.textbox1.value 'roll_no .range("B1").value = me.whatever..... End with with newwks.parent 'the new workbook .saveas _ filename:="C:\newfilename_" & format(now,"yyyymmdd_hhmmss") & ".xls", _ fileformat:=xlworkbooknormal .close savechanges:=false end with sam wrote: Hey Dave, Thanks for this.. It worked out great! I am trying to create a button âœInsertâ in the same form. What I want this button to do is: When I click Insert.. It Opens a new workbook, Inserts the current student details in first row(Roll_No, Task, ect.), SO basically Every time I hit this âœInsertâ button I want to populate current student detail(Roll_No, Task, ect.) in a new row. Is this possible? Thanks in advance "Dave Peterson" wrote: After this section of your code: me.combobox1.clear for each mycell in myrng.cells if mycell.value = clng(me.textbox1.value) then me.combobox1.additem mycell.offset(0,-1).value end if next mycell if me.combobox1.listcount = 0 then 'nothing found me.combobox1.enabled = false else me.combobox1.enabled = true end if =================================== You could even check beforehand: With worksheets("Sheet2") set myrng = .range("B2",.cells(.rows.count,"B").end(xlup)) end with me.combobox1.clear 'no matter what if application.countif(myrng, me.textbox1.value) = 0 then me.combobox1.enabled = false else me.combobox1.enabled = true 'just in case it was ever false for each mycell in myrng.cells if mycell.value = clng(me.textbox1.value) then me.combobox1.additem mycell.offset(0,-1).value end if next mycell end if sam wrote: Hey Dave, Thanks a lot for all you help in getting this to work. I was working on this and I came across an issue where students input values in "Roll_No" field that are not present in the sheet. Is there a way where I can disable the "Tasks" dropdown if they input an invalid "Roll_No" or a "Roll_No" that is not present in the sheet? So basically enable the "Tasks" dropdown on form only if the "Roll_No" they input is from the list in the sheet. Thanks in advance "Dave Peterson" wrote: You're not really filtering the worksheet based on the roll number, right? If you're not... You can loop through the range. Dim myRng as range dim myCell as range With worksheets("Sheet2") set myrng = .range("B2",.cells(.rows.count,"B").end(xlup)) end with me.combobox1.clear for each mycell in myrng.cells if mycell.value = clng(me.textbox1.value) then me.combobox1.additem mycell.offset(0,-1).value end if next mycell sam wrote: Hey Dave, to make it clear. Here is and eg of how the data looks on sheet Tasks Roll_No 3 11 5 11 4 12 6 12 2 13 5 13 3 14 4 14 1 15 2 15 1 16 4 16 So lets say you select "12" from Roll_No column filter then it will display Tasks Roll_No(filter) 4 12 6 12 So I want this 4 and 6 to be displayed in the dropdown list on userform too Is there a way to capture filter values(4 and 6) from a sheet to a userfrom. So when I input "12" on userform textbox I se 4 and 6 for my Tasks Dropdown on the userform. Hope I made it clear "Dave Peterson" wrote: Once you find the cell that holds the name, you can use VBA's split command (if you're using xl2k or higher) to parse that data into an array. Then use that array as the combobox's .list. Dim myStr As String Dim myArr As Variant myStr = "1,2,3" 'how ever you find that cell would go here myArr = Split(myStr, ",") With Me.ComboBox1 .Clear .List = myArr End With sam wrote: Thanks a lot for you help Dave, One more thing I wanted in my form was a way to capture filter values from sheet2 into a Dropdown list in the user form. Is there a way to do this? For eg, I have a column "Tasks" on sheet2 which have values 1,2,3,4,5,6 and each student can have multiple "Tasks" such as.. John: 1,3 Jill: 1,5 jack: 3,5,6 Bill: 2,4,6 and so on... I have this column set as a filter(Behaves like a dropdown on excel sheet), so John will have a Dropdown Filter with values 1 and 3 in "Tasks" column So basically, I want these filter values to be displayed on the form as dropdown menu with 1 and 3 displayed for John. Hope I made it clear Thanks in advance "Dave Peterson" wrote: Dim res as variant with worksheets("sheet2") 'I'd change the name to something meaningful 'look for a match using a string res = application.match(me.textbox1.value,.range("A:A"), 0) if iserror(res) then 'look for a match using a real number res = application.match(clng(me.textbox1.value),.range(" A:a"),0) end if if iserror(res) then 'no match between textbox1 and column A of Sheet2 'what should happen beep msgbox "Invalid entry" exit sub '??? end if me.textbox2.value = .range("a:a")(res).offset(0,1).value 'column b me.textbox3.value = .range("a:a")(res).offset(0,5).value 'column F End with (Untested, uncompiled. Watch for typos.) sam wrote: Hi All, How can I use Inded and Match function on VBA to populate certain form fields? For eg: I have a userform where Users input: Roll No(Unique to every student): Last Name : First Name : Subject1 : Ph No: What I want to do is: Once Users Input Their Roll No, I want to populate their Last Name, First Name, Subject1 and Ph No. Also the roll number might not be in descending order, Hence I want to use somethign like Index and Match function, So once a student Inputs the roll number, It will match the roll number and populate other fields respectively The button to launch the Form is on Sheet1 and All the student data is in Sheet2. Hope I made it clear Thanks in Advance -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
PLEASE HELP: Index and Match function in Userform to populate
Set up the output sheet first:
'at the top of the userform module dim WksCreated as boolean sub commandbutton1_click() dim newWks as worksheet dim DestCell as range if wkscreated = false then 'new workbook with a single sheet set newwks = workbooks.add(1).worksheets(1) wksCreated = true end if with newwks set destcell = .cells(.rows.count,"A").end(xlup).offset(1,0) end with with destcell .value = me.textbox1.value .offset(0,1).value = "next column over" .offset(0,5).value = "5 columns to the right" end with End sub Untested, uncompiled. Then do the sam wrote: Hey Dave, Rite now with this code it is saving seperate copies on clicking "Insert" I want to loop it through the same worksheet and save the data on the same sheet but new row every time I hit "Insert" SO when I click "Insert" after selecting a Task from the dropdown... I want that Task and ROll no to populate in Row1, then If I input a different roll no and select a Task for that and click "Insert" I want this to populate in Row2 and so on.. Untill I manually save the workbook Thanks in advance "Dave Peterson" wrote: dim newWks as worksheet 'new workbook with a single sheet set newwks = workbooks.add(1).worksheets(1) with newwks .range("A1").value = me.textbox1.value 'roll_no .range("B1").value = me.whatever..... End with with newwks.parent 'the new workbook .saveas _ filename:="C:\newfilename_" & format(now,"yyyymmdd_hhmmss") & ".xls", _ fileformat:=xlworkbooknormal .close savechanges:=false end with sam wrote: Hey Dave, Thanks for this.. It worked out great! I am trying to create a button âœInsertâ in the same form. What I want this button to do is: When I click Insert.. It Opens a new workbook, Inserts the current student details in first row(Roll_No, Task, ect.), SO basically Every time I hit this âœInsertâ button I want to populate current student detail(Roll_No, Task, ect.) in a new row. Is this possible? Thanks in advance "Dave Peterson" wrote: After this section of your code: me.combobox1.clear for each mycell in myrng.cells if mycell.value = clng(me.textbox1.value) then me.combobox1.additem mycell.offset(0,-1).value end if next mycell if me.combobox1.listcount = 0 then 'nothing found me.combobox1.enabled = false else me.combobox1.enabled = true end if =================================== You could even check beforehand: With worksheets("Sheet2") set myrng = .range("B2",.cells(.rows.count,"B").end(xlup)) end with me.combobox1.clear 'no matter what if application.countif(myrng, me.textbox1.value) = 0 then me.combobox1.enabled = false else me.combobox1.enabled = true 'just in case it was ever false for each mycell in myrng.cells if mycell.value = clng(me.textbox1.value) then me.combobox1.additem mycell.offset(0,-1).value end if next mycell end if sam wrote: Hey Dave, Thanks a lot for all you help in getting this to work. I was working on this and I came across an issue where students input values in "Roll_No" field that are not present in the sheet. Is there a way where I can disable the "Tasks" dropdown if they input an invalid "Roll_No" or a "Roll_No" that is not present in the sheet? So basically enable the "Tasks" dropdown on form only if the "Roll_No" they input is from the list in the sheet. Thanks in advance "Dave Peterson" wrote: You're not really filtering the worksheet based on the roll number, right? If you're not... You can loop through the range. Dim myRng as range dim myCell as range With worksheets("Sheet2") set myrng = .range("B2",.cells(.rows.count,"B").end(xlup)) end with me.combobox1.clear for each mycell in myrng.cells if mycell.value = clng(me.textbox1.value) then me.combobox1.additem mycell.offset(0,-1).value end if next mycell sam wrote: Hey Dave, to make it clear. Here is and eg of how the data looks on sheet Tasks Roll_No 3 11 5 11 4 12 6 12 2 13 5 13 3 14 4 14 1 15 2 15 1 16 4 16 So lets say you select "12" from Roll_No column filter then it will display Tasks Roll_No(filter) 4 12 6 12 So I want this 4 and 6 to be displayed in the dropdown list on userform too Is there a way to capture filter values(4 and 6) from a sheet to a userfrom. So when I input "12" on userform textbox I se 4 and 6 for my Tasks Dropdown on the userform. Hope I made it clear "Dave Peterson" wrote: Once you find the cell that holds the name, you can use VBA's split command (if you're using xl2k or higher) to parse that data into an array. Then use that array as the combobox's .list. Dim myStr As String Dim myArr As Variant myStr = "1,2,3" 'how ever you find that cell would go here myArr = Split(myStr, ",") With Me.ComboBox1 .Clear .List = myArr End With sam wrote: Thanks a lot for you help Dave, One more thing I wanted in my form was a way to capture filter values from sheet2 into a Dropdown list in the user form. Is there a way to do this? For eg, I have a column "Tasks" on sheet2 which have values 1,2,3,4,5,6 and each student can have multiple "Tasks" such as.. John: 1,3 Jill: 1,5 jack: 3,5,6 Bill: 2,4,6 and so on... I have this column set as a filter(Behaves like a dropdown on excel sheet), so John will have a Dropdown Filter with values 1 and 3 in "Tasks" column So basically, I want these filter values to be displayed on the form as dropdown menu with 1 and 3 displayed for John. Hope I made it clear Thanks in advance "Dave Peterson" wrote: Dim res as variant with worksheets("sheet2") 'I'd change the name to something meaningful 'look for a match using a string res = application.match(me.textbox1.value,.range("A:A"), 0) if iserror(res) then 'look for a match using a real number res = application.match(clng(me.textbox1.value),.range(" A:a"),0) end if if iserror(res) then 'no match between textbox1 and column A of Sheet2 'what should happen beep msgbox "Invalid entry" exit sub '??? end if me.textbox2.value = .range("a:a")(res).offset(0,1).value 'column b me.textbox3.value = .range("a:a")(res).offset(0,5).value 'column F End with (Untested, uncompiled. Watch for typos.) sam wrote: Hi All, How can I use Inded and Match function on VBA to populate certain form fields? For eg: I have a userform where Users input: Roll No(Unique to every student): Last Name : First Name : Subject1 : Ph No: What I want to do is: Once Users Input Their Roll No, I want to populate their Last Name, First Name, Subject1 and Ph No. Also the roll number might not be in descending order, Hence I want to use somethign like Index and Match function, So once a student Inputs the roll number, It will match the roll number and populate other fields respectively The button to launch the Form is on Sheet1 and All the student data is in Sheet2. Hope I made it clear Thanks in Advance -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
PLEASE HELP: Index and Match function in Userform to populate
Hi Dave,
I am trying to get this thing to work but its not working as I want. Lets say I have a button "Export" on the userform, This userform has a dropdown menu, which has student names, and selecting a student name from that dropdown would populate other fields on the form such as, Name, Age, Task etc.. So if i select Student1 from the dropdown, I will get the data of Student1 displayed on the form, If i select Student2 from the dropdown, I will get data of Student2 displayed on the form. Now when I click export, I want it to: - open a file dialog box that lets me select another empty excel file - once i select the excel file, i want the details of the select student to be displayed in Row1 -If i select another student from the dropdown, i want the details of that student to be displayed in Row2... and so on.. - Once I am done selecting the students, I want to manualy save the excel file where I exported the student data. Is this possible? Thanks in Advance "Dave Peterson" wrote: Set up the output sheet first: 'at the top of the userform module dim WksCreated as boolean sub commandbutton1_click() dim newWks as worksheet dim DestCell as range if wkscreated = false then 'new workbook with a single sheet set newwks = workbooks.add(1).worksheets(1) wksCreated = true end if with newwks set destcell = .cells(.rows.count,"A").end(xlup).offset(1,0) end with with destcell .value = me.textbox1.value .offset(0,1).value = "next column over" .offset(0,5).value = "5 columns to the right" end with End sub Untested, uncompiled. Then do the sam wrote: Hey Dave, Rite now with this code it is saving seperate copies on clicking "Insert" I want to loop it through the same worksheet and save the data on the same sheet but new row every time I hit "Insert" SO when I click "Insert" after selecting a Task from the dropdown... I want that Task and ROll no to populate in Row1, then If I input a different roll no and select a Task for that and click "Insert" I want this to populate in Row2 and so on.. Untill I manually save the workbook Thanks in advance "Dave Peterson" wrote: dim newWks as worksheet 'new workbook with a single sheet set newwks = workbooks.add(1).worksheets(1) with newwks .range("A1").value = me.textbox1.value 'roll_no .range("B1").value = me.whatever..... End with with newwks.parent 'the new workbook .saveas _ filename:="C:\newfilename_" & format(now,"yyyymmdd_hhmmss") & ".xls", _ fileformat:=xlworkbooknormal .close savechanges:=false end with sam wrote: Hey Dave, Thanks for this.. It worked out great! I am trying to create a button ââ¬ÅInsertââ¬Â in the same form. What I want this button to do is: When I click Insert.. It Opens a new workbook, Inserts the current student details in first row(Roll_No, Task, ect.), SO basically Every time I hit this ââ¬ÅInsertââ¬Â button I want to populate current student detail(Roll_No, Task, ect.) in a new row. Is this possible? Thanks in advance "Dave Peterson" wrote: After this section of your code: me.combobox1.clear for each mycell in myrng.cells if mycell.value = clng(me.textbox1.value) then me.combobox1.additem mycell.offset(0,-1).value end if next mycell if me.combobox1.listcount = 0 then 'nothing found me.combobox1.enabled = false else me.combobox1.enabled = true end if =================================== You could even check beforehand: With worksheets("Sheet2") set myrng = .range("B2",.cells(.rows.count,"B").end(xlup)) end with me.combobox1.clear 'no matter what if application.countif(myrng, me.textbox1.value) = 0 then me.combobox1.enabled = false else me.combobox1.enabled = true 'just in case it was ever false for each mycell in myrng.cells if mycell.value = clng(me.textbox1.value) then me.combobox1.additem mycell.offset(0,-1).value end if next mycell end if sam wrote: Hey Dave, Thanks a lot for all you help in getting this to work. I was working on this and I came across an issue where students input values in "Roll_No" field that are not present in the sheet. Is there a way where I can disable the "Tasks" dropdown if they input an invalid "Roll_No" or a "Roll_No" that is not present in the sheet? So basically enable the "Tasks" dropdown on form only if the "Roll_No" they input is from the list in the sheet. Thanks in advance "Dave Peterson" wrote: You're not really filtering the worksheet based on the roll number, right? If you're not... You can loop through the range. Dim myRng as range dim myCell as range With worksheets("Sheet2") set myrng = .range("B2",.cells(.rows.count,"B").end(xlup)) end with me.combobox1.clear for each mycell in myrng.cells if mycell.value = clng(me.textbox1.value) then me.combobox1.additem mycell.offset(0,-1).value end if next mycell sam wrote: Hey Dave, to make it clear. Here is and eg of how the data looks on sheet Tasks Roll_No 3 11 5 11 4 12 6 12 2 13 5 13 3 14 4 14 1 15 2 15 1 16 4 16 So lets say you select "12" from Roll_No column filter then it will display Tasks Roll_No(filter) 4 12 6 12 So I want this 4 and 6 to be displayed in the dropdown list on userform too Is there a way to capture filter values(4 and 6) from a sheet to a userfrom. So when I input "12" on userform textbox I se 4 and 6 for my Tasks Dropdown on the userform. Hope I made it clear "Dave Peterson" wrote: Once you find the cell that holds the name, you can use VBA's split command (if you're using xl2k or higher) to parse that data into an array. Then use that array as the combobox's .list. Dim myStr As String Dim myArr As Variant myStr = "1,2,3" 'how ever you find that cell would go here myArr = Split(myStr, ",") With Me.ComboBox1 .Clear .List = myArr End With sam wrote: Thanks a lot for you help Dave, One more thing I wanted in my form was a way to capture filter values from sheet2 into a Dropdown list in the user form. Is there a way to do this? For eg, I have a column "Tasks" on sheet2 which have values 1,2,3,4,5,6 and each student can have multiple "Tasks" such as.. John: 1,3 Jill: 1,5 jack: 3,5,6 Bill: 2,4,6 and so on... I have this column set as a filter(Behaves like a dropdown on excel sheet), so John will have a Dropdown Filter with values 1 and 3 in "Tasks" column So basically, I want these filter values to be displayed on the form as dropdown menu with 1 and 3 displayed for John. Hope I made it clear Thanks in advance "Dave Peterson" wrote: Dim res as variant with worksheets("sheet2") 'I'd change the name to something meaningful 'look for a match using a string res = application.match(me.textbox1.value,.range("A:A"), 0) if iserror(res) then 'look for a match using a real number res = application.match(clng(me.textbox1.value),.range(" A:a"),0) end if if iserror(res) then 'no match between textbox1 and column A of Sheet2 'what should happen beep msgbox "Invalid entry" exit sub '??? end if me.textbox2.value = .range("a:a")(res).offset(0,1).value 'column b me.textbox3.value = .range("a:a")(res).offset(0,5).value 'column F End with (Untested, uncompiled. Watch for typos.) sam wrote: Hi All, How can I use Inded and Match function on VBA to populate certain form fields? For eg: I have a userform where Users input: Roll No(Unique to every student): Last Name : First Name : Subject1 : Ph No: What I want to do is: Once Users Input Their Roll No, I want to populate their Last Name, First Name, Subject1 and Ph No. Also the roll number might not be in descending order, Hence I want to use somethign like Index and Match function, So once a student Inputs the roll number, It will match the roll number and populate other fields respectively The button to launch the Form is on Sheet1 and All the student data is in Sheet2. Hope I made it clear Thanks in Advance -- Dave Peterson |
PLEASE HELP: Index and Match function in Userform to populate
What does "select another empty excel file" mean?
I guessed that creating a new workbook with a single worksheet would be ok. That's what this did: set newwks = workbooks.add(1).worksheets(1) wksCreated = true end if sam wrote: Hi Dave, I am trying to get this thing to work but its not working as I want. Lets say I have a button "Export" on the userform, This userform has a dropdown menu, which has student names, and selecting a student name from that dropdown would populate other fields on the form such as, Name, Age, Task etc.. So if i select Student1 from the dropdown, I will get the data of Student1 displayed on the form, If i select Student2 from the dropdown, I will get data of Student2 displayed on the form. Now when I click export, I want it to: - open a file dialog box that lets me select another empty excel file - once i select the excel file, i want the details of the select student to be displayed in Row1 -If i select another student from the dropdown, i want the details of that student to be displayed in Row2... and so on.. - Once I am done selecting the students, I want to manualy save the excel file where I exported the student data. Is this possible? Thanks in Advance "Dave Peterson" wrote: Set up the output sheet first: 'at the top of the userform module dim WksCreated as boolean sub commandbutton1_click() dim newWks as worksheet dim DestCell as range if wkscreated = false then 'new workbook with a single sheet set newwks = workbooks.add(1).worksheets(1) wksCreated = true end if with newwks set destcell = .cells(.rows.count,"A").end(xlup).offset(1,0) end with with destcell .value = me.textbox1.value .offset(0,1).value = "next column over" .offset(0,5).value = "5 columns to the right" end with End sub Untested, uncompiled. Then do the sam wrote: Hey Dave, Rite now with this code it is saving seperate copies on clicking "Insert" I want to loop it through the same worksheet and save the data on the same sheet but new row every time I hit "Insert" SO when I click "Insert" after selecting a Task from the dropdown... I want that Task and ROll no to populate in Row1, then If I input a different roll no and select a Task for that and click "Insert" I want this to populate in Row2 and so on.. Untill I manually save the workbook Thanks in advance "Dave Peterson" wrote: dim newWks as worksheet 'new workbook with a single sheet set newwks = workbooks.add(1).worksheets(1) with newwks .range("A1").value = me.textbox1.value 'roll_no .range("B1").value = me.whatever..... End with with newwks.parent 'the new workbook .saveas _ filename:="C:\newfilename_" & format(now,"yyyymmdd_hhmmss") & ".xls", _ fileformat:=xlworkbooknormal .close savechanges:=false end with sam wrote: Hey Dave, Thanks for this.. It worked out great! I am trying to create a button ââ¬ÅInsertââ¬Â in the same form. What I want this button to do is: When I click Insert.. It Opens a new workbook, Inserts the current student details in first row(Roll_No, Task, ect.), SO basically Every time I hit this ââ¬ÅInsertââ¬Â button I want to populate current student detail(Roll_No, Task, ect.) in a new row. Is this possible? Thanks in advance "Dave Peterson" wrote: After this section of your code: me.combobox1.clear for each mycell in myrng.cells if mycell.value = clng(me.textbox1.value) then me.combobox1.additem mycell.offset(0,-1).value end if next mycell if me.combobox1.listcount = 0 then 'nothing found me.combobox1.enabled = false else me.combobox1.enabled = true end if =================================== You could even check beforehand: With worksheets("Sheet2") set myrng = .range("B2",.cells(.rows.count,"B").end(xlup)) end with me.combobox1.clear 'no matter what if application.countif(myrng, me.textbox1.value) = 0 then me.combobox1.enabled = false else me.combobox1.enabled = true 'just in case it was ever false for each mycell in myrng.cells if mycell.value = clng(me.textbox1.value) then me.combobox1.additem mycell.offset(0,-1).value end if next mycell end if sam wrote: Hey Dave, Thanks a lot for all you help in getting this to work. I was working on this and I came across an issue where students input values in "Roll_No" field that are not present in the sheet. Is there a way where I can disable the "Tasks" dropdown if they input an invalid "Roll_No" or a "Roll_No" that is not present in the sheet? So basically enable the "Tasks" dropdown on form only if the "Roll_No" they input is from the list in the sheet. Thanks in advance "Dave Peterson" wrote: You're not really filtering the worksheet based on the roll number, right? If you're not... You can loop through the range. Dim myRng as range dim myCell as range With worksheets("Sheet2") set myrng = .range("B2",.cells(.rows.count,"B").end(xlup)) end with me.combobox1.clear for each mycell in myrng.cells if mycell.value = clng(me.textbox1.value) then me.combobox1.additem mycell.offset(0,-1).value end if next mycell sam wrote: Hey Dave, to make it clear. Here is and eg of how the data looks on sheet Tasks Roll_No 3 11 5 11 4 12 6 12 2 13 5 13 3 14 4 14 1 15 2 15 1 16 4 16 So lets say you select "12" from Roll_No column filter then it will display Tasks Roll_No(filter) 4 12 6 12 So I want this 4 and 6 to be displayed in the dropdown list on userform too Is there a way to capture filter values(4 and 6) from a sheet to a userfrom. So when I input "12" on userform textbox I se 4 and 6 for my Tasks Dropdown on the userform. Hope I made it clear "Dave Peterson" wrote: Once you find the cell that holds the name, you can use VBA's split command (if you're using xl2k or higher) to parse that data into an array. Then use that array as the combobox's .list. Dim myStr As String Dim myArr As Variant myStr = "1,2,3" 'how ever you find that cell would go here myArr = Split(myStr, ",") With Me.ComboBox1 .Clear .List = myArr End With sam wrote: Thanks a lot for you help Dave, One more thing I wanted in my form was a way to capture filter values from sheet2 into a Dropdown list in the user form. Is there a way to do this? For eg, I have a column "Tasks" on sheet2 which have values 1,2,3,4,5,6 and each student can have multiple "Tasks" such as.. John: 1,3 Jill: 1,5 jack: 3,5,6 Bill: 2,4,6 and so on... I have this column set as a filter(Behaves like a dropdown on excel sheet), so John will have a Dropdown Filter with values 1 and 3 in "Tasks" column So basically, I want these filter values to be displayed on the form as dropdown menu with 1 and 3 displayed for John. Hope I made it clear Thanks in advance "Dave Peterson" wrote: Dim res as variant with worksheets("sheet2") 'I'd change the name to something meaningful 'look for a match using a string res = application.match(me.textbox1.value,.range("A:A"), 0) if iserror(res) then 'look for a match using a real number res = application.match(clng(me.textbox1.value),.range(" A:a"),0) end if if iserror(res) then 'no match between textbox1 and column A of Sheet2 'what should happen beep msgbox "Invalid entry" exit sub '??? end if me.textbox2.value = .range("a:a")(res).offset(0,1).value 'column b me.textbox3.value = .range("a:a")(res).offset(0,5).value 'column F End with (Untested, uncompiled. Watch for typos.) sam wrote: Hi All, How can I use Inded and Match function on VBA to populate certain form fields? For eg: I have a userform where Users input: Roll No(Unique to every student): Last Name : First Name : Subject1 : Ph No: What I want to do is: Once Users Input Their Roll No, I want to populate their Last Name, First Name, Subject1 and Ph No. Also the roll number might not be in descending order, Hence I want to use somethign like Index and Match function, So once a student Inputs the roll number, It will match the roll number and populate other fields respectively The button to launch the Form is on Sheet1 and All the student data is in Sheet2. Hope I made it clear Thanks in Advance -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 09:05 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com