Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question with existing page numbering macro?
Hi the code below is an abstract from a macro I use to automatically page
numbers when printing, Sub workbook_BeforePrint(Cancel As Boolean) With Worksheets("sheet1") ..PageSetup.CenterFooter = "" & .Range("A1").Text End With With Worksheets("sheet2") ..PageSetup.CenterFooter = "" & .Range("a1").Text End With 'etc etc End Sub The macro functions as required and is placed within Thisworkbook. As the number of pages have grown to 20, the macro will only function if I press print preview before printing, otherwise the page numbering is not updated. I'm guessing the print function is quicker than the macro and it doesn't get chance to complete. Not the end of the world, but I just wondered if there was a way to get around this to allolw the macro enough time to complete before the printing starts? -- This post was created using recycled electrons! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question with existing page numbering macro?
I don't think it is a question of time, I think it is more a matter of the
page numbers are calculated dynamically, when a print (or Printpreview) command is issued. I have often noticed in a large Word document if you do a Page n of m in the footer, as you scroll down, m is updated as well as n, Word works it out. I haven't tried this, and it maybe overkill, but perhaps you could try forcing a printpreview. Private Sub Workbook_BeforePrint(Cancel As Boolean) Cancel = True With Worksheets("sheet1") Application.EnableEvents = False .PrintPreview .PageSetup.CenterFooter = "" & .Range("A1").Text .PrintOut Application.EnableEvents = True End With End Sub Of course the problem here is that you get a preview that you need to respond to. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Newbeetle" wrote in message ... Hi the code below is an abstract from a macro I use to automatically page numbers when printing, Sub workbook_BeforePrint(Cancel As Boolean) With Worksheets("sheet1") .PageSetup.CenterFooter = "" & .Range("A1").Text End With With Worksheets("sheet2") .PageSetup.CenterFooter = "" & .Range("a1").Text End With 'etc etc End Sub The macro functions as required and is placed within Thisworkbook. As the number of pages have grown to 20, the macro will only function if I press print preview before printing, otherwise the page numbering is not updated. I'm guessing the print function is quicker than the macro and it doesn't get chance to complete. Not the end of the world, but I just wondered if there was a way to get around this to allolw the macro enough time to complete before the printing starts? -- This post was created using recycled electrons! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question with existing page numbering macro?
Hi Newbeetle,
Try suspending recalcs and using a loop, like: Sub workbook_BeforePrint(Cancel As Boolean) Dim wsSheet As Worksheet ' Stop Re-Calcs Application.Calculation = xlManual ' Loop through all sheets For Each wsSheet In ActiveWorkbook.Worksheets wsSheet.PageSetup.CenterFooter = "" & wsSheet.Range("A1").Text Next wsSheet ' Restore Re-Calcs Application.Calculation = xlAutomatic End Sub Cheers -- macropod [MVP - Microsoft Word] "Newbeetle" wrote in message ... | Hi the code below is an abstract from a macro I use to automatically page | numbers when printing, | | Sub workbook_BeforePrint(Cancel As Boolean) | With Worksheets("sheet1") | .PageSetup.CenterFooter = "" & .Range("A1").Text | End With | | With Worksheets("sheet2") | .PageSetup.CenterFooter = "" & .Range("a1").Text | End With | | 'etc etc | | End Sub | | The macro functions as required and is placed within Thisworkbook. | | As the number of pages have grown to 20, the macro will only function if I | press print preview before printing, otherwise the page numbering is not | updated. I'm guessing the print function is quicker than the macro and it | doesn't get chance to complete. | | Not the end of the world, but I just wondered if there was a way to get | around this to allolw the macro enough time to complete before the printing | starts? | | | -- | This post was created using recycled electrons! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question with existing page numbering macro?
Hi Guys thanks for the help,
Bob I tried this, funny thing is when the print preview happens this way it still has the wrong page numbers, if I use the print preview button built in to excel it updates ok ready to be printed. Macropod, that looping macro certainly has cut the script down, as you could expect the way I was doing the code meant for 20 pages it was very long, so thats a major help. Weird thing again though pages still don't show the correct numbering unless I do manual print preview first. Well its not a major thing, I put a note on it for the other users to print preview first, thanks for the help and advice, both are useful for other things I'm playing around with, and it keeps me thinking. -- This post was created using recycled electrons! "Newbeetle" wrote: Hi the code below is an abstract from a macro I use to automatically page numbers when printing, Sub workbook_BeforePrint(Cancel As Boolean) With Worksheets("sheet1") .PageSetup.CenterFooter = "" & .Range("A1").Text End With With Worksheets("sheet2") .PageSetup.CenterFooter = "" & .Range("a1").Text End With 'etc etc End Sub The macro functions as required and is placed within Thisworkbook. As the number of pages have grown to 20, the macro will only function if I press print preview before printing, otherwise the page numbering is not updated. I'm guessing the print function is quicker than the macro and it doesn't get chance to complete. Not the end of the world, but I just wondered if there was a way to get around this to allolw the macro enough time to complete before the printing starts? -- This post was created using recycled electrons! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Page Numbering Question | Excel Discussion (Misc queries) | |||
start page numbering from page 2 in excel | Excel Discussion (Misc queries) | |||
Question with existing print macro | Excel Discussion (Misc queries) | |||
Page numbering question | Excel Discussion (Misc queries) | |||
Revised Question on Page Numbering | Excel Programming |