Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Lastrow = ActiveSheet.Cells(Rows.Count, 12).End(xlUp).Row
If ActiveWorkbook.Name Like "FR_*" And WorksheetFunction.CountIf(ActiveSheet.Range(Cells(4, 12), Cells(Lastrow, 12)), "<>Pending Distribution") > 0 Then MsgBox "Warning, column L has values other than Pending Distribution" Cancel = True End If End Sub
It works when it is saved into the Workbook in vba but it doesn't work in Personal.xlsb
You want the application to do the verification in all worksheets of all open workbooks, or only in the active worksheet of each open workbook, or something else?
Do you want the warning message to be generated for each anomaly, and include the name of the worksheet and the name of the workbook?
