Mandatory fields with blank rows

[three482]

Hi all.

Macro novice here - I’ve added a macro to a that requires all fields to be completed before it will allow the user to close the file. Unfortunately, this is a file that is continually added to, so I need additional code that will exclude blank rows:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim cell As Range

For Each cell In Range(Sheet1.Cells(2, 1), Sheet1.Cells(60, 3))
If isempty(cell.Value) = True Then
Cancel = True
MsgBox “Please fill in all fields.”, vbInformation, “Missing input”
End If

Next cell

ActiveWorkbook.Save

End Sub

Thanks in advance!

 

[Cubist]

What constitutes a blank row vs. missing input?

 

[three482]

What constitutes a blank row vs. missing input?

If cells A and B have data, but not C, C would be a missing input. No entries on any row (a blank/empty row) is what I want to exclude from the macro.

 

[Cubist]

If cells A and B have data, but not C, C would be a missing input. No entries on any row (a blank/empty row) is what I want to exclude from the macro.

What if you have A, C, but not B? or any of the combinations.
Is it a blank only when all 3 cells are empty?

 

[three482]

What if you have A, C, but not B? or any of the combinations.
Is it a blank only when all 3 cells are empty?

Yes, if one cell has text, all three should. All-or-nothing on each row, basically.

 

[Cubist]

Try:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim i As Long
    Dim rowRange As Range
    Dim countFormula As String
    Dim nonBlankCount As Long
    Dim rowIncomplete As Boolean
    Dim targetRange As Range
    Dim targetSheet As Worksheet

    Set targetSheet = Sheet1
  
    ' Define the target range (adjust as needed)
    Set targetRange = targetSheet.Range("A2:C60")
    rowIncomplete = False
    For i = 1 To targetRange.Rows.Count
        ' columns A to C
        Set rowRange = targetSheet.Range(targetRange.Cells(i, 1), targetRange.Cells(i, 3))
        countFormula = "=COUNTIF(" & rowRange.Address & "," & """<>" & """" & ")"
      
        nonBlankCount = Application.Evaluate(countFormula)
        If nonBlankCount = 1 Or nonBlankCount = 2 Then
            rowIncomplete = True
            Exit For
        End If
    Next i
  
    If rowIncomplete Then
        Cancel = True
        MsgBox "There are rows with 1 or 2 blank cells. Please fill in all fields.", vbInformation, "Incomplete rows"
    Else
        ActiveWorkbook.Save
    End If
End Sub

 

[three482]

Nicely done - thanks!

I may be tasked with working on a similar form, but that data would be dependent on one cell instead of all. So, column C, for example, would require all other cells on the row to be completed, but no other cell would trigger that requirement. How would that code differ?

Also, if I wanted to highlight missing cells, would you recommend a macro or just conditional formatting?

Thanks again!

 

[Cubist]

Change this line:

 Set rowRange = targetRange.Cells(i, 3)

and

 If nonBlankCount = 0 Then

Also, if I wanted to highlight missing cells, would you recommend a macro or just conditional formatting?

Conditional formatting is probably better.