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

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

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

On Thu, 6 Jul 2006 12:16:02 -0700, gill
<gill@discussions.microsoft.com> 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?

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" <bookreader127@yahoo.com> wrote in message
news:uv3ra2l8s925citnhj1bqebnll1tpgo116@4ax.com...
> On Thu, 6 Jul 2006 12:16:02 -0700, gill
> <gill@discussions.microsoft.com> 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?

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" <gill@discussions.microsoft.com> wrote in message
news:25B5F679-C872-4A39-9B44-760A4C491753@microsoft.com...
> 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

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" <TungCheWah@Invalid.com> wrote in message
news:O17Q%23LZoGHA.5084@TK2MSFTNGP03.phx.gbl...
> 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" <gill@discussions.microsoft.com> wrote in message
> news:25B5F679-C872-4A39-9B44-760A4C491753@microsoft.com...
> > 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
>
>