Macro For Standard Print Button

Hello Juda,

Add this macro to the Worksheet where X1 will be.

Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.Address = "$X$1" And Target.Value <> "" Then
    ActiveSheet.PrintOut From:=1, To:=Target.Value
  End If
  
End Sub

How to Save a Worksheet Event Macro
1. Copy the macro using CTRL+C keys.
2. Open your Workbook and Right Click on the Worksheet's Name Tab for the Worksheet the macro will run on.
3. Left Click on View Code in the pop up menu.
4. Paste the macro code using CTRL+V
5. Make any custom changes to the macro if needed at this time.
6. Save the macro in your Workbook using CTRL+S

Sincerely,
Leith Ross

Thanks for the response - unfortunately, the macro doesn't meet all my wishes yet - for example, with data entered on 3 pages, when I enter the number 2 into Cell X1 and hit either 'Tab' or 'Enter', the printer immediately prints out 2 pages. If I then hit the 'Print' button on the Standard Toolbar, 3 pages print. The fact that I can still use 'File/Print...' is very exciting, but the pages should not print until I hit the 'Print' button - and when I do, I only want the number of pages in Cell X1 printed.

Hello Juda,

I misunderstood your post. This macro is written to be run from a button.

Sub PrintCopies()

  With ActiveSheet.Range("X1")
    If .Value <>"" Then
      ActiveSheet.Printout From:=1, To:=.Value
    End If
  End With

End Sub

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

Sincerely,
Leith Ross

Thanks again - but now when I enter the number '2' into Cell X1 and hit the 'Print' button on the Standard Toolbar, 3 pages still print. Also, if nothing is entered, 3 pages print as well. (Instead of none.) It's basically as if there is no macro at all.

Hello Juda,

Post you workbook and I'll review it and correct any problems with the code.

Sincerely,
Leith Ross

Juda,

What version of Excel are you using? I developed this in Excel 2000 but it should also work in 2003. I haven't tested it with 2007.

I did something similar to what you are trying to do by reassigning the OnAction event that happens when the print button is pressed.
You should be careful doing this because once reassigned the print button will not function normall in any open workbooks unless you reset the OnAction property of your print button.

Here is an example workbook that you can take a look at. I think this will do what you are after. I used cell C4 in my code for the cell to determine what to print.


ReassignPrintButton.xls

Thanks so much for your input, however, when I open your workbook, I receive this error:

Compile Error: Can't find project or library

I am using Excel 2003 - is that why it isn't working for me?

Try this file. I had a reference in the other from part of the my project I swiped that code from that I had removed for your application.

ReassignPrintButton.xls

Wow, I think this is the closest I've come to the solution yet. Now, do you think it is possible to incorporate this macro into the workbook I posted? I am not sure how to assign this macro to two different sheets - since the 'Inspection Report' Sheet uses Cell X1 to define the number of pages, and the 'Inspection Report Attachment' Sheet uses Cell P1. Also, what do you mean about that warning you gave me of the print button not functioning normally in any open workbooks unless I reset the OnAction property of my print button? Thanks again, Juda.

Try this workbook. I pasted slightly modified code from my other workbook into your workbook.

BlankSampleWorkbook(1).xls

My warning was that if you don't put some code like what I've included in the example workbook to remove the OnAction assignment when you either close this workbook or switch to another workbook then your print button will still run the macro it was reassigned to point to instead of the default Excel print command.

These two pieces of code are the ones that take care of that.
The first reassign the OnAction property of the print button.
The second resets the OnAction property of the print button to the Excel default.
The workbook I attached then calls the appropriate one of these when the workbook is activated or deactivated from a Workbook Event handler in the ThisWorkbook module.

Sub ReassignOnAction()
        Dim i As Integer
        i = 1
        Do Until Left(Application.CommandBars("Standard").Controls(i).Caption, 7) = "Print ("
            i = i + 1
        Loop
        Application.CommandBars("Standard").Controls(i).OnAction = "PrintSelected"
End Sub

Sub ResetOnAction()
        Dim i As Integer
        i = 1
        Do Until Left(Application.CommandBars("Standard").Controls(i).Caption, 7) = "Print ("
            i = i + 1
        Loop
        Application.CommandBars("Standard").Controls(i).OnAction = ""
End Sub

Hope this helps solve your problem and answers your question.