CODEWITHSUNDEEP
vbaexcel
Muffin
Muffin

Reputation: 5

VBA code to work on ALL workbooks

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

I want to make it work on ALL workbook that start with FR_ , but it is not working despite I am using ActiveSheet and ActiveWorkbook, why ?

Upvotes: 0

Views: 156

Answers (1)

Nathan_Sav
Nathan_Sav

Reputation: 8531

using the personal like this should help

Public WithEvents CUSTOM_EXCEL As Excel.Application

Private Sub CUSTOM_EXCEL_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
Lastrow = Wb.ActiveSheet.Cells(Rows.Count, 12).End(xlUp).Row
If Wb.Name Like "FR_*" And WorksheetFunction.CountIf(Wb.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

Private Sub Workbook_Open()
    Set CUSTOM_EXCEL = Application
End Sub

enter image description here

Upvotes: 1

Related Questions