Showing No. of Pages in a cell

R

Rob

I've set up a workbook to print data with page breaks in various places.
The amount of the data can increase or decrease, either by sheer volume or
by filtering. I would like to notify the user the number of pages that will
be printed depending on the setup/filtering/volume of data, etc.
I realise there is the Option to have the total number of pages printed at
the bottom (or top)via the Footer (or header) facility in Page setup, but I
want to show this elsewhere.

Is there some way to do this?

Rob
 
G

Gord Dibben

Rob

Sub Page_Nos()
TotalPages = ExecuteExcel4Macro("Get.Document(50)")
MsgBox "there are " & TotalPages & " pages in this print job"
Sheets("Sheet1").Range("A1").Value = TotalPages
End Sub

Alternative in Thisworkbook BeforePrint routine.........

Private Sub Workbook_BeforePrint(Cancel As Boolean)
TotalPages = ExecuteExcel4Macro("Get.Document(50)")
msg = "There will be " & TotalPages & " Printed Pages" & Chr(13) _
& "Is this acceptable?" & Chr(13) _
& "If Not, Hit No to Cancel Job"
Ans = MsgBox(msg, vbYesNo)
Select Case Ans
Case vbNo
Cancel = True
End Select
End Sub


Gord Dibben Excel MVP
 
R

Rob

Thanks Gord!!!

That was a real help. I had to add a dim statement to get it to work and
made some minor modifications but the following shows the number of pages in
the appropriate cell.

Thanks too for the Workbook_BeforePrint routine which I might make use of as
well.

Sub Page_Nos()
Dim TotalPages As Integer
TotalPages = ExecuteExcel4Macro("Get.Document(50)")
With Sheet3
.Unprotect
.Range("a1").Value = TotalPages
.Protect
End With
End Sub

Rob
 
G

Gord Dibben

Rob

Thanks for the feedback.

You're right......I am guilty of not Dimming properly when testing.

If you leave Option Explicit off you can get away with some things, but not
good practice.


Gord
 
R

Rob

Hi Gord,

I really wasn't passing blame. I was just happy to get the code! I didn't
even realise that the OptionExplicit caused the code not to work.

If you have time I'd like to know what the
ExecuteExcel4Macro("Get.Document(50)") part of the code means, including why
(50).

Rob
 
G

Gord Dibben

Rob

ExecuteExcel4Macro runs an Excel4 macro because there is no VBA equivalent
method.

The Get.Document(50) is nothing more than a code number.

I have no idea what the code numbers are for the various properties.

The macro I provided was originally posted by Ron de Bruin, I believe.

One of the VBA-knowledgeable people will be able to elaborate should they
choose to jump in.

There is small bit of help on this if you type executeexcel4macro in the VBA
help answer wizard.


Gord
 
R

Rob

Thanks Gord.

Rob

Gord Dibben said:
Rob

ExecuteExcel4Macro runs an Excel4 macro because there is no VBA equivalent
method.

The Get.Document(50) is nothing more than a code number.

I have no idea what the code numbers are for the various properties.

The macro I provided was originally posted by Ron de Bruin, I believe.

One of the VBA-knowledgeable people will be able to elaborate should they
choose to jump in.

There is small bit of help on this if you type executeexcel4macro in the
VBA
help answer wizard.


Gord
Click to expand...
 
Top