Calling macro via IF statement when range values are present

[GW89]

So I am working on a user form for our plant production process that allows the user to simply polulate needed cells and submit those values to a database and have the for reset for the next set of values. I have all functions working for the physical submittal of these values as well as the form reset though I am in process of placing checks and balances to verify that all required cells have information within them before the user is able to submit the form. I have successfully written code to check each individual cell and pop up a message if one of the values is missing, however, I cannot figure out the remaining portion of the code in which the form in its entirety is reviewed and then submitted. To try and make things easier for the complete form review I have allocated a range on a secondary sheet for the required values on the user form so that my Range is together instead of multiple sporadic cells across the form. I am using this range when trying to run my IF statement. Below is my current code, note that I have two different sections of code that I have tried. One is successful at stopping the sub it information is missing though will not follow through when complete and the other will not stop when information is missing but will follow through the submittal process.

Sub Data_Check()

'----- Checks for blank entries in required cells individually

'General Information Values
If Range("Batch_Number") = Empty Then
MsgBox "Enter Batch Number!"
End If

If Range("Part_Number") = Empty Then
MsgBox "Enter Part Number!"
End If

If Range("Laser_Number") = Empty Then
MsgBox "Enter Laser Number!"
End If

If Range("Operator_Number") = Empty Then
MsgBox "Enter Operator Number!"
End If

If Range("Shift") = Empty Then
MsgBox "Enter Shift!"
End If
' Bore Hole Values 1-3
If Range("BoreHole_Head1") = Empty Then
MsgBox "Bore Hole Head 1 Missing!"
End If

If Range("BoreHole_Head2") = Empty Then
MsgBox "Bore Hole Head 2 Missing!"
End If

If Range("BoreHole_Head3") = Empty Then
MsgBox "Bore Hole Head 3 Missing!"
End If
' Knockout Values 1-3
If Range("Knockout_Head1") = Empty Then
MsgBox "Knockout Head 1 Missing!"
End If

If Range("Knockout_Head2") = Empty Then
MsgBox "Knockout Head 2 Missing!"
End If

If Range("Knockout_Head3") = Empty Then
MsgBox "Knockout Head 3 Missing!"
End If
'Flatness Values 1-3
If Range("Flatness_Head1") = Empty Then
MsgBox "Flatness Head 1 Missing!"
End If

If Range("Flatness_Head2") = Empty Then
MsgBox "Flatness Head 2 Missing!"
End If

If Range("Flatness_Head3") = Empty Then
MsgBox "Flatness Head 3 Missing!"
End If
'Profile Shift Values 1-3
If Range("PSM_Head1") = Empty Then
MsgBox "Profile Shift Head 1 Missing!"
End If

If Range("PSM_Head2") = Empty Then
MsgBox "Profile Shift Head 2 Missing!"
End If

If Range("PSM_Head3") = Empty Then
MsgBox "Profile Shift Head 3 Missing!"
End If


'-------- Checks User Form for Empty Cells as a whole

'Call sheet in which grouped range is located
ws_output1 = "Info_Page"

'***This section(1) works for the message box to keep the sub routine from running if
' missing data but will not call next subroutine if data complete.

Dim Cell As Range
Dim CellsEmpty As Boolean
CellsEmpty = True

For Each Cell In ThisWorkbook.Sheets(ws_output1).Range("L4:L20")

MsgBox "Look"

If Cell.Value <> "0" Then
CellsEmpty = True
MsgBox "Exit"
Exit Sub
'Works to here if missing data
End If

Next
If CellsEmpty = False Then
MsgBox "False"
Call Data_Input
MsgBox "Call Data Input"
End If

'***This section(2) works for submitting data but if value missing does not stop
' routine nor display message box.

'If Sheets(ws_output1).Range("L4:L20") <> "" Then
' MsgBox "MISSING DATA!!!"
' Else
' Call Data_Input
' End If

End Sub

 

[GW89]

Answer to my own problem. I wound up creating a column in my table in which I created an IF statement on the sheet to identify the current value to an integer of 1 or 0. 1 being True and 0 representing False. Here is my final code for the lower portion.

' ----- Checks for Empty Cells as a whole

ws_output1 = "Info_Page"
Dim cell As Range
For Each cell In Sheets(ws_output1).Range("M4:M20")
If cell.Value = "0" Then
Exit Sub
ElseIf cell.Value = "1" Then
End If

Next cell

Call Data_Input

End Sub

Here is the table that existed on my "Info_Page" worksheet.

So what this portion of the code is doing is looking through each row within the Output column. So what I had to do first was identify the location of the table as well as the table range that I am specifically reviewing [Code Lines 1-3]. Once the data has been identified I wanted to go through each Output cell by cell. Using the "If cell.Value" line allows me to do just that. Now I wanted the sub to stop if any cell was left blank on the user form, which was converted to a 0 within the Cell Value column of the table. I then used a simple IF statement in the worksheet itself to create the Output Integer of a 0 or 1 [=If(cell value>0, 1,0)]. This allowed me to pull the required information for the VBA side as an integer. What this code does between the "For Each" line and "Next" line is similar to a loop as this code causes the contents in between to run through each cell specified in the range one at a time. Once complete it exits the loop like sequence and moves onto the next line of code. Now in the range example shown below it would run through the first 3 rows of the table and then stop because row 4 has an output of "0" which when found to be true triggers the Exit Sub line. If all output values are "1" it will complete the "For Each" loop for each cell and then call the next sub "Data_Input".

I hope this explaination hopes someone else later on down the line.

 

[NoSparks]

Unless I'm mistaken, all this is to make sure the textbox and combobox controls on the userform aren't empty.
If I've got that right, another approach would be to incorporate code along the lines of this
at the beginning of the userform sub that writes the data to the sheets.

    Dim controlNames As Variant
    Dim i As Integer
   
'check form for blanks
controlNames = Array("nametxt", "phonetxt", "bldgtxt", "roomtxt", "codetxt", "resttxt", "explaintxt", "stafftxt")
For i = LBound(controlNames) To UBound(controlNames)
    If Len(Me.Controls(controlNames(i)).Text) = 0 Then
        MsgBox controlNames(i) & "  is blank."
        Me.Controls(controlNames(i)).SetFocus
        Exit Sub
    End If
Next i

Not my code, saw it as a solution in the forums quite some time ago and tucked it away for future reference.