Error vba for checking if required fields are not empty.

[Phluke]

Hi, I'm looking for vba code to check if column A has a value then column C must also have a value. I would like it to prompt me before saving.

Basically its a supplier list and if a supplier name is listed in column A then the commodity they provide must be filled in column C to comply with our operational standards.

I've tried a few different methods I've found online but most are for single cell checks.

Thanks!

 

[steve the fish]

Hi. Try something like this placed in the ThisWorkbook module:

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

Dim flag as Boolean, lr as Long, i as long, arr

flag = False

With Sheets("Sheet1")
    lr = .Cells(.Rows.Count, "A").End(xlUp).Row
    arr = .Range("A1:C" & lr)
    For i = LBound(arr, 1) To UBound(arr, 1)
        If Len(arr(i, 1)) > 0 Then
            If Len(arr(i, 3)) = 0 Then
                flag = True
                Exit For
            End If
        End If
    Next
End With

If flag = True Then
    SaveAsUI = False
    Cancel = True
    MsgBox "Fill in all fields"
End If
    
End Sub

 

[Phluke]

Hi. Try something like this placed in the ThisWorkbook module:

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

Dim flag as Boolean, lr as Long, i as long, arr

flag = False

With Sheets("Sheet1")
    lr = .Cells(.Rows.Count, "A").End(xlUp).Row
    arr = .Range("A1:C" & lr)
    For i = LBound(arr, 1) To UBound(arr, 1)
        If Len(arr(i, 1)) > 0 Then
            If Len(arr(i, 3)) = 0 Then
                flag = True
                Exit For
            End If
        End If
    Next
End With

If flag = True Then
    SaveAsUI = False
    Cancel = True
    MsgBox "Fill in all fields"
End If
    
End Sub

Thanks, I'm getting into this now I'll let you know what happens!

 

[Phluke]

This seems to be working perfectly, doing some more testing but you sir are a life saver, Thank you steve the fish!