Hello,


When I press 'Preview Print' it says, at the buttom, how many pages the
spreadsheet will use upon being printed, i.e.: "Page 1 of 4".

Is it possible to optain this number (4) in VBA and assign it to a variable?


TIA,

Hello Charlotte E,

Here are 2 methods of getting the page count...

=====================================
Function GetWorksheetPageCount() As Integer
With ActiveSheet
TotalPrintPagesOnWorksheet = .HPageBreaks.Count *
(.VPageBreaks.Count + 1)
End With
End Function

Function GetPageCount()
GetPrintedPages =
Application.ExecuteExcel4Macro("Get.Document(50)")
End Function
=====================================
Sincerely,
Leith Ross


--
Leith Ross

Sincerely,
Leith Ross

'The Code Cage' (http://www.thecodecage.com/)
------------------------------------------------------------------------
Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=87673

Don't think Leith's reply is quite accurate. Both Vertical and Horizontal
page breaks need 1 added to them.

Also need to assign the result to the function name.

Function GetWorksheetPageCount() As Integer
With ActiveSheet
GetWorksheetPageCount = (.HPageBreaks.Count + 1) _
* (.VPageBreaks.Count + 1)
End With
End Function

Function GetPageCount()
GetPageCount = Application.ExecuteExcel4Macro _
("Get.Document(50)")
End Function

Examples of using the functions:

Sub NoOfPages1()
'Call function GetWorksheetPageCount
MsgBox GetWorksheetPageCount()
End Sub

Sub NoOfPages2()
'Call function GetPageCount
MsgBox GetPageCount()
End Sub

--
Regards,

OssieMac


"Leith Ross" wrote:


Hello Charlotte E,

Here are 2 methods of getting the page count...

=====================================
Function GetWorksheetPageCount() As Integer
With ActiveSheet
TotalPrintPagesOnWorksheet = .HPageBreaks.Count *
(.VPageBreaks.Count + 1)
End With
End Function

Function GetPageCount()
GetPrintedPages =
Application.ExecuteExcel4Macro("Get.Document(50)")
End Function
=====================================
Sincerely,
Leith Ross


--
Leith Ross

Sincerely,
Leith Ross

'The Code Cage' (http://www.thecodecage.com/)
------------------------------------------------------------------------
Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=87673

I believe that one must first preview a worksheet before one can return the
print page count. It may be possible to get the count to work by changing
to page break preview first. Try the sample codes immediately after opening
Excel and before printing or do a preview to see if they work.

Robert Flanagan
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

"Charlotte E" <@ wrote in message
...
Hello,


When I press 'Preview Print' it says, at the buttom, how many pages the
spreadsheet will use upon being printed, i.e.: "Page 1 of 4".

Is it possible to optain this number (4) in VBA and assign it to a
variable?


TIA,

Works :-)

Thanks :-)



OssieMac wrote:
Don't think Leith's reply is quite accurate. Both Vertical and
Horizontal page breaks need 1 added to them.

Also need to assign the result to the function name.

Function GetWorksheetPageCount() As Integer
With ActiveSheet
GetWorksheetPageCount = (.HPageBreaks.Count + 1) _
* (.VPageBreaks.Count + 1)
End With
End Function

Function GetPageCount()
GetPageCount = Application.ExecuteExcel4Macro _
("Get.Document(50)")
End Function

Examples of using the functions:

Sub NoOfPages1()
'Call function GetWorksheetPageCount
MsgBox GetWorksheetPageCount()
End Sub

Sub NoOfPages2()
'Call function GetPageCount
MsgBox GetPageCount()
End Sub



Hello Charlotte E,

Here are 2 methods of getting the page count...

=====================================
Function GetWorksheetPageCount() As Integer
With ActiveSheet
TotalPrintPagesOnWorksheet = .HPageBreaks.Count *
(.VPageBreaks.Count + 1)
End With
End Function

Function GetPageCount()
GetPrintedPages =
Application.ExecuteExcel4Macro("Get.Document(50)")
End Function
=====================================
Sincerely,
Leith Ross


--
Leith Ross

Sincerely,
Leith Ross

'The Code Cage' (http://www.thecodecage.com/)
------------------------------------------------------------------------
Leith Ross's Profile:
http://www.thecodecage.com/forumz/member.php?userid=75 View this
thread: http://www.thecodecage.com/forumz/sh...ad.php?t=87673