Showing No. of Pages in a cell

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" <randwnobel@hotmail.com> 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
news:5p8jq1lnpkqngfuvk63nlqlhp8k1ldjruj@4ax.com...
> 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" <randwnobel@hotmail.com> 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" <randwnobel@hotmail.com> 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
>news:5p8jq1lnpkqngfuvk63nlqlhp8k1ldjruj@4ax.com...
>> 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" <randwnobel@hotmail.com> 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
news:knvlq15l8jrkvffqad9v34ph7b0jjja9ij@4ax.com...
> 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" <randwnobel@hotmail.com> 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
>>news:5p8jq1lnpkqngfuvk63nlqlhp8k1ldjruj@4ax.com...
>>> 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" <randwnobel@hotmail.com>
>>> 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" <randwnobel@hotmail.com> 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
>news:knvlq15l8jrkvffqad9v34ph7b0jjja9ij@4ax.com...
>> 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" <randwnobel@hotmail.com> 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
>>>news:5p8jq1lnpkqngfuvk63nlqlhp8k1ldjruj@4ax.com...
>>>> 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" <randwnobel@hotmail.com>
>>>> 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
>>>>>
>>>
>