Print using Add-in Only (Excel 2010, VBA)

I am trying to disable the PRINT function being used be separate workbooks. The only time I want it to print is using an AddIn. I have attached an example. It shows the messagebox but then allow the Excel Print function to work without using the AddIn. Additionally, the AddIn is getting the messagebox.

Book1 in ThisWorkbook

Public Sub Workbook_BeforePrint(Cancel As Boolean)
    Cancel = PrntOK
    MsgBox "You can't print this workbook"
    Exit Sub
End Sub

AddIn Code (partial)

Public Sub PrintDoc()

    name = UCase(Application.UserName)
    ctrlNbr = "12345"
    PrntOK = True
    Call PrintDocX(name, ctrlNbr)
    PrntOK = False
End Sub

If you never want to allow normal printing from that workbook then just use:

Public Sub Workbook_BeforePrint(Cancel As Boolean)
    Cancel = True
    MsgBox "You can't print this workbook"
End Sub

I don't see the need for the global variable PrntOK.

In fact, why not do this:

Public Sub Workbook_BeforePrint(Cancel As Boolean)
    PrintDoc
    Cancel = True
End Sub

Public Sub PrintDoc()
    Name = UCase(Application.UserName)
    ctrlNbr = "12345"
    Call PrintDocX(Name, ctrlNbr)
End Sub

Then you effectively cancel normal printing, and do the add-in printing instead, without the necessity for an error message.

I am not following what you're saying or how to place the code. In short, the problem is that I have several workbooks (20+) that run macros running calculations. People are printing directly from those workbooks (which is what I want to stop). I want them to use the AddIn that I created to print (as it saves into .PDF into a specific format). This is why I wanted to place the code disabling printing into these 20+ workbooks using a public sub so I could pass the boolean value of True from the AddIn so it would print from the AddIn.

Not sure that was the best way, but the only way this novice could devise. Alas, the code does not work.

That's exactly what it does. They will TRY to print normally, but it prints via the add-in instead. You've removed their normal print function, and effectively taken away their choice. Am I missing something? Do they have to press a button in your add-in to print? I've assumed that the PrintDocX routine prints.

I'm assuming that you are able to insert code in those 20 workbooks to implement this. These were just replacements for your existing routines of the same names.

When any user attempts to print, the default print job instruction will be ignored (Cancel = True). But at the same time your PrintDoc routine is called instead. So the user presses print, and gets a printout, except via your add-in. So they do not even have to be aware that there is an add-in; all printing will be sent to it without their being aware.

"So the user presses print, and gets a printout, except via your add-in"

I am trying to make it so the can ONLY print by using the print Macro in the AddIn. They are already aware of the print macro in the AddIn but are not utilizing it. They use the default print job instruction which is what I am trying to prevent and force them to use the AddIn.

Thread bump

Sorry, been/am busy. Will get back to you.

No worries. I understand. Was just bumping to see if there were others who might have something to add.

I am really confused, what actually happens when

Call PrintDocX(name, ctrlNbr)

is called?

Does it print immediately or does it bring up a UI/Dialog?

I understood that you were originally asking for this behaviour:

• User wants to print, so clicks the Excel print button
• Your messagebox comes up and tells them they can't print this way
• They must now go to the add-in and start printing from there

I was suggesting this behaviour:

• User wants to print, so clicks the Excel print button
• We've hijacked the Excel print button, so we can do anything now, so we cancel his 'normal' print request. Now rather than complain at him, just do what he asked, print.
• So call the PrintDocX routine, and start printing immediately.

Of course if Call PrintDocX isn't actually an instruction to start printing, then that's why I've been confused.

The PrintDocX(name, ctrlNbr) is a function that passes the name & ctrlNbr to another set of instructions that is indeed an instruction to print immediately though it is code to save as a .PDF with a prescribed naming convention. The reason I want the behavior suggested is my users are not saving their files in the prescribed naming covention that I want used. So I do not want them to print normally but be forced to use my AddIn to print.

The previously attached files should better give an idea of what I am looking for. Each of the workbooks will contain the code necessary to disable the Excel print function, unless they use the AddIn. The AddIn creates a toolbar that has dropdown list selection, one of which has the code to call the PrintDocX routine.

I've already explained how my suggested way disables normal printing and performs add-in printing instead.
Is it imperative that they interact with the dropdown interface?
I don't understand why you don't prefer the way without the extra steps.

I am not a programmer by nature. I prefer to be able to see what the code is in the actual sheets. I am sorry I was not able to follow your examples

Yes, it is imperative they work with the dropdown interface.

It may seem like an extra step but it is necessary the way we do things at the company here.

Appreciate the help. Thank you for your time.