Previous Topic Next Topic

Excel vba to work on ALL workbooks - please help.

[Closed]
Report
-
Posts
17119
Registration date
Sunday June 8, 2008
Status
Contributor
Last seen
October 19, 2021
 -
Dear valuable members,

I have this code: 

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

Could anyone kindly please help me in this?

Thanks in advance,

1 reply

Answer 1 / 1
Posts
17119
Registration date
Sunday June 8, 2008
Status
Contributor
Last seen
October 19, 2021
 5
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?
Ask a question