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

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

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

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

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

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

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

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

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

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

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

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