Restrict being able to record entry unless all fields on a line on userform are populated.

[sueanne]

Hi,

I have a previous thread where I have disabled textbox2 until textbox 1 is populated. I am now trying to extend this a little further so I have posted this as a new thread so it doesn't get too confusing...

Basically, if Qty 2 is entered, and not all of the other fields on that line are entered, then want a msgbox to come up when hit record to say "not all data populated" or something similar to that.Then it returns you to the screen without recording so you can complete that line entry or delete the fields that have been entered if you don't want that line anymore.
This needs to work for lines 2 - 10. Line 2 cannot start to be populated until all fields on line 1 are populated.

This data is recorded in a sales item worksheet, and I don't want partial fields on a line being populated to the worksheet. Also, if the user enters a few fields which populates total but then account code or gst is not selected on that line, the invoice value will not calculate correctly.

I have the following code in to populate the data from the userform to the worksheet, I only did this for the first 2 lines whilst I was testing it out.

I was checking Column H to see if it was empty to find the next row to populate.

What is the best way to handle this? Am I trying to do the impossible?

I will try and edit the post to add in a screen shot of the userform.

[CODE TO RECORD INFO TO SALES ITEM WORKSHEET]
Private Sub Record_click()

If Ttlamt.Value = "" Then
MsgBox "Invoice value must be greater than $0.00", vbCritical + vbOKOnly, "Invoice Value Error"
Exit Sub
End If

If D8.Value = "" Then
MsgBox "You must enter an invoice date to continue", vbCritical + vbOKOnly, "Error"
Exit Sub
End If

If cboTax1.Value = "" Then
MsgBox "You must select Tax code to save entry", vbCritical + vbOKOnly, "Error"
Exit Sub
End If


Res = MsgBox("Updating data will be saved.Do you want to continue??", vbQuestion + vbOKCancel, "Invoice Warning")
If Res = vbOK Then

Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("Accounts Receivable")
'find first row in database
irow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With ws
.Range("B" & irow) = CustomerNo.Value
.Range("A" & irow) = cboCustomer.Value
.Range("C" & irow) = InvoiceNo.Value
.Range("E" & irow) = Subttl.Value
.Range("F" & irow) = Taxamt.Value
.Range("G" & irow) = Ttlamt.Value
.Range("H" & irow) = DueD8.Value
.Range("J" & irow) = Ttlamt.Value
.Range("L" & irow) = CustomerMsg.Value
.Range("D" & irow) = D8.Value
ThisWorkbook.Save
End With


Dim ws1 As Worksheet
Set ws1 = Worksheets("Sales Item")
'find first row in database
irow = ws1.Cells(Rows.Count, 8).End(xlUp).Offset(1, 0).Row
With ws1
.Range("B" & irow) = CustomerNo.Value
.Range("A" & irow) = cboCustomer.Value
.Range("C" & irow) = InvoiceNo.Value
.Range("D" & irow) = Qty1.Value
.Range("E" & irow) = Desc1.Value
.Range("F" & irow) = UP1.Value
.Range("G" & irow) = Disc1.Value
.Range("H" & irow) = Total1.Value
.Range("I" & irow) = cboAC1.Value
.Range("J" & irow) = cboTax1.Value
ThisWorkbook.Save
End With

Dim ws2 As Worksheet
Set ws2 = Worksheets("Sales Item")
'find first row in database
irow = ws2.Cells(Rows.Count, 8).End(xlUp).Offset(1, 0).Row
With ws2
.Range("B" & irow) = CustomerNo.Value
.Range("A" & irow) = cboCustomer.Value
.Range("C" & irow) = InvoiceNo.Value
.Range("D" & irow) = Qty2.Value
.Range("E" & irow) = Desc2.Value
.Range("F" & irow) = UP2.Value
.Range("G" & irow) = Disc2.Value
.Range("H" & irow) = Total2.Value
.Range("I" & irow) = cboAC2.Value
.Range("J" & irow) = cboTax2.Value
ThisWorkbook.Save
End With


Me.Record.Enabled = False
End If

If vbCancel Then
Me.Record.Enabled = True
Exit Sub
End If

End Sub


[CODE TO RESTRICT ENTRY IN FIELDS - I HAVE ONLY PASTED IN THE FIRST PART, THIS IS REPEATED FOR ALL BOXES 1-10]
Function TBox(Nam As String) As Boolean
Dim TB As Variant
Dim n As Integer
Dim Num As Integer


TB = Array("Qty1", "Desc1", "UP1", "cboAC1", "cboTax1", "Qty2", "Desc2", "UP2", "cboAC2", "cboTax2", "Qty3", "Desc3", "UP3", "cboAC3", "cboTax3", "Qty4", "Desc4", "UP4", "cboAC4", "cboTax4", "Qty5", "Desc5", "UP5", "cboAC5", "cboTax5", "Qty6", "Desc6", "UP6", "cboAC6", "cboTax6", "Qty7", "Desc7", "UP7", "cboAC7", "cboTax7", "Qty8", "Desc8", "UP8", "cboAC8", "cboTax8", "Qty9", "Desc9", "UP9", "cboAC9", "cboTax9", "Qty10", "Desc10", "UP10", "cboAC10", "cboTax10")
If Not Nam = "Texbox1" Then
For n = 1 To UBound(TB)
If TB = Nam Then Num = n - 1: Exit For
Next n
End If
For n = 0 To Num
If Me.Controls(TB).Object.Value = vbNullString Then TBox = True: Exit For
Next n
End Function

Private Sub Desc1_Change()
If TBox("Desc1") Then Desc1 = ""
End Sub
Private Sub UP1_Change()
If TBox("UP1") Then UP1 = ""
End Sub
Private Sub cboAC1_Change()
If TBox("cboAC1") Then cboAC1 = ""
End Sub
Private Sub cboTax1_Change()
If TBox("cboTax1") Then cboTax1 = ""
End Sub
Private Sub Qty2_Change()
If TBox("Qty2") Then Qty2 = ""
End Sub

 

[sueanne]

I am trying to upload an image of the tax invoice but not having much luck...basically its is 7 columns and 10 rows.

 

[sueanne]

It is ok, I have worked out how to do this.