VBA To prevent document save unless criteria is met

[MrRosco]

Hello wonderful Mr Excel forum.

Could i have some assistance on creating VBA code to prevent a document save unless certain criteria is met.

i have four codes within a drop down which relate to invoices, however one is a general code and require information is their a way to stop the document saving unless information
is added into the comments section.

The code in question is 609110 which is located in cell H and the comments box is located in O

in addition would it also be possible to add a message box to say "Please add comment"

Thanks
Rosco

 

[mikerickson]

You could put this in the ThisWorkbook code module. (Adjust the sheet reference to suit)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
    Cancel = 0 < WorksheetFunction.CountIfs(Sheet1.Range("H:H"), 609110, Sheet1.Range("O:O"), vbNullString)
    
    If Cancel Then
        MsgBox "add a comment"
    End If
    
End Sub

 

[mikerickson]

A variation

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim badRow As Long
    badRow = Evaluate("MAX((Sheet1!H:H=609110)*(Sheet1!O:O="""")*ROW(H:H))")
    
    Cancel = 0 < badRow
    
    If Cancel Then
        Application.Goto Sheet1.Cells(badRow, "H"), Scroll:=True
        ActiveCell.EntireRow.Range("O1").Select
        MsgBox "add a comment in O" & badRow
    End If
    
End Sub

 

[MrRosco]

Thanks Mike,

I have tried using this code with no luck, I think it might need some kind of look to check all the rows within the document, as their will be more than one entry. again I don't know if this will be possible to do or not
thanks

Rosco