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!



    "Varium et mutabile semper Excel"

  • 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

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


    "Varium et mutabile semper Excel"

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!