Help with VBA 400 error

[Toastis]

i get a 400 error with this VBA code. Basically I want a control that they have to complete a field before they can print or close the form. If everything is complete and they hit the "send" button (macro) then it should either deny them from doing so or allow it to print as a pdf. It works fine as laid out if everything is filled in - it's when it's missing something, it gives the error and then ends on the VB screen. That would confuse them terribly.

Help!

Sub Make_PDF()
' Create and save .pdf

pdfName = Range("A9").Text

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
pdfName + " " + Format$(Date, "mm-dd-yyyy") + ".pdf" _
, Quality:=xlQualityMedium, IncludeDocProperties:=False, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub
Private Sub Workbook_BeforePrint(Cancel As Boolean)
If WorksheetFunction.CountBlank(Worksheets("sheet1").Range("A9:A11")) >= 1 Or WorksheetFunction.CountBlank(Worksheets("sheet1").Range("F9:F15")) >= 1 Then
MsgBox "Print disabled because at least one cell is left blank in Range A9:A11 or Range F9:F15"
Cancel = True
End If
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If WorksheetFunction.CountBlank(Worksheets("sheet1").Range("A9:A11")) >= 1 Or WorksheetFunction.CountBlank(Worksheets("sheet1").Range("F9:F15")) >= 1 Then
MsgBox "Close disabled because at least one cell is left blank in Range A9:A11 or Range F9:F15"
Cancel = True
End If
End Sub

 

[Toastis]

400 error

 

[Toastis]

If I take out the Before Print controls (ensuring the cells are completed) it works fine. But I need to ensure they completed the fields before they send it.

Not to mention I need to add a disable code to prevent using file -> send and I don't know if you can create a vba code that does that.

 

[shg]

The reason you don't get a compile error is because there is no such constant as xlQualityMedium; I changed it to xlQualityMinimum, which VBA recognizes. That's why I wanted you to use Option Explicit.

What is the value of pdfName when you step through the code?

When you step through the code, do you get to the BeforePrint sub?

 

[Toastis]

Where would I see the "value"?

 

[shg]

See http://www.cpearson.com/excel/debug.htm.

In themeanwhile, try coding it like this.

In a standard module,

Sub PrintPDF()
    With Worksheets("sheet1").Range("A9:A11, F9:F15")
        If NoBlanks(.Cells) Then
            Make_PDF
        Else
            Application.Goto .Cells
            MsgBox "Print disabled because one or more of these cells is blank"
        End If
    End With
End Sub
 
Sub Make_PDF()
    Dim pdfName As String
 
    pdfName = Range("A9").Text & Format$(Date, " mm-dd-yyyy") & ".pdf"
    ActiveSheet.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=pdfName, _
            Quality:=xlQualityMinimum, _
            IncludeDocProperties:=False, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=True
End Sub
 
Function NoBlanks(r As Range) As Boolean
    Dim rArea As Range
 
    For Each rArea In r.Areas
        If WorksheetFunction.CountBlank(rArea) Then Exit Function
    Next rArea
    NoBlanks = True
End Function

In ThisWorkbook,

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    With Worksheets("sheet1").Range("A9:A11, F9:F15")
        If Not NoBlanks(.Cells) Then
            Cancel = True
            Application.Goto .Cells
            MsgBox "Close disabled because one or more of these cells is blank"
        End If
    End With
End Sub

 

[Toastis]

Genius!!!

Thank you! It works - got a brilliant code to restrict them from file send without completing those fields?

 

[shg]

You could adapt the PrintPDF routine.

 

[Toastis]

Thank you but it works fine and I dare not novicily mess with it.