Previous Topic Next Topic

Excel vba to work on ALL workbooks - please help.

Closed
Sam - Nov 23, 2016 at 07:08 AM
yg_be Posts 23437 Registration date Sunday June 8, 2008 Status Contributor Last seen June 6, 2025 - Nov 23, 2016 at 02:43 PM
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,
Related:

1 response

Answer 1 / 1
yg_be Posts 23437 Registration date Sunday June 8, 2008 Status Contributor Last seen June 6, 2025 5
Nov 23, 2016 at 02:43 PM
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?

Similar discussions

How to invoke VLookUp function in Excel VBA.
Wingie -
TrowaD -

1 response