I'm making an Excel workbook for both Windows & Mac. I have a button in my workbook that allows the user to PRINT worksheets of their choice, and another that allows them to make a PDF of worksheets of their choice.
Both buttons work perfectly on Windows. On Mac 2016/2019/365, the PDF button works fine. It’s the PRINT button that sometimes causes a problem. It doesn't work properly when the user has pressed the PDF button before pressing the PRINT button. In that case, the .PrintOut line of code either gives an error, or saves a PDF file instead of printing.
If I close the workbook and reopen it, it will print just fine until I create a PDF, and then the problem happens again. I feel like this must be a bug in Excel for Mac, but am not 100% sure. I can only test it on my one Mac computer, so I know it's possible it might have something to do with my computer.
Here is some simple code to test this. I appreciate anyone who would try this on their Mac and let me know the results.
'this should send Sheet1 to the default printer
ThisWorkbook.Sheets("Sheet1").PrintOut Preview:=False, IgnorePrintAreas:=False
'this should create a PDF file of Sheet1 on the Desktop of the Mac
Dim UserName As String
Dim FileNameAndPath As String
UserName = MacScript("do shell script ""echo $USER""")
FileNameAndPath = "/Users/" & UserName & "/Desktop/TestPDF.pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
FileNameAndPath, Quality:=xlQualityStandard, _
The way to test it is:
Run the PrintButton sub. It should send "Sheet1" to your printer.
Run the PDFButton sub. It should create a PDF of "Sheet1" on your desktop.
Run the PrintButton sub again. This is where the problem is happening for me. I am curious if it works properly for you, or if you receive an error? I received Run-time 1004 error. (I have seen in some circumstances that instead of it printing the sheet here or throwing the error, it makes a PDF when it gets to the .PrintOut line.)
I created a workbook that has this code in it already, if you would rather download it and try it that way. You can download it here:
Thanks to anyone who tries it for me. I'm very curious to see if this is an actual bug in Excel for Mac. If it is, I'm curious if anyone knows of a work-around?