returning the activecell's page number
 

How would I get the selected cell's page number, for example if ive selected
cell C345, i may be on page 5 depending on the print area. I would like it
that VB would return me 5 and i could assign this to a variable

returning the activecell's page number
 

X = 0
For Y = 1 to Worksheets.Count
If Worksheets(Y).Name = ActiveSheet.Name then X = Y
Next Y

"gill" wrote:

How would I get the selected cell's page number, for example if ive selected
cell C345, i may be on page 5 depending on the print area. I would like it
that VB would return me 5 and i could assign this to a variable

returning the activecell's page number
 

I want to know about the page itself in that specific sheet, not the number
of sheets

"Brad E." wrote:

X = 0
For Y = 1 to Worksheets.Count
If Worksheets(Y).Name = ActiveSheet.Name then X = Y
Next Y

"gill" wrote:

How would I get the selected cell's page number, for example if ive selected
cell C345, i may be on page 5 depending on the print area. I would like it
that VB would return me 5 and i could assign this to a variable

returning the activecell's page number
 

On Thu, 6 Jul 2006 12:16:02 -0700, gill
wrote:

I want to know about the page itself in that specific sheet, not the number
of sheets

"Brad E." wrote:

X = 0
For Y = 1 to Worksheets.Count
If Worksheets(Y).Name = ActiveSheet.Name then X = Y
Next Y

"gill" wrote:

How would I get the selected cell's page number, for example if ive selected
cell C345, i may be on page 5 depending on the print area. I would like it
that VB would return me 5 and i could assign this to a variable

So, you would like to count the pagebreaks and add one to the count?

returning the activecell's page number
 

As well as the horizontal and vertical page breaks, it would also depend on
if you are printing across-then-down, or down-then-across.
And also .FirstPageNumber

NickHK

"Bookreader" wrote in message
...
On Thu, 6 Jul 2006 12:16:02 -0700, gill
wrote:

I want to know about the page itself in that specific sheet, not the
number
of sheets

"Brad E." wrote:

X = 0
For Y = 1 to Worksheets.Count
If Worksheets(Y).Name = ActiveSheet.Name then X = Y
Next Y

"gill" wrote:

How would I get the selected cell's page number, for example if ive
selected
cell C345, i may be on page 5 depending on the print area. I would
like it
that VB would return me 5 and i could assign this to a variable

So, you would like to count the pagebreaks and add one to the count?

returning the activecell's page number
 

Gill,
This eems to work, but not well tested:

Public Function GetMyPage() As Variant
Dim CallerCell As Range
Dim ThisWS As Worksheet
Dim VertBreak As VPageBreak
Dim HorizBreak As HPageBreak
Dim VertBreakCount As Long
Dim HorizBreakCount As Long
Dim PageNumber As Long

Set CallerCell = Application.Caller

Set ThisWS = ThisWorkbook.Worksheets(CallerCell.Parent.Name)
With ThisWS

For Each VertBreak In .VPageBreaks
If VertBreak.Location.Column < CallerCell.Column Then
VertBreakCount = VertBreakCount + 1
Else
Exit For
End If
Next

For Each HorizBreak In .HPageBreaks
If HorizBreak.Location.Row <= CallerCell.Row Then
HorizBreakCount = HorizBreakCount + 1
Else
Exit For
End If
Next

Select Case .PageSetup.Order
Case xlDownThenOver
PageNumber = (.HPageBreaks.Count + 1) * VertBreakCount +
(HorizBreakCount + 1)
Case xlOverThenDown
PageNumber = (.VPageBreaks.Count + 1) * HorizBreakCount +
(VertBreakCount + 1)
End Select

If .PageSetup.FirstPageNumber < xlAutomatic Then
PageNumber = PageNumber + .PageSetup.FirstPageNumber
Else
PageNumber = PageNumber ' + 1
End If

End With

GetMyPage = PageNumber

End Function


NickHK

"gill" wrote in message
...
How would I get the selected cell's page number, for example if ive
selected
cell C345, i may be on page 5 depending on the print area. I would like it
that VB would return me 5 and i could assign this to a variable

returning the activecell's page number
 

Actually,
You don't need the objects set. That was just provide the Intellisense
whilst writing. So it can be simplified to:
Public Function GetMyPage() As Variant
Dim VertBreak As VPageBreak
Dim HorizBreak As HPageBreak
Dim VertBreakCount As Long
Dim HorizBreakCount As Long
Dim PageNumber As Long

With ThisWorkbook.Worksheets(Application.Caller.Parent. Name)
'VertBreakCount = 1
For Each VertBreak In .VPageBreaks
If VertBreak.Location.Column < Application.Caller.Column Then
VertBreakCount = VertBreakCount + 1
Else
Exit For
End If
Next

'HorizBreakCount = 1
For Each HorizBreak In .HPageBreaks
If HorizBreak.Location.Row <= Application.Caller.Row Then
....etc

It seem some what slow, but I suppose Excel needs to process a kind of
preview each time to assess the pagebreaks.
Note that if you change the PageSetup and/or column widths/row heights, so
function will NOT automatically update, so the results will get out of sync.

NickHK

"NickHK" wrote in message
...
Gill,
This eems to work, but not well tested:

Public Function GetMyPage() As Variant
Dim CallerCell As Range
Dim ThisWS As Worksheet
Dim VertBreak As VPageBreak
Dim HorizBreak As HPageBreak
Dim VertBreakCount As Long
Dim HorizBreakCount As Long
Dim PageNumber As Long

Set CallerCell = Application.Caller

Set ThisWS = ThisWorkbook.Worksheets(CallerCell.Parent.Name)
With ThisWS

For Each VertBreak In .VPageBreaks
If VertBreak.Location.Column < CallerCell.Column Then
VertBreakCount = VertBreakCount + 1
Else
Exit For
End If
Next

For Each HorizBreak In .HPageBreaks
If HorizBreak.Location.Row <= CallerCell.Row Then
HorizBreakCount = HorizBreakCount + 1
Else
Exit For
End If
Next

Select Case .PageSetup.Order
Case xlDownThenOver
PageNumber = (.HPageBreaks.Count + 1) * VertBreakCount +
(HorizBreakCount + 1)
Case xlOverThenDown
PageNumber = (.VPageBreaks.Count + 1) * HorizBreakCount +
(VertBreakCount + 1)
End Select

If .PageSetup.FirstPageNumber < xlAutomatic Then
PageNumber = PageNumber + .PageSetup.FirstPageNumber
Else
PageNumber = PageNumber ' + 1
End If

End With

GetMyPage = PageNumber

End Function


NickHK

"gill" wrote in message
...
How would I get the selected cell's page number, for example if ive
selected
cell C345, i may be on page 5 depending on the print area. I would like
it
that VB would return me 5 and i could assign this to a variable