VBA for on save event

[RPrasad]

I have 5 columns in spreadsheet. User has to fill up 4 columns, if column A is filled up and any one of the other 3 columns(C,D or E) left blank in the blank cells it should print the message "Required Column". This event should occur when the user saves the spreadsheet. and message should appear on all cells where col A is not blank.

my previous post was similar to this but it was worksheetchange event and this requirement is completely different from that.
Please help.

 

[nirvana_]

Two events need to be updated.

1) Workbook_BeforeSave
2) Workbook_BeforeClose

Also this macro only looks for values in sheet1, not on all sheets. Hope this is what you are looking for.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim i As Integer
    i = 1
    Do While Worksheets(1).Cells(i, 1) <> ""
        If Worksheets(1).Cells(i, 2) = "" Or Worksheets(1).Cells(i, 3) = "" Or Worksheets(1).Cells(i, 4) = "" Then
            MsgBox ("Row number: " & i & " has not been filled up correctly")
            MsgBox ("Columns B,C and D cannot be blank for row:= " & i)
            Cancel = True
            Exit Sub
        End If
        i = i + 1
    Loop
End Sub
 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim i As Integer
    i = 1
    Do While Worksheets(1).Cells(i, 1) <> ""
        If Worksheets(1).Cells(i, 2) = "" Or Worksheets(1).Cells(i, 3) = "" Or Worksheets(1).Cells(i, 4) = "" Then
            MsgBox ("Row number: " & i & " has not been filled up correctly")
            MsgBox ("Columns B,C and D cannot be blank for row:= " & i)
            Cancel = True
            Exit Sub
        End If
        i = i + 1
    Loop
End Sub