Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 755
  • Last Modified:

How to add VB codes before save as PDF file in Excel

I added some VB codes when users hit save or save as in the subrutine below:

Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

But if users save the workbook as pdf they will skip my VB codes.  So how to add my VB codes into the saving as pdf function?

Thanks!
0
jjxia2001
Asked:
jjxia2001
  • 8
  • 7
1 Solution
 
Ejgil HedegaardCommented:
Save to pdf is equal to printing to pdf, so use Workbook_BeforePrint
0
 
jjxia2001Author Commented:
I added the following code in the "ThisWorkbook" Object and hit save as pdf, but I didn't see "Hello"

Sub Workbook_BeforePrint()
    MsgBox "Hello"
End Sub

Here is where the "Save as Adobe pdf" is:
SavePDF.JPG
0
 
jjxia2001Author Commented:
An alternative is to disable Save/SaveAs/SaveAsPDF from manu bar and create a button in the excel workbook to save the current sheet to a selective location.  In this way, I can add my VB code into it.  Is that doable?  Any comments?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Ejgil HedegaardCommented:
You can't have a sub with the same name as the event, should produce a compilation error.
Try the correct syntax for the workbook before print event.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    MsgBox "Hello"
End Sub

I use Excel 2007, it works.
0
 
jjxia2001Author Commented:
This seems working and thanks!

But when the PDF file was created in a new window and then was closed (by me), the macro in the original excel workbook became disable, which means that I can't continue working on that workbook (I had to close it and reopen).   Is there anyway I can add some codes to enable the macro after creating PDF?

Private Sub Workbook_BeforePrint(Cancel As Boolean)
     MsgBox "Hello"
   
     Enable Macro..........

 End Sub
0
 
Ejgil HedegaardCommented:
The macro runs before print, so when the pdf file is created the macro is terminated.
There should be no problem selecting the Excel file when the pdf is open or after it is closed.

Try close all other programs, set Excel to Window view, and move the window so the middle of the screen is visible.
Then create the pdf file, and close it.
If a message box appears with something that has to be answered, it should then be visible.
0
 
jjxia2001Author Commented:
The pdf file is created in a new window and I still have the original workbook open.  I need to continue working on the original workbook including some macro related functions, but the macro is terminated.  That is why I want to add some codes to enable macro after creating pdf if possible.
0
 
Ejgil HedegaardCommented:
I don't understand what it is you can't do with the workbook.
What macro is terminated?

Macros are enabled when the workbook is open, and don't disable themselves.
A macro run when called to run.
Only Application.EnableEvents=False disable event macros (like before print) until set to True again, but other macros work normally.
0
 
jjxia2001Author Commented:
There is a print button in our Excel workbook and it worked when I open the workbook.  After I save the workbook as the pdf file, the print button didn't work any more and saw an error message said the macro was disable.  

Here is the my workbook and I've prefilled it.  Can you do (1) hit print button at the bottom (it should work), (2) save as pdf from the File manu to your C drive and then close the pdf window, (3) back to the workbook and hit print button again ( you will get a message like "can't run macro and it may be disable").

I need the print button work after save a pdf file.
CNS-WC-KE-Tool-TestVersion.xlsm
0
 
Ejgil HedegaardCommented:
Removed all the comments in the workbook before print event in the workbook module.

Made a print with the print button, worked.
Saved a pdf file, worked.
Made a print with the print button, worked again.
No message except for the archive warning.

Added a line in the bottom of the sub UserHasAccess to make it return True, to bypass the archive warning, don't know if it is important, but same result as before, without the archive warning, and with the Wait userform shown.

I have no idea of what all these subs do, tried to follow by step to see if something could give a hint to the problem, but there are a lot of calls to other subs calling other subs etc., checking a lot of things, so I gave up, and just let it run.

It seems to work ok, and without getting the error, it is hard to tell what is wrong.
Do you have a SAS call updating in the background, causing the next macro calls to fail, because VBA is busy with that.
0
 
jjxia2001Author Commented:
Can you send me the version that works?
0
 
Ejgil HedegaardCommented:
0
 
jjxia2001Author Commented:
It was not working for me.

Here is what I did:

1. clicked the print button in the bottom of the "Tool" tab - worked
2. clicked "Save as Adobe PDF" from the File manu bar, anwsered yes for all warnings, a new window with a pdf file was created - worked
3. closed the window with the pdf file
4. clicked the print button again - got an error message "Cannot run the macro "file name...".  The macro may not be available in this workbook or all macros may be  disabled.
0
 
Ejgil HedegaardCommented:
I have never heard of macros disabling after the workbook is opened with macros enabled.
But strange things happens sometimes.
Guess something has gone wrong with the Excel installation.

Try to run a repair (Diagnostic) on Excel, or if not successful reinstall.
0
 
jjxia2001Author Commented:
Sorry it didn't solve the problem.  But I would like to thank you for your effort.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now