Forum Discussion

kwabsa's avatar
kwabsa
Copper Contributor
Nov 24, 2023

Issue with PrintOut Method in Excel VBA for Mac Not Working Consistently

Hello,

I'm experiencing a problem with Excel for Mac (specify version, e.g., Excel for Mac 16.45) where the PrintOut method in VBA is not functioning consistently. Here's the context:

  • I have a macro that needs to print a worksheet multiple times.
  • The PrintOut method sometimes works (prints one copy even when multiple are specified), but often fails with a "Run-time error '1004': Method 'PrintOut' of object '_Worksheet' failed".
  • This issue occurs even with basic macros that simply call Sheets("Invoice").PrintOut.
  • I have tried the following without success:
    • Checking printer settings and ensuring it's the default printer.
    • Ensuring the latest printer drivers are installed.
    • Restarting Excel and the Mac.
    • Using error handling in the VBA code.
    • Running the macro on different workbooks.
  • Oddly, the issue started after I added code to save worksheets as PDFs using ExportAsFixedFormat.

Here is a sample of the code that's causing issues:

 

 

 

 

 

Sub PrintOutWithErrorHandling()
    On Error GoTo ErrorHandler
    Sheets("Invoice").PrintOut Copies:=2
    Exit Sub
    
ErrorHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical
End Sub

 

 

 

 

 

 

The environment is as follows:

  • Mac OS version: MacOS Sonoma
  • Excel for Mac version: 16.79

Any advice on how to troubleshoot this or a workaround would be greatly appreciated. Thank you for your assistance!

Excel on Mac
Macros and VBA
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Nov 24, 2023

    kwabsa 

    It seems like you have taken several steps to troubleshoot the issue with the PrintOut method in Excel VBA on your Mac, and it's still not functioning consistently. Let's explore a few additional suggestions and potential workarounds:

    Specify Printer Parameters: Try specifying additional parameters when using the PrintOut method. For example, set the ActivePrinter property before printing:

    Vba code (is untested):

    Sub PrintOutWithPrinter()
    On Error GoTo ErrorHandler
    
    ' Store current printer
    Dim currentPrinter As String
    currentPrinter = Application.ActivePrinter
    
    ' Set desired printer (replace "YourPrinterName" with your printer's name)
    Application.ActivePrinter = "YourPrinterName"
    
    ' Print multiple copies
    Sheets("Invoice").PrintOut Copies:=2
    
    ' Restore original printer
    Application.ActivePrinter = currentPrinter
    
    Exit Sub

ErrorHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical
End Sub

     

    Check PDF Export Code: Since the issue started after adding code to save worksheets as PDFs, review the PDF export code to ensure it's not causing conflicts. Make sure the PDF export is completed before attempting to print.

    Test Different Print Settings: Experiment with different print settings in the Excel worksheet, such as adjusting margins, page orientation, or page size. Sometimes, specific print settings can lead to inconsistencies.

    Check Printer Dialogue Interaction: If the issue persists, consider adding code to interact with the printer dialogue box. This might help in situations where the dialogue box needs confirmation.

    For example:

    Sub PrintOutWithDialog()
    On Error GoTo ErrorHandler
    
    ' Display the print dialogue
    Application.Dialogs(xlDialogPrint).Show
    
    Exit Sub

ErrorHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical
End Sub

    Excel for Mac Updates: Ensure that your Excel for Mac installation is up-to-date. Check for any available updates and install them.

    Test on a Different Mac: If possible, try running the VBA code on a different Mac with a different setup to see if the issue persists. This can help determine if the problem is specific to your machine.

    Check Print Drivers: Confirm that the print drivers on your Mac are up-to-date. Sometimes, issues with print drivers can affect the printing process.

    Remember to tailor the code snippets to your specific environment and requirements. The text and steps were edited with the help of AI.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

    Was the answer useful? Mark as best response and Like it!

    This will help all forum participants.

Resources