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

 

[shg]

Maybe like this?

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim wks         As Worksheet
 
    Set wks = Worksheets("sheet1")
    With WorksheetFunction
        If .CountBlank(wks.Range("A9:A11")) > 0 Or _
           .CountBlank(wks.Range("F9:F15")) > 0 Then
            Cancel = True
            Application.Goto wks.Range("A9:A11, F9:F15")
            MsgBox "Print disabled because one or more of these cells is blank"
 
        End If
    End With
End Sub
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim wks         As Worksheet
 
    Set wks = Worksheets("sheet1")
    With WorksheetFunction
        If .CountBlank(wks.Range("A9:A11")) > 0 Or _
           .CountBlank(wks.Range("F9:F15")) > 0 Then
            Cancel = True
            Application.Goto wks.Range("A9:A11, F9:F15")
            MsgBox "Close disabled because one or more of these cells is blank"
 
        End If
    End With
End Sub

If that doesn't work, what line of code causes the error?

 

[Toastis]

it works fine but doesn't care for my print macro - returning me back to VBA screen after I hit the "send" button which = Macro:


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

 

[shg]

Put Option Explicit at the top of the module and see what happens.

 

[Toastis]

No it gets really cranky when I add that Not to mention I just realized that despite the code I have so far - they could not use my "send" button and then would be allowed to do file, send ... that's not good either. They shouldn't be able to send it at all without the designated fields being complete. I would like the macro button to work because it needs to print as a PDF and then send.

 

[shg]

The problem is in the MakePDF macro, right?

What message do you get when you use Option Explicit?

 

[Toastis]

yes it's with the macro to save as a pdf - Compile Error:Variable not found

 

[shg]

On pdfName, right? So add this line to the macro and try again. Now what happens?

Dim pdfName as String

 

[Toastis]

Not knowing where to put it - I just added under PDFName = Range ...
still a 400 error

When I added above create and save it just was some sort of unexpected error, if at the bottom then invalid combination.

 

[shg]

Option Explicit
 
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

Try this. Now what happens?