How do you make multiple entries with multiselect on userform

W

whiskey_kind_of_night

New Member
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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
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
 
Upvote 0
Solution
Fluff said:
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
Click to expand...
Took a little bit of tweaking but it worked!!!
Thank you :)
 
Upvote 0
Glad you sorted it & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
VBA Code Error
Replies
3
Views
272
dreid1011
dreid1011
SemperFi
  • Solved
VBA Form TextBox needs formatting
Replies
1
Views
334
SemperFi
SemperFi
P
  • Question
ListBox Click to textbox & delete row
Replies
0
Views
319
pure vito
P
I
  • Solved
Cell above is selected when i duplicate row
Replies
2
Views
84
ipbr21054
I
S
  • Question
Accumulate Data from 10k+ Files. Very Slow. Issue with ActiveWorkbook.Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row
2
Replies
10
Views
645
NdNoviceHlp
NdNoviceHlp

Forum statistics

Threads
1,214,822
Messages
6,121,770
Members
449,049
Latest member
greyangel23

Share this page

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
Back
Top