VBA: Find & Select a Row based on ComboBox Value

Hi All,

I have a workbook with 2 sheets; “Lists” and “DataTable”, and a UserForm with several controls on it, one of which is a ComboBox “cboSerNo” populated with serial numbers from a dynamic range “Serial_No” based on column A of “Lists”.

The User enters a value in cboSerNo or picks one from the drop-down and clicks the OK button. The code then checks to see if cboSerNo.Value exists in [Serial_No], and branches as follows:

1. If it isn’t on “Lists”, the contents of the UserForm are appended to the bottom of “Lists”, the whole row is copied to “DataTable”, and “Lists” is sorted by Serial_No.

2. If it’s already on “Lists”, cboSerNo is cleared and gets the Focus.

What I’m trying to do is modify (2) so that;

a. If cboSerNo.Value is in [Serial_No]
b. Select the row where it appears
c. Load the values from this ‘found’ row into the UserForm
d. Unlock the appropriate controls for editing
e. Make an “Edit” button visible and enabled.

I’ve got no problem with (d) and (e), but steps (a) to (c) are completely beyond me!

This must be bread-and-butter to you guys; any hints/tips/FAQ’s will be much appreciated!

Chris

If it helps, this is the code I've attached to the OK button:

Private Sub btnOK_Click()
Dim i As Integer, r As Long
Dim ctrl As Control
' Look for Serial Number in "Lists" sheet
If ([serial_no].Find(cboSerNo, , xlValues)) Is Nothing Then
' If no record with that number, create it
With Sheets("Lists")
[A65536].End(xlUp).Offset(1, 0).Select
r = ActiveCell.Row
For Each ctrl In Me.Controls
If IsNumeric(ctrl.Tag) Then
i = Val(ctrl.Tag)
Cells(r, i) = ctrl.Value
End If
Next ctrl
CopyRow 'Copy the entry just made to "DataTable" Sheet
SortListSheet 'Sort "Lists" by Serial Number
End With
End If
'**************************************************************
'* CODE TO HIGHLIGHT AND SHOW THE SELECTED RECORD *
'**************************************************************

With cboSerNo 'Clear the ComboBox
.RowSource = [serial_no].Address(external:=True)
.Value = ""
.SetFocus
End With
End Sub