Userform to Not Enter Info if Null Values

[wmunsey81]

Hello all,

Trying to make a userform enter data in a log but I need to force the user to fill out all information or it will not enter data into the table. Below is the code I have presently:

Private Sub CommandButton_Add_Click()
Dim the_sheet As Worksheet
Dim table_list_object As ListObject
Dim table_object_row As ListRow


Set the_sheet = Sheets("2019")
Set table_list_object = the_sheet.ListObjects(1)
Set table_object_row = table_list_object.ListRows.Add






'check for a Name number
If Trim(Me.TextBox_Time.Value) = "" Then
  Me.TextBox_Time.SetFocus
  MsgBox "Please complete the form"
  Exit Sub
End If


'copy the data to the database


table_object_row.Range(1, 1).Value = Date
table_object_row.Range(1, 2).Value = Me.TextBox_Time.Value
table_object_row.Range(1, 3).Value = Me.TextBox_Order.Value
table_object_row.Range(1, 4).Value = Me.ComboBox_Venue.Value
table_object_row.Range(1, 6).Value = Me.TextBox_Driver.Value
table_object_row.Range(1, 7).Value = Me.ListBox_Problem.Value
table_object_row.Range(1, 8).Value = Me.TextBox_Hours.Value
table_object_row.Range(1, 10).Value = Me.TextBox_Detail.Value




'check for a Name number
If Trim(Me.TextBox_Time.Value) = "" Then
  Me.TextBox_Time.SetFocus
  MsgBox "Please complete the form"
  Exit Sub
End If




MsgBox "Transportation Issue Added", vbOKOnly + vbInformation, "Transportation Issue Added"
'clear the data


Me.TextBox_Time.Value = ""
Me.TextBox_Order.Value = ""
Me.ComboBox_Venue.Value = ""
Me.TextBox_Driver.Value = ""
Me.ListBox_Problem.Value = ""
Me.TextBox_Hours.Value = ""
Me.TextBox_Detail.Value = ""
Me.TextBox_Time.SetFocus
End Sub


Private Sub CommandButton_Close_Click()
Unload Me
End Sub

A simple test is to just click the submit button. It does tell you to complete the form, but enters a blank line (besides the formulated columns that include on the table). Any pointers would be greatly appreciated.

 

[Rosen]

Move the lines

Set the_sheet = Sheets("2019")
Set table_list_object = the_sheet.ListObjects(1)
Set table_object_row = table_list_object.ListRows.Add​

To after the validation check and see if that fixes your problem.
​

 

[wmunsey81]

That seems to be it... now I will just add validation for each field and do a 'SetFocus' on items not filled out. Thank you. SOLVED

 

[wmunsey81]

Actually... nope, came back now with a run time error 91 pointing at the 'copy the data to database' section. Any ideas on why? What am I not declaring correctly?

 

[wmunsey81]

Present code is as is:

Private Sub CommandButton_Add_Click()


'declare objects

Dim the_sheet As Worksheet
Dim table_list_object As ListObject
Dim table_object_row As ListRow


'copy the data to the database

table_object_row.Range(1, 1).Value = Date
table_object_row.Range(1, 2).Value = Me.TextBox_Time.Value
table_object_row.Range(1, 3).Value = Me.TextBox_Order.Value
table_object_row.Range(1, 4).Value = Me.ComboBox_Venue.Value
table_object_row.Range(1, 6).Value = Me.TextBox_Driver.Value
table_object_row.Range(1, 7).Value = Me.ListBox_Problem.Value
table_object_row.Range(1, 8).Value = Me.TextBox_Hours.Value
table_object_row.Range(1, 10).Value = Me.TextBox_Detail.Value


'Check Validation of Completed form

If Trim(Me.TextBox_Time.Value) = "" Then
  Me.TextBox_Time.SetFocus
  MsgBox "Please fill in Time on form"
  Exit Sub
End If

If Trim(Me.TextBox_Order.Value) = "" Then
  Me.TextBox_Order.SetFocus
  MsgBox "Please fill in 'Order #' on form"
  Exit Sub
End If

If Trim(Me.ComboBox_Venue.Value) = "" Then
  Me.ComboBox_Venue.SetFocus
  MsgBox "Please fill in 'Venue Location #' on form"
  Exit Sub
End If

If Trim(Me.TextBox_Driver.Value) = "" Then
  Me.TextBox_Driver.SetFocus
  MsgBox "Please fill in 'Driver' on form"
  Exit Sub
End If

If Trim(Me.ListBox_Problem.Value) = "" Then
  Me.ListBox_Problem.SetFocus
  MsgBox "Please fill in 'Problem Category' on form"
  Exit Sub
End If

If Trim(Me.TextBox_Hours.Value) = "" Then
  Me.TextBox_Hours.SetFocus
  MsgBox "Please fill in 'Hours Impact' on form"
  Exit Sub
End If

If Trim(Me.TextBox_Detail.Value) = "" Then
  Me.TextBox_Detail.SetFocus
  MsgBox "Please fill in 'Problem Detail' on form"
  Exit Sub
End If


'Set objects in worksheet

Set the_sheet = Sheets("2019")
Set table_list_object = the_sheet.ListObjects(1)
Set table_object_row = table_list_object.ListRows.Add


'Message box when data has been correctly added

MsgBox "Transportation Issue Added", vbOKOnly + vbInformation, "Transportation Issue Added"


'clear the data

Me.TextBox_Time.Value = ""
Me.TextBox_Order.Value = ""
Me.ComboBox_Venue.Value = ""
Me.TextBox_Driver.Value = ""
Me.ListBox_Problem.Value = ""
Me.TextBox_Hours.Value = ""
Me.TextBox_Detail.Value = ""
Me.TextBox_Time.SetFocus
End Sub


Private Sub CommandButton_Close_Click()
Unload Me
End Sub

 

[Rosen]

The copy data to database section should be after the set object section

Your order should be

Declare variables
Validate data
Set objects
Add data