Retrieving data based on a combobox selection

[Aussiebear]

I beg the indulgence of the Administrators, for I have a two part question.

In the attached file, I have supplied two sets of flat based records. On one sheet is data relating to individual employees and it is this data set that the first part of my question is based. I want to use a combobox to select from the range $A$2:$A$6 and then have the relevant data returned to additional text boxes on a form, based upon the value selected from the combobox. How is this done?

The second part of the question is based on the data set contained in the second sheet of the attached file. This is more complex in so far as a value that you might select from the range $A$2:$A$9 of sheet 2 is repeated. How do I find the last occuring record set of a value selected by combobox value, and have the related data return to text boxes on a form?

Example if I wanted to find the last the last occurance of employee Appleby, Sam (row 8) and return the data contained therein.

[Bob Phillips]

In the attached file, I have supplied two sets of flat based records. On one sheet is data relating to individual employees and it is this data set that the first part of my question is based. I want to use a combobox to select from the range $A$2:$A$6 and then have the relevant data returned to additional text boxes on a form, based upon the value selected from the combobox. How is this done?

When you select an item in a combo, the ListIndex property is set. This is 0 based, so add 1 and you get the row number. As you use row 1 for headings which I assume will not be in the combo, you need to add another 1. You can then pick up other values with

Cells(ComboBox1.ListIndex+2,"E")

as an example.

Another, more complex, way, but one that I would use is to include all columns in the ComboBox but don't increase the ColumnCount property, and then pick them up when you select from the Combo.

The second part of the question is based on the data set contained in the second sheet of the attached file. This is more complex in so far as a value that you might select from the range $A$2:$A$9 of sheet 2 is repeated. How do I find the last occuring record set of a value selected by combobox value, and have the related data return to text boxes on a form?

Example if I wanted to find the last the last occurance of employee Appleby, Sam (row 8) and return the data contained therein.

You have to have some way of selecting out all rows pertaining to the selected value and then get the last.

I would use a simple worksheetfunction to get an array of the matches and then max out of that

[vba]

ary = ActiveSheet.Evaluate("INDEX((A2:A9=""" & Me.ComboBox1.Value & """)*(ROW(A2:A9)),0)")
MsgBox Application.Max(ary)
[/vba]

[Aussiebear]

Bob what about the following as a possible solution?

[VBA]
Private Sub cboEmployee_Click()
Worksheets("Sheet2").Select
Set tgt = ActiveSheet.Coumns(1).Find(What:=Me.cboEmployee.Text, LookAt:=xlwhole)
Me.txtStartdate.Value = Tgt.Offset(, 1)
Me.txttype = Tgt.Offset(, 2)
Me.txtcourse1 = Tgt.Offset(, 4)
Me.txtcourse2 = Tgt.Offset(, 5)
Me.txtcourse3 = Tgt.Offset(, 6)
End Sub
[/VBA]

The form I've built is looking for information on sheet2 "Complex data Set", or rather it is meant to. Column D is a space column hence the jump in the numbers in the Tgt.Offset. ONly problem here is that the find method will only work properly if there is a unique value in range A2:A9

[mdmackillop]

Hi Ted,
A bit confusing.
The textbox names seem to relate to Simple Data Set
Names are not identical on both sheets (missing space) Consider a Validation list
Avoid selection of sheets to return data; it's rarely required.
Declare your variables.
Attaching a copy of your form would save us creating one to test the code.

I've not tried to address your multiple name problem as this stage.

[vba]Private Sub cboEmployee_Click()
Dim Tgt As Range
Set Tgt = Sheets(1).Columns(1).Find(What:=Me.cboEmployee.Text, LookAt:=xlWhole)
Me.txtStartDate.Value = Tgt.Offset(, 1)
Me.txtType = Tgt.Offset(, 2)
Me.txtCourse1 = Tgt.Offset(, 4)
Me.txtCourse2 = Tgt.Offset(, 5)
Me.txtCourse3 = Tgt.Offset(, 6)
End Sub

[/vba]

[Aussiebear]

I concur wholehearted MD. I did stuff up.

Now as to the second part, is it possible to set a target based upon to criteria, that is initially from the value selected in the combobox and then based on the last entry ( value based on a date) ?

[vba]
Private Sub cboEmployee_Click()
Dim Tgt As Range
Set Tgt = Sheets(1).Columns 1).Find (What:=cboEmployee.Text, After:=Tgt.Offset(, 0), ActiveSheet.Columns(2).Find (What:=LastDate, LookAt :=xlWhole)
Me.txtStartDate.Value = Tgt.Offset(, 1)
Me.txtType = Tgt.Offset(, 2)
Me.txtCourse1 = Tgt.Offset(, 4)
Me.txtCourse2 = Tgt.Offset(, 5)
Me.txtCourse3 = Tgt.Offset(, 6)
End Sub
[/vba]

Bob , kindly offerred the following;

[vba]
ary=Activesheet.Evaluate("Index((A2:A9=""" & Me.cboCombobox1.Value _
& """) * (ROW(A2:A9)),0)")
[/vba] ...which I didn't understand. I can only assume that ary is short for Array, that is calls for an evaluation of an Index based on the range A2: A9, and in doing so is looking for the value selected from the combobox. Does it dispay the findings in a msgbox?


BTW, Ill fix the workbook up and repost it. So you'll understand what I'm rambling on about

[Aussiebear]

Sorry fresh out of bed and brain is not in gear yet. Left off a piece of code from Bob.
[vba]
Msgbox Application.Max (ary)
[/VBA]

[Bob Phillips]

I concur wholehearted MD. I did stuff up.

Now as to the second part, is it possible to set a target based upon to criteria, that is initially from the value selected in the combobox and then based on the last entry ( value based on a date) ?

[vba]
Private Sub cboEmployee_Click()
Dim Tgt As Range
Set Tgt = Sheets(1).Columns 1).Find (What:=cboEmployee.Text, After:=Tgt.Offset(, 0), ActiveSheet.Columns(2).Find (What:=LastDate, LookAt :=xlWhole)
Me.txtStartDate.Value = Tgt.Offset(, 1)
Me.txtType = Tgt.Offset(, 2)
Me.txtCourse1 = Tgt.Offset(, 4)
Me.txtCourse2 = Tgt.Offset(, 5)
Me.txtCourse3 = Tgt.Offset(, 6)
End Sub
[/vba]

Bob , kindly offerred the following;

[vba]
ary=Activesheet.Evaluate("Index((A2:A9=""" & Me.cboCombobox1.Value _
& ""&quot * (ROW(A2:A9)),0)&quot
[/vba] ...which I didn't understand. I can only assume that ary is short for Array, that is calls for an evaluation of an Index based on the range A2: A9, and in doing so is looking for the value selected from the combobox. Does it dispay the findings in a msgbox?


BTW, Ill fix the workbook up and repost it. So you'll understand what I'm rambling on about

No, ary is a Variant variable. The worksheet function that I use returns an array, which is why I use that name, and then I use the max function to get the last row number.

[Bob Phillips]

I used MsgBox just to demonstrate the technique. You would use it as an index into other columns of the data in reality.

[Aussiebear]

At this stage the form reflects the first find of the value selected in the combobox, which raises the question, can it reverse the search order and look from the last row as the starting point rather than from the first row?

The dummy data is meant to represent those employees who have undertaken courses and when they did so. So if I was looking for the last course undertaken by an employee, say "Billy Smith" it could tell me that on the 25/07/06 he started marketing.

Bob, where does your suggested code go? You called it a function, so do I use the Insert Procedure and write it to there?

[Bob Phillips]

The code would go wherever you have identified the resource and then you need to populate the other data on the form dependent upon the selected form. And I didn't say function, I said worksheetfunction, although you could build it as a function. And don't ignore what Malcolm said abut the names and Data Validation.

[Aussiebear]

Another, more complex, way, but one that I would use is to include all columns in the ComboBox but don't increase the ColumnCount property, and then pick them up when you select from the Combo.



You have to have some way of selecting out all rows pertaining to the selected value and then get the last.

I would use a simple worksheetfunction to get an array of the matches and then max out of that

[vba]

ary = ActiveSheet.Evaluate("INDEX((A2:A9=""" & Me.ComboBox1.Value & """)*(ROW(A2:A9)),0)")
MsgBox Application.Max(ary)
[/vba]

Excel help has given me a basic understanding of the Index, & Row components of your worksheet function. Can I ask then the purpose of the "*"? Is this to force Excel to pick up the value of the Row component which is alongside the value found in the range A2:A9 that equates to the value selected in the combobox?

Secondly after the range has been selected in the row component, you have used ",0)" what does this do?

[Bob Phillips]

Sort of. The * acts as an AND operator here. I am getting data matching two conditions, those items in A2:A9 that match the combobox value, and the row numbers of A2:A9. By */ANDing it, it gives me row numbers only where the cells match the combo value.

Normally, INDEX is used to return a single value from an array, and you tell it which column and which row in the array. If you use an argument value of 0, it will return an array of values instead of a single value.