How do you make multiple entries with multiselect on userform

Joined
Aug 18, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Hello forum, This is my first time using VBA. I'm attempting to make a data entry form to input trainings.
My goal is to select multiple names in order to create multiple entries with the same training information.
At the moment my form looks like this, where I choose the employee name one at a time and hit submit, which produces the data on a table. I have it so that each time it submits, only the "employee" section resets and I can quickly choose another name and submit again.
1629307429759.png

I'm trying to figure out how to change the ComboBox to a multiselect ListBox that will produce multiple entries on one table. I would also like to be able to pull the names from a specific column on a separate sheet instead of having the names as part of the code as I do now. Is this possible?


My current code is as follows

VBA Code:
Private Sub CommandButton1_Click()

''''''''''' Validation '''''''''''''



If Me.OptionButton1.Value = False And Me.OptionButton2.Value = False And Me.OptionButton3.Value = False Then
    MsgBox "Please select completed option", vbCritical
    Exit Sub
End If

If Me.ComboBox1.Value = "" Then
    MsgBox "Please select an employee.", vbCritical
    Exit Sub
End If

If VBA.IsDate(Me.TextBox3.Value) = False Then
    MsgBox "Please enter a date", vbCritical
    Exit Sub
End If

''''''''''''''''''''
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Data")
Dim n As Long
_____________

'''''''''''''''''' Enter the date



n = sh.Range("C" & Application.Rows.Count).End(xlUp).Row



sh.Range("D" & n + 1).Value = Me.TextBox1.Value
sh.Range("E" & n + 1).Value = Me.TextBox2.Value

'Completed

If Me.OptionButton1.Value = True Then
    sh.Range("I" & n + 1).Value = "Yes"
End If

If Me.OptionButton2.Value = True Then
    sh.Range("I" & n + 1).Value = "No"
End If

If Me.OptionButton3.Value = True Then
    sh.Range("I" & n + 1).Value = "Not Required"
End If

'Type of Training

If Me.OptionButton4.Value = True Then
    sh.Range("G" & n + 1).Value = "EHS"
End If

If Me.OptionButton6.Value = True Then
    sh.Range("G" & n + 1).Value = "QMS"
End If

If Me.OptionButton5.Value = True Then
    sh.Range("G" & n + 1).Value = "PPG"
End If

If Me.OptionButton7.Value = True Then
    sh.Range("G" & n + 1).Value = "Other"
End If


sh.Range("C" & n + 1).Value = Me.ComboBox1.Value
sh.Range("H" & n + 1).Value = Me.TextBox3.Value
sh.Range("F" & n + 1).Value = Me.TextBox4.Value

'''''''''' Clear boxes


Me.ComboBox1.Value = ""


End Sub
____________

Private Sub CommandButton2_Click()
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""

Me.OptionButton1.Value = False
Me.OptionButton2.Value = False
Me.OptionButton3.Value = False
Me.OptionButton4.Value = False
Me.OptionButton5.Value = False
Me.OptionButton6.Value = False
Me.OptionButton7.Value = False

Me.ComboBox1.Value = ""

End Sub
__________


Private Sub UserForm_Activate()

With Me.ComboBox1
.Clear
.AddItem ""
'''''List of employee names added here


End With

End Sub


Thank you in advance!
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,728
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Private Sub CommandButton1_Click()

''''''''''' Validation '''''''''''''



If Me.OptionButton1.Value = False And Me.OptionButton2.Value = False And Me.OptionButton3.Value = False Then
    MsgBox "Please select completed option", vbCritical
    Exit Sub
End If

If Me.ComboBox1.Value = "" Then
    MsgBox "Please select an employee.", vbCritical
    Exit Sub
End If

If VBA.IsDate(Me.TextBox3.Value) = False Then
    MsgBox "Please enter a date", vbCritical
    Exit Sub
End If

''''''''''''''''''''
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Data")
Dim n As Long, i As Long

'''''''''''''''''' Enter the date



n = sh.Range("C" & Application.Rows.Count).End(xlUp).Row


For i = 0 To Me.ListBox1.ListCount - 1
   If Me.ListBox1.Selected(i) Then
      sh.Range("D" & n + 1).Value = Me.TextBox1.Value
      sh.Range("E" & n + 1).Value = Me.TextBox2.Value
      
      'Completed
      
      If Me.OptionButton1.Value = True Then
          sh.Range("I" & n + 1).Value = "Yes"
      End If
      
      If Me.OptionButton2.Value = True Then
          sh.Range("I" & n + 1).Value = "No"
      End If
      
      If Me.OptionButton3.Value = True Then
          sh.Range("I" & n + 1).Value = "Not Required"
      End If
      
      'Type of Training
      
      If Me.OptionButton4.Value = True Then
          sh.Range("G" & n + 1).Value = "EHS"
      End If

      If Me.OptionButton6.Value = True Then
          sh.Range("G" & n + 1).Value = "QMS"
      End If

      If Me.OptionButton5.Value = True Then
          sh.Range("G" & n + 1).Value = "PPG"
      End If

      If Me.OptionButton7.Value = True Then
          sh.Range("G" & n + 1).Value = "Other"
      End If
      
      
      sh.Range("C" & n + 1).Value = Me.ListBox1.List(i)
      sh.Range("H" & n + 1).Value = Me.TextBox3.Value
      sh.Range("F" & n + 1).Value = Me.TextBox4.Value
      Me.ListBox1.Selected(i) = False
      n = n + 1
   End If
Next i

End Sub
And to load the listbox you can use something like
VBA Code:
Private Sub UserForm_Activate()
   With Sheets("List")
      Me.ListBox1.List = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value
   End With
      
End Sub
 
Solution
Joined
Aug 18, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
How about
VBA Code:
Private Sub CommandButton1_Click()

''''''''''' Validation '''''''''''''



If Me.OptionButton1.Value = False And Me.OptionButton2.Value = False And Me.OptionButton3.Value = False Then
    MsgBox "Please select completed option", vbCritical
    Exit Sub
End If

If Me.ComboBox1.Value = "" Then
    MsgBox "Please select an employee.", vbCritical
    Exit Sub
End If

If VBA.IsDate(Me.TextBox3.Value) = False Then
    MsgBox "Please enter a date", vbCritical
    Exit Sub
End If

''''''''''''''''''''
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Data")
Dim n As Long, i As Long

'''''''''''''''''' Enter the date



n = sh.Range("C" & Application.Rows.Count).End(xlUp).Row


For i = 0 To Me.ListBox1.ListCount - 1
   If Me.ListBox1.Selected(i) Then
      sh.Range("D" & n + 1).Value = Me.TextBox1.Value
      sh.Range("E" & n + 1).Value = Me.TextBox2.Value
     
      'Completed
     
      If Me.OptionButton1.Value = True Then
          sh.Range("I" & n + 1).Value = "Yes"
      End If
     
      If Me.OptionButton2.Value = True Then
          sh.Range("I" & n + 1).Value = "No"
      End If
     
      If Me.OptionButton3.Value = True Then
          sh.Range("I" & n + 1).Value = "Not Required"
      End If
     
      'Type of Training
     
      If Me.OptionButton4.Value = True Then
          sh.Range("G" & n + 1).Value = "EHS"
      End If

      If Me.OptionButton6.Value = True Then
          sh.Range("G" & n + 1).Value = "QMS"
      End If

      If Me.OptionButton5.Value = True Then
          sh.Range("G" & n + 1).Value = "PPG"
      End If

      If Me.OptionButton7.Value = True Then
          sh.Range("G" & n + 1).Value = "Other"
      End If
     
     
      sh.Range("C" & n + 1).Value = Me.ListBox1.List(i)
      sh.Range("H" & n + 1).Value = Me.TextBox3.Value
      sh.Range("F" & n + 1).Value = Me.TextBox4.Value
      Me.ListBox1.Selected(i) = False
      n = n + 1
   End If
Next i

End Sub
And to load the listbox you can use something like
VBA Code:
Private Sub UserForm_Activate()
   With Sheets("List")
      Me.ListBox1.List = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value
   End With
     
End Sub
Took a little bit of tweaking but it worked!!!
Thank you :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,728
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Forum statistics

Threads
1,147,745
Messages
5,742,954
Members
423,767
Latest member
dafydd_jwc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top