Thread: Required Fields

I need help again. I'm so confused. It doesn't seem like a hard concept but it definetly is tricky. I have a new customer setup for that a ton of people use and there are errors on them all the time. I'm trying to elimate these by requiring certain cells to contain data. They must not be blank. What I have right now is just alot of If/Then statements that

Code:

Sub Require()
Application.DisplayAlerts = False

If Range("D9").Value = "" Then
   MsgBox "Please enter a date."
End If

If Range("L9").Value = "" Then
   MsgBox "Please enter in the type of form this is."
End If

If Range("D13").Value = "" Then
   MsgBox "Please enter a Tax ID."
End If

If Range("D15").Value = "" Then
   MsgBox "Please enter a Customer Number or N/A if one has not yet been assigned."
End If

If Range("D17").Value = "" Then
   MsgBox "Please enter a Customer Name."
End If

If Range("D19").Value = "" Then
   MsgBox "Please enter the Billing Address."
End If

If Range("D22").Value = "" Then
   MsgBox "Please enter the billing address city."
End If

If Range("D24").Value = "" Then
   MsgBox "Please enter the billing address state."
End If

If Range("D26").Value = "" Then
   MsgBox "Please enter the billing address zip code."
End If

If Range("D28").Value = "" Then
   MsgBox "Please enter the AR Contact."
End If

If Range("D30").Value = "" Then
   MsgBox "Please enter a phone number."
End If

If Range("D32").Value = "" Then
   MsgBox "Please enter the Salesman #."
End If

If Range("J32").Value = "" Then
   MsgBox "Please enter the CSR #."
End If

If Range("D39").Value = "" Then
   MsgBox "Please enter the projected gas volume."
End If

If Range("D41").Value = "" Then
   MsgBox "Please enter the projected Diesel volume."
End If

If Range("D43").Value = "" Then
   MsgBox "Please enter the credit limit or N/A if one has not been assigned."
End If

If Range("D45").Value = "" Then
   MsgBox "Please enter the terms."
End If

If Range("M41").Value = "" Then
   MsgBox "Please enter an invoice fax number."
End If

If Range("M43").Value = "" Then
   MsgBox "Please enter an invoice e-mail address."
End If




If Range("H68").Value = "" Then
   MsgBox "Please enter your name."
End If

If Range("H69").Value = "" Then
   MsgBox "Please enter the type of form this is."
End If

If Range("D71").Value = "" Then
   MsgBox "Please enter the customer number or N/A if one has not yet been assigned."
End If

If Range("D72").Value = "" Then
   MsgBox "Please enter a ship to # or N/A if one has not yet been assigned."
End If

If Range("D74").Value = "" Then
   MsgBox "Please enter the ship to customer name."
End If

If Range("D76").Value = "" Then
   MsgBox "Please enter the delivery address."
End If

If Range("D79").Value = "" Then
   MsgBox "Please enter the delivery city."
End If

If Range("D81").Value = "" Then
   MsgBox "Please enter the delivery state."
End If

If Range("D83").Value = "" Then
   MsgBox "Please enter the delivery zip code."
End If

If Range("N96").Value = "" Then
   MsgBox "Please specify if whether the customer is billed for Net or Gross gallons."
End If

If Range("E104").Value = "" Then
   MsgBox "Please specify the pricing method/formula."
End If

If Range("C108").Value = "" Then
   MsgBox "Please enter a dispatch phone number."
End If

If Range("C109").Value = "" Then
   MsgBox "Please enter a dispatch contact person."
End If

If Range("D112").Value = "" Then
   MsgBox "Please enter the hours of delivery."
End If

If Range("D113").Value = "" Then
   MsgBox "Please enter the days of delivery."
   
End If
End Sub

So it works to the point that if one of those cells is blank, it tells you what you must enter. What I would like for it to do is to go through each field and check to see if there is data there, and if there isn't, I'd like to to stop at that field so that the person could type what they needed there. And once they finished typing they'd try and send the form again with another macro I have and it would repeat the process of checking. I would like it to continue runnin my macro called "FinalMacro" IF all of the fields I need to contain data, do.

It sounds confusing and let me know if I need to clarify something. Thanks a bunch and you guys are amazing!!!

-Jerad

Try this:

Code:

Sub test()

Dim r, c As Range

Set r = Range("D9,L9,D13,D15,D17,D19,D22,D24,D26,D28,D30,D32,J32,D39,D41,D43,D45,M41,M43,H68,H69,D71,D72,D74,D76,D79,D81,D83,N96,E104,C108,C109,D112,D113")

If Not IsEmpty(r) Then
    For Each c In r
        If c.Value = "" Then
            c.Select
            MsgBox "This is a Required Field"
            Exit Sub
        End If
    Next c
End If

End Sub

ska

Thank you so much! It works! I'm still trying to figure out how when it ends it will go to that specific field. I think you wrote it in your code, I'm going back to check now. Thanks again! You're great!

This will check each of the requiredCells and, if it is empty, prompt the user for the correct info and put it into the cell required.

Code:

Sub test()
Dim requiredCells As Variant
Dim prompts As Variant
Dim i As Long

 requiredCells = Array("a1", "b2", "c3")
 prompts = Array("name", "address", "phone")

 For i = LBound(requiredCells) To UBound(requiredCells)
     If Range(requiredCells(i)).Value = vbNullString Then
         Range(requiredCells(i)).Value = userInput(prompts(i))
         If Range(requiredCells(i)).Value = vbNullString Then Exit Sub: Rem Cancel pressed
     End If
 Next i
End Sub

Function userInput(promptString As Variant) As String
Do
    userInput = Application.InputBox("You must enter a " & promptString, Type:=2)
    If userInput = "False" Then userInput = vbNullString: Exit Function
Loop Until userInput <> vbNullString
End Function