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

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


On Wed, 21 Dec 2005 17:10:09 +1030, "Rob" wrote:

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

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

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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


On Wed, 21 Dec 2005 17:10:09 +1030, "Rob" wrote:

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

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

On Thu, 22 Dec 2005 21:15:31 +1030, "Rob" wrote:

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

"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
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


On Wed, 21 Dec 2005 17:10:09 +1030, "Rob" wrote:

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

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

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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

On Thu, 22 Dec 2005 21:15:31 +1030, "Rob" wrote:

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

"Gord Dibben" <gorddibbATshawDOTca wrote in message
. ..
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


On Wed, 21 Dec 2005 17:10:09 +1030, "Rob"
wrote:

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

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

On Fri, 23 Dec 2005 18:18:21 +1030, "Rob" wrote:

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

"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
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

On Thu, 22 Dec 2005 21:15:31 +1030, "Rob" wrote:

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

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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


On Wed, 21 Dec 2005 17:10:09 +1030, "Rob"
wrote:

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

Thanks Gord.

Rob

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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

On Fri, 23 Dec 2005 18:18:21 +1030, "Rob" wrote:

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

"Gord Dibben" <gorddibbATshawDOTca wrote in message
. ..
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

On Thu, 22 Dec 2005 21:15:31 +1030, "Rob"
wrote:

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

"Gord Dibben" <gorddibbATshawDOTca wrote in message
m...
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


On Wed, 21 Dec 2005 17:10:09 +1030, "Rob"
wrote:

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