Page Number Problem

S

socram_06

Hi there, hope you are fine... I'm new in this forum, and I have a doubt
(a big doubt) about page number... Here is the problem:

I have a long spreadsheet (about 320 pages), and I need to show the the
current page number, in a sigle cell (A1, for example)...
So, if a cell in page 1 is selected, in A1 we'll have "Page 1 of 320",
otherwise if the selected cell is in page 20, now A1 will be showing
"Page 20 of 320", and so on... I've tried use the PageBreaks.Count, but
I get always the same result: "*Page 1 of 320*", it doesn't change to
"15 of N", or "85 of N"...

It would have to work as when we're in the Print Preview mode...
scrolling (down or up), the message in StatusBar is always displaying
the current page number (I can't use the header / footer to do this,
but a single cell) !!!

Tanks in advance, for any help

Marcos.
 
S

socram_06

sorry, I was not so clear...

I'm talking about pages...

In header / footer the excel uses a macro to show the page number,
doesn't it ??? I'd like to do the same, but not using header / footer,
but a single cell, where I can see the page I am all the time...

*****************************************
A friend told me to do this: (VBA)

Insert, Name, Define: Add a name (PageNo) and the refers to is
=get.document(64)

In a cell in the column you want enter the formula

=IF(ISNA(MATCH(ROW(),PageNo)),1,MATCH(ROW(),PageNo)+1)
*****************************************
It works, but not in a single cell, like I need...

If you need more details, please tell me and I'll try explain a bit
better !!!

Thanks a lot
Marcos
 
B

Bill Sharpe

socram_06 said:
sorry, I was not so clear...

I'm talking about pages...

In header / footer the excel uses a macro to show the page number,
doesn't it ??? I'd like to do the same, but not using header / footer,
but a single cell, where I can see the page I am all the time...

*****************************************
A friend told me to do this: (VBA)

Insert, Name, Define: Add a name (PageNo) and the refers to is
=get.document(64)

In a cell in the column you want enter the formula

=IF(ISNA(MATCH(ROW(),PageNo)),1,MATCH(ROW(),PageNo)+1)
*****************************************
It works, but not in a single cell, like I need...

If you need more details, please tell me and I'll try explain a bit
better !!!

Thanks a lot
Marcos
Click to expand...
View | Page Break Preview will always show what page you are on, but of
course not in a single cell.

If you ever do find what to put in A1 to show the current page, you'll
have to freeze the top row to display that cell.

Bill
 
S

socram_06

Bill said:
View | Page Break Preview will always show what page you are on, but
of
course not in a single cell.

If you ever do find what to put in A1 to show the current page, you'll
have to freeze the top row to display that cell.

Bill
Click to expand...

Hello Bill, that's it... I'll freeze the top row, and then when a cell
is selected or changed, according to its location (page), excel would
count the pages (or page breaks), and the result would appear in A1...
The code bellow is part of one that I have tried...


Code:
--------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim h
Dim v

h = Application.Excel4MacroSheets.HPageBreaks.Count + 1
v = Application.Excel4MacroSheets.VPageBreaks.Count + 1

Range("A1") = "Page " & v & " of " & h
End Sub
--------------------


How to complete or change this code to make it work ???

Best regards

Marcos.
 
Top