CODEWITHSUNDEEP
vbaexcel
whydoieven
whydoieven

Reputation: 599

Check if mandatory fields are not empty before each Save in Macro

I have a scenario in which I have to check if Column A is not empty, corresponding row values in other columns should not be empty. If they are, I want an error message to be thrown.

So I came up with this. 

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

    Dim rngCell As Range
    Dim lngLstRow As Long, lngTCols As Long
    Dim lngRowCheck(1 To 5) As String

    For Each rngCell In Range("A1:A" & lngLstRow)
        If Not IsEmpty(rngCell.Value) Then

            lngRowCheck(1) = "C"
            lngRowCheck(2) = "F"
            lngRowCheck(3) = "G"
            lngRowCheck(4) = "J"

            lngLstRow = ActiveSheet.UsedRange.Rows.Count

            For i = 1 To UBound(lngRowCheck)
                For Each rngCell In Range(lngRowCheck(i) & "2:" & lngRowCheck(i) & lngLstRow)
                    If rngCell.Value = 0 Then
                        MsgBox ("Please enter a name in cell " & rngCell.Address)
                        rngCell.Select
                    End If
                Next
            Next i
    Next
End Sub

However, this doesn't work and the validation doesn't happen before each save and I'm not able to debug this as well. Any help? Am I doing something wrong?

Upvotes: 0

Views: 1138

Answers (1)

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

Try something like this...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
Dim rngCell As Range, cell As Range
Dim lngLstRow As Long, lngTCols As Long
Dim lngRowCheck(1 To 5) As String

lngLstRow = ActiveSheet.UsedRange.Rows.Count

For Each rngCell In Range("A1:A" & lngLstRow)
    If Not IsEmpty(rngCell.Value) Then
        lngRowCheck(1) = "C"
        lngRowCheck(2) = "F"
        lngRowCheck(3) = "G"
        lngRowCheck(4) = "J"

        For i = 1 To UBound(lngRowCheck)
            For Each cell In Range(lngRowCheck(i) & "2:" & lngRowCheck(i) & lngLstRow)
                If cell.Value = 0 Then
                    MsgBox "Please enter a name in cell " & cell.Address(0, 0)
                    cell.Select
                    Cancel = True
                    Exit Sub
                End If
            Next cell
        Next i
    End If
Next rngCell

End Sub

Upvotes: 1

Related Questions