pagenumber of active cell (vba)

  • Thread starter Thread starter Ralf Hörbelt
  • Start date Start date
R

Ralf Hörbelt

Hi,

how can i get the pagenumber of the selected cell?

thanks for any help
Ralf
merry christmas from germany!
 
Here are some approaches posted in the past:

http://groups.google.com/groups?selm=uehLs6#w#GA.222@cppssbbsa03

From: Dana DeLouis ([email protected])
Subject: Re: # of pages and page#
View: Complete Thread (4 articles)
Original Format
Newsgroups: microsoft.public.excel.programming
Date: 1999/07/01


Here is something I wrote a long time ago. Not tested for Excel 2k. Maybe
this will give you some ideas. I have numbered the pages left to right,
then down. Adjust it you want it different. Good luck. Dana

Sub Print_Page_of_ActiveCell()
'// Prints the page of the active cell only
'// Dana DeLouis: (e-mail address removed)
'// Not tested completely for Excel 2k

Dim pb As Variant
Dim Nr As Long, Nc As Integer
Dim MaxColumns As Long
Dim MaxRows As Long
Dim CurrentPage As Long
Dim TotalPages As Long

If Selection.Cells.Count > 1 Then
MsgBox "Select 1 Cell Only"
Exit Sub
End If

'// Save Settings
ActiveWorkbook.CustomViews.Add _
ViewName:="_temp", _
PrintSettings:=True, _
RowColSettings:=True

With ActiveSheet.PageSetup
.PrintArea = False
.Order = xlOverThenDown
End With

TotalPages = ExecuteExcel4Macro("Get.Document(50)")
MaxColumns = ActiveSheet.VPageBreaks.Count + 1
MaxRows = ActiveSheet.HPageBreaks.Count + 1
' or MaxRows = TotalPages / MaxColumns

For Each pb In ActiveSheet.HPageBreaks
If pb.Location.Row <= ActiveCell.Row Then
Nr = Nr + 1
Else
Exit For
End If
Next

For Each pb In ActiveSheet.VPageBreaks
If pb.Location.Column <= ActiveCell.Column Then
Nc = Nc + 1
Else
Exit For
End If
Next

Nc = Nc + 1
CurrentPage = Nr * MaxColumns + Nc

'// Reset what I had
With ActiveWorkbook.CustomViews("_temp")
.Show
.Delete
End With

MsgBox "Page : " & CurrentPage & " out of " & TotalPages

'// Print the selected page
'ActiveSheet.PrintOut From:=CurrentPage, To:=CurrentPage
End Sub

-----------------------

In the thread below, Dana cautions that setting a print area could affect
the results of the code, so he suggested removing the print area and
restoring it. This post is in the thread below.

---------------------------

http://groups.google.com/groups?threadm=eEe8mwaDCHA.2164@tkmsftngp02

[ Posted by Ron de Bruin]

Laurent Longre posted the following code on April 21 2000, which
displays a message box with the page number. To print the page,
line added at the end of the macro:

Sub PrintCurrentPage()
Dim VPC As Integer, HPC As Integer
Dim VPB As VPageBreak, HPB As HPageBreak
Dim NumPage As Integer

If ActiveSheet.PageSetup.Order = xlDownThenOver Then
HPC = ActiveSheet.HPageBreaks.Count + 1
VPC = 1
Else
VPC = ActiveSheet.VPageBreaks.Count + 1
HPC = 1
End If
NumPage = 1
For Each VPB In ActiveSheet.VPageBreaks
If VPB.Location.Column > ActiveCell.Column Then Exit For
NumPage = NumPage + HPC
Next VPB
For Each HPB In ActiveSheet.HPageBreaks
If HPB.Location.Row > ActiveCell.Row Then Exit For
NumPage = NumPage + VPC
Next HPB
'MsgBox "Page number of the active cell = " & NumPage
ActiveWindow.SelectedSheets.PrintOut _
From:=NumPage, To:=NumPage, _
Copies:=1, Collate:=True

End Sub
 
Ralf,

This would work if you don't have vertical page breaks..

Private Sub CommandButton1_Click()
Dim obj As Object
Dim intRow As Integer
Dim i As Integer
intRow = ActiveCell.Row
i = 1
For Each obj In ActiveSheet.HPageBreaks
If obj.Location.Row > intRow Then
MsgBox i & " is the current page number."
Exit Sub
End If
i = i + 1
Next
End Sub

Hope that helps.

-Brad
 
Back
Top