How to assign PRINT option to execute a macro

[Zubair]

How can I assign PRINT option to execute a macro, used for printing page
numbers in a cell. Macro results a proper answer I require but only on
executing or playing macro. I want to use Print options of excel to execute
the following mentioned macro.

Sub Demo()
Dim TotalPages As Long
Dim pg As Long


TotalPages = ExecuteExcel4Macro("Get.Document(50)")
For pg = 1 To TotalPages
With ActiveSheet
.Range("BY3").Value = pg & " of " & TotalPages '<<< CHANGE HERE
.PrintOut From:=pg, To:=pg
End With
Next pg
End Sub


Kind regards,
Zubair

 

[Luke M]

If you're wanting to code to tell XL to print a document, the easiest way is
prb to record yourself printing the document. That will record all the
different settings such as printer, paper size, which pages, etc. Adapt to
suit.

 

[Jim Thomlinson]

So do you want this macro to fire whenever the user prints? If so then you
can use the before print event. Right click on the XL icon jut to the left of
File in the XL menu. Select View Code.
Just above the code window are two drop downs. Change the one on the left to
Workbook and th eone onf the right to before print. A code stub will be
written for you that will execute any time a print event is detected.

 

[Gord Dibben]

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim TotalPages As Long
Dim pg As Long
TotalPages = ExecuteExcel4Macro("Get.Document(50)")
For pg = 1 To TotalPages
With ActiveSheet
.Range("BY3").Value = pg & " of " & TotalPages '<<< CHANGE HERE
.PrintOut From:=pg, To:=pg
End With
Next pg
End Sub

Paste into Thisworkbook module.


Gord Dibben MS Excel MVP