UserForm VBA with Data List Drop-downs, Check Boxes and Search Box

[powerpivotlegal]

Hello,

After watching many tutorials on VBA UserForms and simple Data Entry Excel Forms, I am in way over my head. I think I can create the layout for the customized form with command buttons, text boxes, check boxes, but I have no idea what code is needed to make it all work.

Essentially, I need a UserForm with 5 data entry points.

1) Project Name (Text Box)
2) Supervisor Name (List Box or Drop Down of 4 names - limit to one selection)
3) Assigned Team Member(s) (check box of 15 names to allow for multiple selections)
4) Status (List of 4 options)
5) Comments (text box)

The UserForm would populate a datasheet in a separate tab with Column Headings of the data entry points.

For example, if someone entered the following:
1) Project A
2) Supervisor A
3) Team Member A, Team Member B
4) In Process
5) Due date extended until May

The datasheet would populate 2 rows - (row 1 data would be for Team Member A with data from points 1,2, 4 and 5 and row 2 data would be for Team Member B with same data of 1,2,4 and 5).

The UserForm would need a Search function to search by Project or Supervisor. The UserForm would also need to be have capability to easily update or delete previous entries. For example, delete Project A entirely from datasheet or add Team Member C to Project A.

Any help to get me started or pointed in the right direction would be greatly appreciated.

Best,
JB

 

[Norie]

Do you have anything at all so far?

PS For the list of assigned team members I would suggest a listbox set up to display a checkbox next to each item rather than multiple, individual checkboxes.

 

[powerpivotlegal]

I only have a draft of the UserForm in Excel where I've changed the underlying captions of the labels, text boxes, command buttons, etc.

 

[Norie]

Well the first part sounds pretty straightforward, if you were to use a multiselect listbox.

Something like this perhaps, which is in this sample file.

Private Sub cmdSave_Click()
Dim wsData As Worksheet
Dim I As Long

    Set wsData = Sheets("DataSheet")

    For I = 0 To lstTeamMembers.ListCount - 1
        If lstTeamMembers.Selected(I) Then
            With wsData.Range("A" & Rows.Count).End(xlUp).Offset(1)
                .Value = lstTeamMembers.List(I)
                .Offset(, 1).Value = txtProjectName.Value
                .Offset(, 2).Value = cmbSupervisor.Value
                .Offset(, 3).Value = cmbStatus.Value
                .Offset(, 4).Value = txtComments
            End With
        End If
    Next I
    
End Sub