Select a record from a combobox and add data to to the selected record row.

[MLI]

Hi all.

I have a user form with two list boxes and two text boxes. On the first list box one can select a "Safety Hazard". Based on that particular selection one can select "Proposed Actions" on the second listbox. The "Actual Actions" and "Date Complete" text boxes are there for user input. All that works fine.

What I want to do is to add two records on the same spread sheet row as the selected "Proposed Actions". In other words, the selected "Proposed Actions" have data in column A10:E10 and I want to add data in column F10 "Actual Actions" & G10 "Date Complete" I don't know how to send the data to the right row for the row number will change depending on the selection made in the "Proposed Actions" list box.

Thanks

[Kenneth Hobs]

Use the ListIndex property to determine which one was selected. Since the list is 0 based, adjust your row computation accordingly.

[MLI]

I was away for a while but am back into it again. Thanks for your suggestion but I think my explanation of the problem wasn't very clear. Let me try again.

The following code is to populate the second combo box dropdown list so that the user can pick an item.

[VBA]Private Sub ComboBox1_Change()
Dim JSAFileName
Dim JSAFileNameValue As String
Dim tmpCell As String
Dim PropRiskScoreCount As Integer

ReDim PropRiskScore(0)
PropRiskScore(0) = ""
PropRiskScoreCount = 0
JSAFileNameValue = CorrActForm1.ComboBox1.Value

For Each JSAFileName In Worksheets("JSA DATA").Range("C2", "C" & ActiveCell.SpecialCells(xlLastCell).Row)
If JSAFileName.Value = JSAFileNameValue Then
PropRiskScore(PropRiskScoreCount) = JSAFileName.Offset(0, 8)
PropRiskScoreCount = PropRiskScoreCount + 1
ReDim Preserve PropRiskScore(UBound(PropRiskScore) + 1)
End If
Next JSAFileName

CorrActForm1.ComboBox2.Enabled = True
CorrActForm1.ComboBox2.List() = PropRiskScore

End Sub[/VBA]

This works fine. What I am struggling with is, once the user selects an item on the combo box list he can go the next textbox on the form and type e.g. "PPE" (Updating the Database). The textbox value "PPE" must then be entered on the same database row as the selected list item but 4 columns down. I don't know how to force the textbox value into that particular position.

[mdmackillop]

When you click the combobox, you need to save the ListIndex property to a variable. When you complete the Textbox, you can use that saved value to set the destination row. Can you post a sample workbook?

[MLI]

Hi mdmackillop.
Attached is a copy of the file. When opening the file, select the "JSA Corrective Action Completion" button and from there select "JSA File Name" and then "Controlle Proposed Measure". The "Actual Corrective Action" and "Date Completed" are the fields that I want save to the database.

Thanks

[mdmackillop]

As you have repeating values in Column E, you cannot use the combobox index to locate the destination. I think you just need to cancel the selection of cell A1 and use the activecell row.