Excel vba to work on ALL workbooks - please help. [Closed]

Question

Excel vba to work on ALL workbooks - please help. [Closed]

Report

Sam - Nov 23, 2016 at 07:08 AM
yg_be

Posts: 13990
Registration date: Sunday June 8, 2008
Status: Contributor
Last seen: January 21, 2021

- 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,

Excel vba to work on ALL workbooks - please help.
Excel vba copy range to another workbook - How-To - Excel
Excel vba copy worksheet to another workbook without opening ✓ - Forum - Excel
Excel vba copy sheet to new workbook ✓ - Forum - Excel
Excel vba copy worksheet to another workbook and rename ✓ - Forum - Excel
Excel VBA to open a EULA script when opening a spesific workbook ✓ - Forum - Excel

1 reply

Ask a question

yg_be · Answer 1 · 2016-11-23T14:43:05-0500

Answer 1 / 1

yg_be

Posts: 13990
Registration date: Sunday June 8, 2008
Status: Contributor
Last seen: January 21, 2021

4
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?

Excel vba to work on ALL workbooks - please help. [Closed]

1 reply

Your reply

Subscribe To Our Newsletter!