Single Cells with Range on Workbook BeforeSave

[malcolm811]

Hi everyone,


I have a problem with a Macro that checks if certain cells contain data before the workbook can be saved.


I copied the VBA code from the Microsoft online documentation about the Workbook.BeforeSave Event (Excel) and I cannot get it to work if working at Workbook level.


The VBA code is simply

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   If WorksheetFunction.CountA(Worksheets("Sheet1").Range("D5,D7,D9,D11,D13, D15")) < 6 Then
      MsgBox "Workbook will not be saved unless" & vbCrLf & _
      "All required fields have been filled in!"
      Cancel = True
   End If
End Sub

Sheet1 is the name of the worksheet I have in my Excel file.


If I use the code in a Worksheet it works just fine. For example

Private Sub Worksheet_Deactivate()
   If WorksheetFunction.CountA(Worksheets("Sheet1").Range("D5,D7,D9,D11,D13, D15")) < 6 Then
      MsgBox "Workbook will not be saved unless" & vbCrLf & _
      "All required fields have been filled in!"
      Cancel = True
   End If
End Sub

I need Workbook_BeforeSave though and when saving Workbook_BeforeSave I get the message Error 1004 “Application-defined or Object-defined error".

If WorksheetFunction.CountA(Worksheets("Sheet1").Range("D5:D15")) < 6 Then

works too but I do not want to check the whole range from D5 to D15 but only those 6 cells.


Can somebody please help me?


Thanks in advance

 

[Joe4]

Welcome to the Board!

It seems to work fine for me. You are putting it in the "ThisWorkbook" module, right?

What happens if you change it to this, which should return a Message Box returning the count:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
    Dim ct As Long
    
    ct = WorksheetFunction.CountA(Worksheets("Sheet1").Range("D5,D7,D9,D11,D13,D15"))
    MsgBox "Count is: " & ct

    If ct < 6 Then
        MsgBox "Workbook will not be saved unless" & vbCrLf & _
        "All required fields have been filled in!"
        Cancel = True
    End If
    
End Sub

If that does not work, try adding this line above the "ct" calculation:

Sheets("Sheet1").Activate

If that still does not work, tell us exactly what happens when you try these steps, and let us know what version of Excel you are using.