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