how to check data has been entered before submitting

Hi Guys

this will be a difficult one, maybe not for some but for most i would think

i currently have an email button setup, so when the person has filled in the sheet and clicks the button it sends the active worksheet to the specified email address in the code

i need an extra step in this code to check that data has been filled in, in certain cells before the email is sent

for example i have numberous cells but in cell i17 if something is entered then there must be something entered in cell i19. If there is nothing filled in cell i17 then the data validation can ignore this. Basically it needs to check that corresponding cell(s) are filled in "if" the first cell is filled in (make sense?)

i would also like it to check certain cells are filled in (mandatory cells)

here is the email button code, is there anything i can implement in that to produce the above results? or if this cannot be achieved maybe a warning box to pop up before it sends to say "Please make sure everything is filled in corrently before submitting" (something like that)

thanks for any help

Sub Mail_ActiveSheet()
'Working in 97-2007
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim Sourcewb As Workbook
    Dim Destwb As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Set Sourcewb = ActiveWorkbook

    'Copy the sheet to a new workbook
    ActiveSheet.Copy
    Set Destwb = ActiveWorkbook

    'Determine the Excel version and file extension/format
    With Destwb
        If Val(Application.Version) < 12 Then
            'You use Excel 97-2003
            FileExtStr = ".xls": FileFormatNum = -4143
        Else
            'You use Excel 2007
            'We exit the sub when your answer is NO in the security dialog that you only
            'see  when you copy a sheet from a xlsm file with macro's disabled.
            If Sourcewb.Name = .Name Then
                With Application
                    .ScreenUpdating = True
                    .EnableEvents = True
                End With
                MsgBox "Your answer is NO in the security dialog"
                Exit Sub
            Else
                Select Case Sourcewb.FileFormat
                Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
                Case 52:
                    If .HasVBProject Then
                        FileExtStr = ".xlsm": FileFormatNum = 52
                    Else
                        FileExtStr = ".xlsx": FileFormatNum = 51
                    End If
                Case 56: FileExtStr = ".xls": FileFormatNum = 56
                Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
                End Select
            End If
        End If
    End With

    '    'Change all cells in the worksheet to values if you want
    '    With Destwb.Sheets(1).UsedRange
    '        .Cells.Copy
    '        .Cells.PasteSpecial xlPasteValues
    '        .Cells(1).Select
    '    End With
    '    Application.CutCopyMode = False

    'Save the new workbook/Mail it/Delete it
    TempFilePath = Environ$("temp") & "\"
    TempFileName = "Absense Report"

    With Destwb
        .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
        On Error Resume Next
        .SendMail "", _
                  "Absence Report - " & Range("E10").Text & " / " & Range("W10").Text & " - " & Range("E13").Text
        On Error GoTo 0
        .Close SaveChanges:=False
    End With

    'Delete the file you have send
    Kill TempFilePath & TempFileName & FileExtStr

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

It would be simpler to add Data Validation to the actual cells

Hi, back again

the original sheet that this help was requested for was completed without the need to check boxes. but i have started a new form which needs to have a print button but before it prints it needs to check certain boxes/cells that data has been entered, if there has been no data enterned then it stops and prompts the user to fill in the cells. once filled they can click the print button and it prints

is this something that can be done with the code you gave previously?

thanks

If you post the workbook, I'll take a look at what you are doing.

Hi Blane

i have added the workbook

I have nearly got it to work with the code below

 

Private Sub Workbook_BeforePrint(Cancel As Boolean)

    If Sheet1.Range("E10").Value = "" Then

         MsgBox "Please Enter a First Name"

        Cancel = True

    ElseIf Sheet1.Range("M10").Value = "" Then

         MsgBox "Please Enter a Surname"

        Cancel = True

    End If

End Sub

which works fine, but again if i add any more i get an error with the below code, can you see what i have done wrong?



it says compile error block if without end if

Private Sub Workbook_BeforePrint(Cancel As Boolean)

    If Sheet1.Range("E10").Value = "" Then

         MsgBox "Please Enter a First Name"

        Cancel = True

    ElseIf Sheet1.Range("M10").Value = "" Then

         MsgBox "Please Enter a Surname"

        Cancel = True

     If Sheet1.Range("E13").Value = "" Then

         MsgBox "Please Enter a First Name"

        Cancel = True

    ElseIf Sheet1.Range("M13").Value = "" Then

         MsgBox "Please Enter a Surname"

        Cancel = True

         If Sheet1.Range("G15").Value = "" Then

         MsgBox "Please Enter a First Name"

        Cancel = True

    ElseIf Sheet1.Range("K15").Value = "" Then

         MsgBox "Please Enter a Surname"

        Cancel = True

         If Sheet1.Range("O15").Value = "" Then

         MsgBox "Please Enter a First Name"

        Cancel = True

    ElseIf Sheet1.Range("G18").Value = "" Then

         MsgBox "Please Enter a Surname"

        Cancel = True

         If Sheet1.Range("K18").Value = "" Then

         MsgBox "Please Enter a First Name"

        Cancel = True

    ElseIf Sheet1.Range("O18").Value = "" Then

         MsgBox "Please Enter a Surname"

        Cancel = True

         If Sheet1.Range("E23").Value = "" Then

         MsgBox "Please Enter a First Name"

        Cancel = True

    ElseIf Sheet1.Range("I23").Value = "" Then

         MsgBox "Please Enter a Surname"

        Cancel = True

         If Sheet1.Range("M23").Value = "" Then

         MsgBox "Please Enter a First Name"

        Cancel = True

    ElseIf Sheet1.Range("Q23").Value = "" Then

         MsgBox "Please Enter a Surname"

        Cancel = True

         If Sheet1.Range("V23").Value = "" Then

         MsgBox "Please Enter a First Name"

        Cancel = True

    ElseIf Sheet1.Range("E28").Value = "" Then

         MsgBox "Please Enter a Surname"

        Cancel = True

         If Sheet1.Range("M28").Value = "" Then

         MsgBox "Please Enter a First Name"

        Cancel = True

    ElseIf Sheet1.Range("V28").Value = "" Then

         MsgBox "Please Enter a Surname"

        Cancel = True

         If Sheet1.Range("E31").Value = "" Then

         MsgBox "Please Enter a First Name"

        Cancel = True

    ElseIf Sheet1.Range("M31").Value = "" Then

         MsgBox "Please Enter a Surname"

        Cancel = True

         If Sheet1.Range("O33").Value = "" Then

         MsgBox "Please Enter a First Name"

        Cancel = True

    ElseIf Sheet1.Range("G33").Value = "" Then

         MsgBox "Please Enter a Surname"

        Cancel = True

         If Sheet1.Range("V33").Value = "" Then

         MsgBox "Please Enter a First Name"

        Cancel = True

    ElseIf Sheet1.Range("E38").Value = "" Then

         MsgBox "Please Enter a Surname"

        Cancel = True

         If Sheet1.Range("O38").Value = "" Then

         MsgBox "Please Enter a First Name"

        Cancel = True

    ElseIf Sheet1.Range("E41").Value = "" Then

         MsgBox "Please Enter a Surname"

        Cancel = True

         If Sheet1.Range("O41").Value = "" Then

         MsgBox "Please Enter a First Name"

        Cancel = True

    ElseIf Sheet1.Range("F45").Value = "" Then

         MsgBox "Please Enter a Surname"

        Cancel = True

         If Sheet1.Range("G45").Value = "" Then

         MsgBox "Please Enter a First Name"

        Cancel = True

    ElseIf Sheet1.Range("H45").Value = "" Then

         MsgBox "Please Enter a Surname"

        Cancel = True

         If Sheet1.Range("I45").Value = "" Then

         MsgBox "Please Enter a First Name"

        Cancel = True

    ElseIf Sheet1.Range("J45").Value = "" Then

         MsgBox "Please Enter a Surname"

        Cancel = True

         If Sheet1.Range("K45").Value = "" Then

         MsgBox "Please Enter a First Name"

        Cancel = True

    ElseIf Sheet1.Range("Q44").Value = "" Then

         MsgBox "Please Enter a Surname"

        Cancel = True

         If Sheet1.Range("E49").Value = "" Then

         MsgBox "Please Enter a First Name"

        Cancel = True

    ElseIf Sheet1.Range("F49").Value = "" Then

         MsgBox "Please Enter a Surname"

        Cancel = True

    ElseIf Sheet1.Range("H49").Value = "" Then

         MsgBox "Please Enter a Surname"

        Cancel = True

         If Sheet1.Range("G49").Value = "" Then

         MsgBox "Please Enter a First Name"

        Cancel = True

    ElseIf Sheet1.Range("I49").Value = "" Then

         MsgBox "Please Enter a Surname"

        Cancel = True

         If Sheet1.Range("J49").Value = "" Then

         MsgBox "Please Enter a First Name"

        Cancel = True

    ElseIf Sheet1.Range("K49").Value = "" Then

         MsgBox "Please Enter a Surname"

        Cancel = True

         ElseIf Sheet1.Range("L49").Value = "" Then

         MsgBox "Please Enter a Surname"

        Cancel = True

    End If

End Sub

You have several places where you have an "If" rather than an "ElseIf". Replace them and you should be fine.

Except the first one.

you missed one

Option Explicit

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Sheet1.Range("E10").Value = "" Then
MsgBox "Please Enter a First Name"
Cancel = True
ElseIf Sheet1.Range("M10").Value = "" Then
MsgBox "Please Enter a Surname"
Cancel = True
ElseIf Sheet1.Range("E13").Value = "" Then
MsgBox "Please Enter a First Name"
Cancel = True
ElseIf Sheet1.Range("M13").Value = "" Then
MsgBox "Please Enter a Surname"
Cancel = True
ElseIf Sheet1.Range("G15").Value = "" Then
MsgBox "Please Enter a First Name"
Cancel = True
ElseIf Sheet1.Range("K15").Value = "" Then
MsgBox "Please Enter a Surname"
Cancel = True
ElseIf Sheet1.Range("O15").Value = "" Then
MsgBox "Please Enter a First Name"
Cancel = True
ElseIf Sheet1.Range("G18").Value = "" Then
MsgBox "Please Enter a Surname"
Cancel = True
ElseIf Sheet1.Range("K18").Value = "" Then
MsgBox "Please Enter a First Name"
Cancel = True
ElseIf Sheet1.Range("O18").Value = "" Then
MsgBox "Please Enter a Surname"
Cancel = True
ElseIf Sheet1.Range("E23").Value = "" Then
MsgBox "Please Enter a First Name"
Cancel = True
ElseIf Sheet1.Range("I23").Value = "" Then
MsgBox "Please Enter a Surname"
Cancel = True
ElseIf Sheet1.Range("M23").Value = "" Then
MsgBox "Please Enter a First Name"
Cancel = True
ElseIf Sheet1.Range("Q23").Value = "" Then
MsgBox "Please Enter a Surname"
Cancel = True
ElseIf Sheet1.Range("V23").Value = "" Then
MsgBox "Please Enter a First Name"
Cancel = True
ElseIf Sheet1.Range("E28").Value = "" Then
MsgBox "Please Enter a Surname"
Cancel = True
ElseIf Sheet1.Range("M28").Value = "" Then
MsgBox "Please Enter a First Name"
Cancel = True
ElseIf Sheet1.Range("V28").Value = "" Then
MsgBox "Please Enter a Surname"
Cancel = True
ElseIf Sheet1.Range("E31").Value = "" Then
MsgBox "Please Enter a First Name"
Cancel = True
ElseIf Sheet1.Range("M31").Value = "" Then
MsgBox "Please Enter a Surname"
Cancel = True
ElseIf Sheet1.Range("O33").Value = "" Then
MsgBox "Please Enter a First Name"
Cancel = True
ElseIf Sheet1.Range("G33").Value = "" Then
MsgBox "Please Enter a Surname"
Cancel = True
ElseIf Sheet1.Range("V33").Value = "" Then
MsgBox "Please Enter a First Name"
Cancel = True
ElseIf Sheet1.Range("E38").Value = "" Then
MsgBox "Please Enter a Surname"
Cancel = True
ElseIf Sheet1.Range("O38").Value = "" Then
MsgBox "Please Enter a First Name"
Cancel = True
ElseIf Sheet1.Range("E41").Value = "" Then
MsgBox "Please Enter a Surname"
Cancel = True
ElseIf Sheet1.Range("O41").Value = "" Then
MsgBox "Please Enter a First Name"
Cancel = True
ElseIf Sheet1.Range("F45").Value = "" Then
MsgBox "Please Enter a Surname"
Cancel = True
ElseIf Sheet1.Range("G45").Value = "" Then
MsgBox "Please Enter a First Name"
Cancel = True
ElseIf Sheet1.Range("H45").Value = "" Then
MsgBox "Please Enter a Surname"
Cancel = True
ElseIf Sheet1.Range("I45").Value = "" Then
MsgBox "Please Enter a First Name"
Cancel = True
ElseIf Sheet1.Range("J45").Value = "" Then
MsgBox "Please Enter a Surname"
Cancel = True
ElseIf Sheet1.Range("K45").Value = "" Then
MsgBox "Please Enter a First Name"
Cancel = True
ElseIf Sheet1.Range("Q44").Value = "" Then
MsgBox "Please Enter a Surname"
Cancel = True
ElseIf Sheet1.Range("E49").Value = "" Then
MsgBox "Please Enter a First Name"
Cancel = True
ElseIf Sheet1.Range("F49").Value = "" Then
MsgBox "Please Enter a Surname"
Cancel = True
ElseIf Sheet1.Range("H49").Value = "" Then
MsgBox "Please Enter a Surname"
Cancel = True
ElseIf Sheet1.Range("G49").Value = "" Then
MsgBox "Please Enter a First Name"
Cancel = True
ElseIf Sheet1.Range("I49").Value = "" Then
MsgBox "Please Enter a Surname"
Cancel = True
ElseIf Sheet1.Range("J49").Value = "" Then
MsgBox "Please Enter a First Name"
Cancel = True
ElseIf Sheet1.Range("K49").Value = "" Then
MsgBox "Please Enter a Surname"
Cancel = True
ElseIf Sheet1.Range("L49").Value = "" Then
MsgBox "Please Enter a Surname"
Cancel = True
End If
End Sub

Use a scheme like

If Sheet1.Range("E10").Value = "" Then
MsgBox "Please Enter a First Name"
Sheet1.Range("E10").Select
Cancel = True

note the line where cell E10 is selected. You would add a line like this after each call to MsgBox selected the cell of interest.

Originally Posted by techmob

is there anyway to make the cell go a colour and disappear when the user enters data?

not sure I understand what you want. How does something have a color and disappear at the same time? Maybe if you sent me a workbook that showed how you want it to look when the data has been entered correctly.

OK, I've done some stream lining for you since the number of cells to be checked was getting quite large. This code should be easier to handle. Please look at the code in the attached book.

First, the cells to be validated and the messages are put into arrays. I only did this for the first 3 cells. You will have to add all the others.

Second, all of the cells to be validated are unhighlighted.

Then each cell is validate in turn either throwing the error message and canceling the print, or highlighting the cell. Note that highlighting is done by setting the interior fill style and color. Printcheck.xls

thanks for that, clever stuff

is there anyway of it highlighting the cell when nothing is entered instead

as it is, the cell is selected, and when you put text in, and it errors again because of another cell needs completing, it then highlights the completed cell in yellow

i would prefer it the other way if it is possible, not sure it is. when you press print and no data is entered into the particular cell, it then says for you to complete the cell, selects the cell and highlights it in a colour. once you fill in the data and press print again, it goes past the newley completed cell, removes the colour highlight and prompts and selects the other to be completed cell, this resulting all completed cells with no highlited colours ready for print

thanks for your effort tho, appreciate it

change the call to Highlight to UnHiglight and the call the UnHighlight to Highlight