recalculate page breaks after setting Zoom in pagesetup

Cross posted since I have gotten no responce in
microsoft.public.excel.programming for 3 days

Ok I've seen the 1,000s of people trying to program page setup
options
and having a bad time.  So let me add more problems.

I was using Excel 2000.  I now have 2007.  I had a workaround for
this
bug in 2000/2003 but I can't seem to get around it in 2007 (and of
course it isn't fixed)


Here is what I'm trying to do:
   Set the page to Landscape, 1 page wide
   Then add some intelligent page breaks by looking for the automatic
page break and putting a manual break above it where the data
recently
changed.


The problem is that the automatic page break is not recalculated
after
setting the zoom factor programatically.


if I do:
       With ActiveSheet.PageSetup
       .Orientation = xlLandscape
        .Zoom = 65
    End With


the first page break is on row 33


In 2000/2003 I could :
    SendKeys "%(FUA)" + Format(ActiveSheet.PageSetup.Zoom) + "~",
True


This is a crappy way to get around it but after the sendkeys the
first
page break would not be row 53


I cannot get a key stroke method to the page setup dialog box in
Excel2007 to work from sendkeys (I can do it manually but that
doesn't
help)


Anybody know how to get Excel to recalculate the page breaks?
Obviously something is being called from teh page setup dialog box
that I can't find directly.


Thanks for any help.
Pete

0
par_60056 (1)
6/21/2007 9:41:16 AM
excel 39879 articles. 2 followers. Follow

3 Replies
274 Views

Similar Articles

[PageSpeed] 49

Does this help?:

With Sheet1.PageSetup
    .Zoom = 65
    .PaperSize = .PaperSize
End With


-- 
Jim
"Par" <par_60056@comcast.net> wrote in message 
news:1182418876.517941.309510@q69g2000hsb.googlegroups.com...
| Cross posted since I have gotten no responce in
| microsoft.public.excel.programming for 3 days
|
| Ok I've seen the 1,000s of people trying to program page setup
| options
| and having a bad time.  So let me add more problems.
|
| I was using Excel 2000.  I now have 2007.  I had a workaround for
| this
| bug in 2000/2003 but I can't seem to get around it in 2007 (and of
| course it isn't fixed)
|
|
| Here is what I'm trying to do:
|   Set the page to Landscape, 1 page wide
|   Then add some intelligent page breaks by looking for the automatic
| page break and putting a manual break above it where the data
| recently
| changed.
|
|
| The problem is that the automatic page break is not recalculated
| after
| setting the zoom factor programatically.
|
|
| if I do:
|       With ActiveSheet.PageSetup
|       .Orientation = xlLandscape
|        .Zoom = 65
|    End With
|
|
| the first page break is on row 33
|
|
| In 2000/2003 I could :
|    SendKeys "%(FUA)" + Format(ActiveSheet.PageSetup.Zoom) + "~",
| True
|
|
| This is a crappy way to get around it but after the sendkeys the
| first
| page break would not be row 53
|
|
| I cannot get a key stroke method to the page setup dialog box in
| Excel2007 to work from sendkeys (I can do it manually but that
| doesn't
| help)
|
|
| Anybody know how to get Excel to recalculate the page breaks?
| Obviously something is being called from teh page setup dialog box
| that I can't find directly.
|
|
| Thanks for any help.
| Pete
| 


0
jrrech (1932)
6/21/2007 1:45:14 PM
Jim,
That is incredible.  It works but I have no idea why.  It is such an
obvious solution I don't know why I didn't think of it (NOT)

So I looked up the doc on papersize and it sure doesn't say anything
(surprising isn't it) that would lead me to try this.

Thanks a lot.  I have been fighting this problem in my spare time for
over a week

Pete

On Jun 21, 7:45 am, "Jim Rech" <jrr...@hotmail.com> wrote:
> Does this help?:
>
> With Sheet1.PageSetup
>     .Zoom = 65
>     .PaperSize = .PaperSize
> End With
>
> --
> Jim"Par" <par_60...@comcast.net> wrote in message
>
> news:1182418876.517941.309510@q69g2000hsb.googlegroups.com...
> | Cross posted since I have gotten no responce in
> | microsoft.public.excel.programming for 3 days
> |
> | Ok I've seen the 1,000s of people trying to program page setup
> | options
> | and having a bad time.  So let me add more problems.
> |
> | I was using Excel 2000.  I now have 2007.  I had a workaround for
> | this
> | bug in 2000/2003 but I can't seem to get around it in 2007 (and of
> | course it isn't fixed)
> |
> |
> | Here is what I'm trying to do:
> |   Set the page to Landscape, 1 page wide
> |   Then add some intelligent page breaks by looking for the automatic
> | page break and putting a manual break above it where the data
> | recently
> | changed.
> |
> |
> | The problem is that the automatic page break is not recalculated
> | after
> | setting the zoom factor programatically.
> |
> |
> | if I do:
> |       With ActiveSheet.PageSetup
> |       .Orientation = xlLandscape
> |        .Zoom = 65
> |    End With
> |
> |
> | the first page break is on row 33
> |
> |
> | In 2000/2003 I could :
> |    SendKeys "%(FUA)" + Format(ActiveSheet.PageSetup.Zoom) + "~",
> | True
> |
> |
> | This is a crappy way to get around it but after the sendkeys the
> | first
> | page break would not be row 53
> |
> |
> | I cannot get a key stroke method to the page setup dialog box in
> | Excel2007 to work from sendkeys (I can do it manually but that
> | doesn't
> | help)
> |
> |
> | Anybody know how to get Excel to recalculate the page breaks?
> | Obviously something is being called from teh page setup dialog box
> | that I can't find directly.
> |
> |
> | Thanks for any help.
> | Pete
> |


0
6/21/2007 2:19:25 PM
Glad it works.  I tried it on a lark.  Just got lucky.

-- 
Jim
<par_60056@hotmail.com> wrote in message 
news:1182435565.165435.146740@n60g2000hse.googlegroups.com...
| Jim,
| That is incredible.  It works but I have no idea why.  It is such an
| obvious solution I don't know why I didn't think of it (NOT)
|
| So I looked up the doc on papersize and it sure doesn't say anything
| (surprising isn't it) that would lead me to try this.
|
| Thanks a lot.  I have been fighting this problem in my spare time for
| over a week
|
| Pete
|
| On Jun 21, 7:45 am, "Jim Rech" <jrr...@hotmail.com> wrote:
| > Does this help?:
| >
| > With Sheet1.PageSetup
| >     .Zoom = 65
| >     .PaperSize = .PaperSize
| > End With
| >
| > --
| > Jim"Par" <par_60...@comcast.net> wrote in message
| >
| > news:1182418876.517941.309510@q69g2000hsb.googlegroups.com...
| > | Cross posted since I have gotten no responce in
| > | microsoft.public.excel.programming for 3 days
| > |
| > | Ok I've seen the 1,000s of people trying to program page setup
| > | options
| > | and having a bad time.  So let me add more problems.
| > |
| > | I was using Excel 2000.  I now have 2007.  I had a workaround for
| > | this
| > | bug in 2000/2003 but I can't seem to get around it in 2007 (and of
| > | course it isn't fixed)
| > |
| > |
| > | Here is what I'm trying to do:
| > |   Set the page to Landscape, 1 page wide
| > |   Then add some intelligent page breaks by looking for the automatic
| > | page break and putting a manual break above it where the data
| > | recently
| > | changed.
| > |
| > |
| > | The problem is that the automatic page break is not recalculated
| > | after
| > | setting the zoom factor programatically.
| > |
| > |
| > | if I do:
| > |       With ActiveSheet.PageSetup
| > |       .Orientation = xlLandscape
| > |        .Zoom = 65
| > |    End With
| > |
| > |
| > | the first page break is on row 33
| > |
| > |
| > | In 2000/2003 I could :
| > |    SendKeys "%(FUA)" + Format(ActiveSheet.PageSetup.Zoom) + "~",
| > | True
| > |
| > |
| > | This is a crappy way to get around it but after the sendkeys the
| > | first
| > | page break would not be row 53
| > |
| > |
| > | I cannot get a key stroke method to the page setup dialog box in
| > | Excel2007 to work from sendkeys (I can do it manually but that
| > | doesn't
| > | help)
| > |
| > |
| > | Anybody know how to get Excel to recalculate the page breaks?
| > | Obviously something is being called from teh page setup dialog box
| > | that I can't find directly.
| > |
| > |
| > | Thanks for any help.
| > | Pete
| > |
|
| 


0
jrrech (1932)
6/21/2007 2:24:40 PM
Reply:

Similar Artilces:

Identical Name on multiple workbook pages
As a hardened Excel/VBA nut, I'm probably going to be really embarrased when I get the answer to this, but here goes... I have a workbook containing a worksheet that records weekly data. I have a named range ("Data") which I select then clear, via GoTo. Each week, I copy the latest week's worksheet and create a new worksheet for the new week. And each week, I select the range "Data" and clear it in the most recently created worksheet. the thing is, I always thought that Range names were unique across a workbook, and that you couldn't have the same Range N...

how do Ii remove the grey page numbers in a work sheet
how do Ii remove the grey page numbers in a work sheet Try going to the View menu and unchecking Page Break Preview On Wed, 2 Feb 2005 11:01:08 -0800, "parker1230" <parker1230@discussions.microsoft.com> wrote: >how do Ii remove the grey page numbers in a work sheet You may be in page break preview mode. Go to "View" and select "normal" Regards Bill K "parker1230" <parker1230@discussions.microsoft.com> wrote in message news:5A65FC73-CAA0-428D-8ECD-0C2A2E15B799@microsoft.com... > how do Ii remove the grey page numbers in a work she...

Underlying Connection was Closed when opening System Settings
I've seen a couple of threads on "Underlying Connection was Closed" when opening System Settings. I just found a solution not mentioned in those posts and thought I would add to the knowledge base. System Settings requires a connection to Microsoft SQL Reporting Server (SRS). If your SRS server is down or offline the above error will be displayed. Zack Jones Chief Architect Q1 Technology ...

Zoom percentage and formatting palette
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel 1. How do you make sure that Word opens to a certain zoom percentage (140% for me) no matter what Word document I open? <br><br>2. How can I get the formatting palette to not be a floating box but to be incorporated right into the toolbar? <br><br>Thanks from a new user On 24/05/2010 03:00, Jerdog201@officeformac.com wrote: > Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: > Intel 1. How do you make sure that Word opens to a certain zoom > percentage ...

Margin Trouble with Custom Page Size
When I try to print in Publisher using a custom page size, printer or Publisher adds approx 1.5" to the top of my page margin, no matter what I do. Checked the printer settings in WordPad, all looks OK. Issue does not occur when printing on 8.5X11 page, nor does it occur in MS Word. I don't have any extra margin set in Master Page...have tried everything I can think of. Under deadline to print invitations and getting desperate...help! Thanks. How are you setting up your printer? Are you saving the custom setting? Are you setting up your printer first? What model is your pr...

Try Catch to Pinpoint Set-Based Error?
SQL 2008. I have a fairly simple Merge statement with this: When Not Matched Then Insert Values ( ... long column list ... cast( crazyCol1 as decimal(16,8) ) / 1000000 ) , cast( crazyCol2 as decimal(16,8) ) / 1000000 ) cast( crazyCol2 as decimal(16,8) ) / 1000000 ) cast( crazyCol2 as decimal(16,8) ) / 1000000 ) ) The insert source is about 50,000 rows. One of the rows is failing the cast (original source is varchar(36). I would like to implement a try/catch block around this somehow so I can catch the offending row. How can I do that? Thanks. SnapDive (...

connecting to data access page with filter criteria
I have an access database that I connect to from a GIS application (ArcView) using VBscript. I need to port my GIS project to the free viewer (ArcReader) which does not support VBscript but does support hyperlinking to a URL based on a value supplied by an underlying record. In my current project I can send filter critera to open a particular database, form and record. Would it be possible to do the same using data access pages or would I need to create a separate page for each record I want linked? ...

Money 2005 Bug: Portfolio toolbar button loads blank page titled Portfolio Manager
Problem Description: Clicking on the Portfolio toolbar button loads a blank page that's titled Portfolio Manager. That's it! I can't see any of my open positions, etc... using this toolbar button. Frequency of problem: Always Happens Operating system / version: Microsoft Windows XP Professional - 5.1.2600 Computer manufacturer and model: Dell Computer Corporation, Dimension 8200 Processor model and speed: I586IV, 1994 MHz Memory (RAM): 523.276 MB The only input I can offer is that I have the same problem on one computer, but it works fine on the other. I converted from 2004 Delu...

Excel page format changes
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hello, <br><br>My client creates spreadsheets for her boss. She pushes the boundaries to get things to fit on the screen. <br> Uses A4, margins at 1.5cms and scales to 90%. <br><br>When her boss receives it by email the margins go to 2.5cms, scales to 100%. They have the same versions 12.2.4 and Leopard. They are just about to be upgraded to Snow Leopard. Any idea why this could be happening? Thanks. ...

Cell identity when printing comments on separate page
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel When printing comments at end of page, it identifies the cell by letters and numbers. (i.e. Cell: AA33) Afterward the comment is shown for that cell. <br><br>Question: Instead of the cell identity being shown this way, is it possible ithe cell can also be identified by its X axis header for that column with the y access header for that Row. <br><br>Thank for your assistance. <br><br>Carl Sorry Carl, but I'm not clear on what you're asking for... In 'MySpeak' ...

Outlook 2003 changing global auto archive settings?
Outlook offers to Run Auto Archive every days. It allows you to run it more frequently, but I would like to run it less frequently. In other words, I would like to run it every 120 days and then archive the bottom four months. It doesn't seem that you can change the auto archive setting above 60 days. Is there a way to do this? I have the box checked to ask me before auto archiving - but if I say no, will it continue to ask? Or, will it never ask again? Essentially I would like to archive every 4 months and not more frequently - and I would like to archive my inbox and my se...

International Range style settings
Hi there, I am using Excel 2007, the Ducth version. I have created a workbook with macros, partly recorded. In one of the I am using some code like With ActiveCell If .Value < gdblNormValue Then .Style = "Ongeldig" End If End With In the English version it shoul be With ActiveCell If .Value < gdblNormValue Then .Style = "Bad" End If End With Is there a constant I can ue instead of the text "Bad"? Thanks Wouter Styles have both Name and Local name properties, so I *think* the English name should work ...

Single set of folders in Navigation Pane?
In Outlook 2007, I have Exchange Server and 2 POP accounts. I would like all of the messages from all of the accounts to display in the navigation pane in the same folders and follow the same rules and alerts. Currently I have Mailbox fro exchange and Personal folders for the 2 POP accounts. Can someone help me out with this? Thanks! You can set your delivery location for each account in; Tools-> Account Settings...-> tab Email You'll have to decide if you want the default location to be a pst-file or the Exchange mailbox. If you choose for a pst-file, all the d...

How do I set up auto complete from a drop down list in Excel 2000.
I have a 10 sheet workbook that I will be sending out to various users around the world. For their convenience, we are adding a lot of drop down lists for them to choose from. However, since there are many options to choose from in each list we would like for it to auto complete based on their initial entries: Ed -> list to jump to Edinburgh, etc.... I have enabled the AutoComplete in the cell values button in the Tools/Options/Edit tab, but it is still not working. Any help on this matter would be greatly appreciated. Thanks Hi not possible for the data validation listboxes --...

Microsoft Word/Page orientation
In a multi-page document, is it possible to change continuous pages from a page orientation of portrait to landscape and back to portrait again? I have tried this with page breaks inserted but it has not worked yet. I am using Word 2000. Thank you. you need to ask in microsoft.public.word or i could tell you the answer :-) use section breaks "KC" <mykcdog@yahoo.com> wrote in message news:521501c42ca1$4ba0e950$a501280a@phx.gbl... > In a multi-page document, is it possible to change > continuous pages from a page orientation of portrait to > landscape and ba...

I need help setting up my database
I used Access in the past, so I have a general idea on how to use it, but now I have Microsoft 2007, so it's not as familiar. I currently have an excel document with staff names and drills they have performed. The drills include dates, times, an observer, and if they passed or failed. I had considered doing something where I had a table with all my staff, and then connecting tables for each person to list out the specifics for their drills. I don't know how to link the relationship with that though because the table name was the staff's name, so there wasn't a...

dropdown filter on Data Access Page
Hi, I have a data access page that is based on a query in my database. Currently the page brings up all data for today, and the page is formated into teams So it looks like this Today's date Team #1 entry #1 entry #2 Team #2 entry #1 Since this page can get long sometimes, I would like to add a dropdown menu at the top of the page, that allows them to filter their results by team. So once they make a selection they would only see the team selected. Today's date Team (only 1 listed) Entry #1 Entry #2 I've been trying to search on this, but jus...

Setting series values on Excel Chart
Hi I'm struggling very hard in getting beyond this error when trying to use VB to generate a chart from data previously inserted into the sheet. Unable to set the Values property of the Series class. The code works absolutely fine in XL2007 but I am having to port it back to 2003 and the inference is that its empty values that cause it to hiccup - something I cannot avoid. My code is: 1. Retrieve selected data from database and place it at the top of the sheet 2. Add The Chart 3. Iterate through the data (amount can be variable depending upon user inputs) For j as i...

Double emails when setting up new POP3
A long time problem I'd like to resolve is when setting up a new, or resetting an user name and password for a POP3 mail account. In all versions of outlook I have users who keep many months of email stored on the mail server. I don't mind them keeping it there, but for some of these people, when I setup a new logon name to the same mail account the Outlook program is somehow reset and does not know that it already has months of emails in its account. So, it goes ahead and downloads them all over again. Well, I've used Duplicate Email remover that works nicely to remove t...

How to set up a uato incremnt in sql server 5000 express
Hi, I'm trying to get into database programming. Cannot figure out how to do a auto int for my key value in sql server express. Ted On 19-12-2009 19:40, tedpottel wrote: > I'm trying to get into database programming. Cannot figure out how to > do a auto int for my key value in sql server express. Set IDENTITY on the field. Arne PS: This is a C# forum. "tedpottel" <tedpottel@gmail.com> wrote in message news:29a76b36-a911-425c-a46d-6f3c03ed5586@c3g2000yqd.googlegroups.com... > I'm trying to get into database programming. Cannot figu...

IE6 ignore font size setting = Outlook fixed 12-pt messages
Setting IE6's Accessibility option to Ignore font sizes specified on web pages (so I can read online newspaper text at my 1600x1200 resolution setting) produces an undesirable side effect in my Outlook 2K-generated html- format email messages: all message text is fixed at 12 point, even though my Outlook default message size is set at 10 point. Manually changing the font size in the message has no effect. Closest KB article I can find is one for Outlook Express (#178895), which indicates "this is by design." If so, why would someone who wants to vary the font size displa...

I do not want quarter fold invitation but rather 1/2 page folded .
HELP... I have search KB and cant get this right...I use Publisher 2003 and I want to do the following: use 81/2 X 11 cardstock to produce TWO folded note cards If I use the template in publisher for invitations my only options are to have it ended up at a quarter fold I tried pasting the avery template in publisher cant get it to work... I want these notecards with the fold at the top..please help aprylb@earthlink.net Use the custom setup. landscape, input 5.5 in the width box. This will give you two per page. In the arrange menu, set the margins for the largest margin of your printer&#...

how excel calculates the major units on an axis when MajorUnitIsAuto property set to true #2
I need to know, how excel calculates the major units on an axis when MajorUnitIsAuto property set to true. We are porting an existing excel chart application to .net with help of a third party tool for charting. If MajorUnitIsAuto property set to true, excel automatically calculates the major unit.Need to know what algorithm excel uses to calculate the Major Units. Thanks in advance, Mathew Microsoft has posted an article which tells how the minimum and maximum scale values are determined, in terms of this mysterious major unit, but I have seen nothing about how this major unit is calcula...

Vertical page numbers in a graph
I need to add vertical page numbers under page setup when printing a graph. Has anyone done this before? I definitely want it out of the graph area. Thanks - I don't understand the problem -- and I suspect a similar reason has stopped others from responding. If you set up a page number in the page header, it is not part of the chart area. In addition, after setting up the page information in the header, switch to Print Preview mode and click the margins... button. XL has a neat graphical way of adjusting various page borders/margins. -- Regards, Tushar Mehta, MS MVP -- E...

Set Clock to 24 Hour in CRM4
I have been tolf that it is not possible to have a 24 hour clock in CRM 4 ? I have 24 Hour clock in CRM 3 Can anyone help me with this please Time format is defined in Personal options and can be changed in CRM 4. Jukka Majander "LEZ Z1TECH" wrote: > I have been tolf that it is not possible to have a 24 hour clock in CRM 4 ? > > I have 24 Hour clock in CRM 3 > > Can anyone help me with this please ...