VBA Userform Code: how to add and code drop down box to select data

Question

Hi, I have a userform to update and edit 670 records, each row in excel is a unique record. Currently, I use "next/previous" function to move up and down records (rows) to edit them. This requires many click on "next/previous" to get to my desired record.

I searched the web and found the post "VBA useform Code: Adding Drop down box to select data - not sure where to start" by sschultz0956 on Oct 19, 2010. Like sschultz0956, I would like to add a drop down box that list the names of the records so I can select a record and the userform will populate other data associated with the record.

Can you show me how to do this? I am very new to VBA coding and desperately need this for work. Thank you for your attention.

This is what my test userform looks like (the actual form has the same coding but +40 fields):

ColumnA: CaseNo

ColumnB: Name

ColumnC: MRN

ColumnD: KD Dx confirmed?

ColumnE: Date of KD Dx

Here's the VBA code for my userform:

Dim lCurrentRow As Long

Private Sub cmdPrev_Click()

    ' Show previous only if not already in fifth row:

    If lCurrentRow > 5 Then

        ' Save form contents before changing rows:

        SaveRow

        ' Decrement row number:

        lCurrentRow = lCurrentRow - 1

        ' Show contents of row in the form:

        LoadRow

    End If

End Sub

Private Sub cmdNext_Click()

    ' Save form contents before changing rows:

    SaveRow

    ' Increment row number:

    lCurrentRow = lCurrentRow + 1

    ' Show contents of row in the form:

    LoadRow

End Sub

Private Sub cmdDelete_Click()

    Dim smessage As String

    smessage = "Are you sure you want to delete " + txtName.Text + "?"

    If MsgBox(smessage, vbQuestion + vbYesNo, _

              "Confirm Delete") = vbYes Then

        ' Delete current row

        Rows(lCurrentRow).Delete

        ' Show contents of new current row in the form:

        LoadRow

    End If

End Sub

Private Sub UserForm_Activate()

    ' Read initial values from Row 2:

    lCurrentRow = 5

    LoadRow

End Sub

Private Sub cmdClose_Click()

    ' Save form contents before closing:

    SaveRow

    Unload Me ' Close the form

End Sub

Private Sub LoadRow()

    txtCaseNo.Text = Cells(lCurrentRow, 1).Value

    txtName.Text = Cells(lCurrentRow, 2).Value

    txtMRN.Text = Cells(lCurrentRow, 3).Value

    cboKDdxconfirmed.Text = Cells(lCurrentRow, 4).Value

    txtDateofKDdx.Text = Cells(lCurrentRow, 5).Value

End Sub

Private Sub SaveRow()

    Cells(lCurrentRow, 1).Value = txtCaseNo.Text

    Cells(lCurrentRow, 2).Value = txtName.Text

    Cells(lCurrentRow, 3).Value = txtMRN.Text

    Cells(lCurrentRow, 4).Value = cboKDdxconfirmed.Text

    Cells(lCurrentRow, 5).Value = txtDateofKDdx.Text

End Sub

Answer 1

See the part on following page of Greg Maxey's website about populating a list box from Excel:

http://gregmaxey.mvps.org/Populate_UserForm_ListBox.htm

Similar code can be used in Excel itself.

Answer 2

Thanks for the quick reply and website link =)