CODEWITHSUNDEEP
excelvba
Emartin1863
Emartin1863

Reputation: 5

VBA for Mandatory cell requirements being met still yields the error message

I have a VBA code for a new customer entry from that requires the user to fill out specific mandatory cells with the customer information before they can save the form.

The ranges for these are:

If range B8:B10 is filled in with the customer name, then all cells in the corresponding row must be filled in for columns: E, F, K, M, N, and P.

If range A13:A50 is filled in with the customer name, then all cells in the corresponding row must be filled in for columns: D, H, I, J, U.

If any of the cells are left blank, a warning message pops up telling the user that fields are missing in the corresponding row.

When the criteria is met for both ranges, no warning message pops up and the user is able to save the form.

The issue is when the criteria is met for one range (lets say range B, row 8), but not the second, the warning message says that row 8 and row 13 are missing data, even though row 8 is filled in with all required data. (See pictured, highlighted cells are not part of the code). I'm afraid this will confuse the end user.

I'm not sure if it's the message that need to be fixed, or the IF statement. I have played around with both, but no luck.

Example

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

Dim ws As Worksheet
Dim rg As Range, c As Range
Dim bCanSave As Boolean
Dim sWarning As String

Set ws = Sheets("Sheet1")
Set rg = ws.Range("B8:B10, A13:A50")
Set b = Range("B8:B10")
Set A = Range("A13:A50")
sWarning = "File not saved!" & vbNewLine & "Mandatory cells missing in rows: " & vbNewLine

With ws
    bCanSave = True
    For Each c In rg
        If Not IsEmpty(c) Then
            If .Cells(b.Row, "E") = "" Or .Cells(b.Row, "F") = "" Or _
               .Cells(b.Row, "K") = "" Or .Cells(b.Row, "M") = "" Or _
               .Cells(b.Row, "N") = "" Or .Cells(b.Row, "P") = "" Or _
               .Cells(A.Row, "D") = "" Or .Cells(A.Row, "E") = "" Or _
               .Cells(A.Row, "H") = "" Or .Cells(A.Row, "I") = "" Or _
               .Cells(A.Row, "J") = "" Or .Cells(A.Row, "U") = "" Then

                bCanSave = False
                bCanClose = False
                sWarning = sWarning & c.Row & ", "

            End If
        End If
    Next c
End With

If Not bCanSave Then
    MsgBox sWarning, vbExclamation
    Cancel = True
End If

End Sub

Upvotes: 0

Views: 77

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

You are going to need to split ranges and do two independent loops:

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

Dim ws As Worksheet
Dim a As Range, c As Range, b as range
Dim bCanSave As Boolean
Dim sWarning As String

Set ws = Sheets("Sheet1")

Set b = ws.Range("B8:B10")
Set a = ws.Range("A13:A50")
sWarning = "File not saved!" & vbNewLine & "Mandatory cells missing in rows: " & vbNewLine

With ws
    bCanSave = True
    For Each c In b
        If Not IsEmpty(c) Then
            If .Cells(c.Row, "E") = "" Or .Cells(c.Row, "F") = "" Or _
               .Cells(c.Row, "K") = "" Or .Cells(c.Row, "M") = "" Or _
               .Cells(c.Row, "N") = "" Or .Cells(b.Row, "P") = "" Then
                bCanSave = False
                bCanClose = False
                sWarning = sWarning & c.Row & ", "

            End If
        End If
    Next c
    For Each c In a
        If Not IsEmpty(c) Then
            If .Cells(c.Row, "D") = "" Or .Cells(c.Row, "E") = "" Or _
               .Cells(c.Row, "H") = "" Or .Cells(c.Row, "I") = "" Or _
               .Cells(c.Row, "J") = "" Or .Cells(c.Row, "U") = "" Then

                bCanSave = False
                bCanClose = False
                sWarning = sWarning & c.Row & ", "

            End If
        End If
    Next c
End With

If Not bCanSave Then
    MsgBox sWarning, vbExclamation
    Cancel = True
End If

Upvotes: 2

Related Questions