Validation script

[efarley]

I am creating vba script that runs on the Workbook_BeforeSave Declaration, which validates several things in the file. I have the validations working correctly, but the file still saves, even if the errors aren't corrected.

I want to ensure that the file will not save unless the corrections are made. So I would like to exit the routine when an invalid entry message box triggers, and disable save, so that the user is forced to save again which will run the entire routine again. This would force them to fix their errors, and cause the whole routine to run through successfully before the file will save successfully. I am just not sure how to do this.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

[COLOR=#008000]'Validate Materials[/COLOR]

Dim Sh As Worksheet
Set Sh = ThisWorkbook.Sheets("Sheet1")

Dim RecordCount As Long
RecordCount = Sh.Range("B13", Sh.Range("B13").End(xlDown)).Rows.Count

Dim Index As Long
Dim Material As Range

Index = 0
Set Material = Sh.Range("H13")
Do Until Index = RecordCount

   If Material <> "" Then
   Material.Offset(0, 15).Select
    If Selection = "" Or Selection.Offset(0, 1) = "" Then
    MsgBox "UOM and Quantity are required with Materials", vbCritical, "Check UOM and Qty"
    End If
   End If
   
   Set Material = Material.Offset(1, 0)
   Index = Index + 1
Loop

[COLOR=#008000]'Validate Totals[/COLOR]

Dim Total As Integer

Total = Application.WorksheetFunction.Sum(Range("Q13:Q" & RecordCount))

If Total <> 0 Then
    MsgBox "Debits and Credits do not equal zero", vbCritical, "Check Debits and Credits"
End If


[COLOR=#008000]'Validate Reference[/COLOR]

Dim Reference As Range
Set Reference = Sh.Range("Z13")

Index = 0

Do Until Index = RecordCount
Reference.Select

If Len(Reference) > 16 Then
    MsgBox "Reference, (column Z), cannot exceed 16 characters, please correct this", vbCritical, "Check Reference"
End If
   Set Reference = Reference.Offset(1, 0)
   Index = Index + 1
Loop

End Sub

 

[mumps]

Try:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    'Validate Materials
    Dim Sh As Worksheet
    Set Sh = ThisWorkbook.Sheets("Sheet1")
    Dim RecordCount As Long
    RecordCount = Sh.Range("B13", Sh.Range("B13").End(xlDown)).Rows.Count
    Dim Index As Long
    Dim Material As Range
    Index = 0
    Set Material = Sh.Range("H13")
    Do Until Index = RecordCount
       If Material <> "" Then
        Material.Offset(0, 15).Select
            If Selection = "" Or Selection.Offset(0, 1) = "" Then
                MsgBox "UOM and Quantity are required with Materials", vbCritical, "Check UOM and Qty"
                Cancel = True
                Exit Sub
            End If
       End If
       Set Material = Material.Offset(1, 0)
       Index = Index + 1
    Loop
    'Validate Totals
    Dim Total As Integer
    Total = Application.WorksheetFunction.Sum(Range("Q13:Q" & RecordCount))
    If Total <> 0 Then
        MsgBox "Debits and Credits do not equal zero", vbCritical, "Check Debits and Credits"
        Cancel = True
        Exit Sub
    End If
    'Validate Reference
    Dim Reference As Range
    Set Reference = Sh.Range("Z13")
    Index = 0
    Do Until Index = RecordCount
        Reference.Select
        If Len(Reference) > 16 Then
            MsgBox "Reference, (column Z), cannot exceed 16 characters, please correct this", vbCritical, "Check Reference"
            Cancel = True
            Exit Sub
        End If
        Set Reference = Reference.Offset(1, 0)
        Index = Index + 1
    Loop
End Sub

 

[efarley]

This did not work...it exits the routine, but it still saves the file, and now it doesn't trigger the message box.

 

[efarley]

My error, I added it before the wrong end if. Moved it up and it worked like a charm...thanks.

 

[mumps]

You are very welcome.