excel97vba coding - page numbering/footer issue.

[scroce]

Say you have 10 worksheets within a workbook.

Each worksheet contains 1 page worth of text.

You want to print all ten pages, with "1 of 10", "2 of 10",
"3 of 10".... etc in the right footer.

how do you do it?

for some reason Excel97 is not behaving consistantly. It keeps seeing each page as its own document, even when I try to group them. It keeps saying "1 of 1" on each.

I've tried a billion things. Nothing seems to work. to top it off, the footer seems to throw off the fomatting every time i try to implement it, so I'm wondering if I can just write some code that will count the worksheets and throw the number of each page in an empty cell on the sheet. - but i'm not sure where to start with the code.

anyone have any suggestions?

SC How much water would there be in the ocean if it weren't for sponges?

 

[Kaah]

hey mann,,

maybe the solution is for you to use some sort of a template. I assume that you're writing your data to a clean workbook? instead of using workbook.add, use a template file and on the template contain your footer. Theres really no reason for you to try to code the footers, and you're right, it probably is hard. Let excel do the work. If the footers are already set on the template worksheet before you even start writing data then they will always be updated correctly and you wont have to code.

VBA is good, but for the most part you should be thinking of ways to make excel do the work instead.

let me know if you need more help or a better explanation.

K.

 

[scroce]

yes, I wholeheartedly agree with you. I resort to code only after Excel's features won't do what I need them to do, and this seems to be the case.

I can't believe I'm having this much trouble with it. I tried to group the pages, play around with page setup and such, but nothing seems to work. So thought I could get excel to throw a calculated page number in one of the cells as a workaround. I know something of VB, but I get bogged down in the component model and what obejects, properties and methods I can manipulate to achieve the desired result. How much water would there be in the ocean if it weren't for sponges?

 

[ratman]

if you decide to have different subs for each worksheet
set up 2 variables as global
ex.
totws as global
thisws as global
if not you wont need the global



either way do the following:


determine the # of worksheets in workbook...=totws

then

in each pagesetup command reference the totws but increment the thisws +1