Reason: because it has to match up with a preprinted form being used by
others. This contains several rows that are table column headers and which
are in a Repeat Rows area.. ..and, before you suggest it, I am not about to
set it all up to use a "preprinted form" - that has too many logistic
problems for me.
The code that "doesn't work" is:
Public Function PageNumber( _
Optional ByRef rng As Excel.Range) As Variant
Dim pbHorizontal As HPageBreak
Dim pbVertical As VPageBreak
Dim nHorizontalPageBreaks As Long
Dim nVerticalPageBreaks As Long
Dim nPageNumber As Long
On Error GoTo ErrHandler
Application.Volatile
If rng Is Nothing Then _
Set rng = Application.Caller
With rng
If .Parent.PageSetup.Order = xlDownThenOver Then
nHorizontalPageBreaks = .Parent.HPageBreaks.Count + 1
nVerticalPageBreaks = 1
Else
nHorizontalPageBreaks = 1
nVerticalPageBreaks = .Parent.VPageBreaks.Count + 1
End If
nPageNumber = 1
For Each pbHorizontal In .Parent.HPageBreaks
If pbHorizontal.Location.Row > .Row Then Exit For
nPageNumber = nPageNumber + nVerticalPageBreaks
Next pbHorizontal
For Each pbVertical In .Parent.VPageBreaks
If pbVertical.Location.Column > .Column Then Exit For
nPageNumber = nPageNumber + nHorizontalPageBreaks
Next pbVertical
End With
PageNumber = nPageNumber
ResumeHere:
Exit Function
ErrHandler:
'Could use much more error handling...!
PageNumber = CVErr(xlErrRef)
Resume ResumeHere
End Function
It works appropriately (gving the page on which that cell is to be found)
when put in a cell outside the Repeat Rows area. Within the Repeat Rows
area it gives the page on which the row is found (ie always 1) and the page
on which the Repeat Row is being printed (ie incrementing with each page
printed)
cheers
Chris
Jim Cone said:
I am not sure if anything comes to mind.
What might help would be knowing why the page number in the header is not
satisfactory.
In other words what are you trying to achieve exactly?
Also, seeing the code to add the page number to a cell could be helpful.
'--
Spent the last few hours registering/downloading and briefly trying out
the technical
preview release of Office 2010 (Excel). MS sure knows how to make things
difficult.
--
Jim Cone
Portland, Oregon USA
"Chris Watts" <
[email protected]>
wrote in message
Thanks Jim.
Using the sheet header won't work for what I want to do.
As I indicated, I am happy to explore VBA solutions, so please come
forward
with your suggestions.
cheers
Chris
Jim Cone said:
Nothing comes to mind that doesn't require VBA code.
You can paste the following in the sheet header (File | Page Setup) and
page x of y will print on each page at the top...
Page &[Page] & of &[Pages]
--
Jim Cone
Portland, Oregon USA
"Chris Watts" <
[email protected]>
wrote in message
Is there a way that I can put a Page Number in the Repeat Rows area of a
sheet - and get it to update when I print?
I have found one piece of VBA that puts a page number within a cell but
it
is only updated if the cell is outside the Repeat Row area. When it is
within the Repeat Rows area I just get a page number of 1 corresponding
to
the original row location..
I do some VBA programming but am not an expert.
TIA
cheers
Chris
