Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    pagesetup.zoom (2000 sp3)

    I looked for other posts and can't seem to find exactly what I'm looking for. I want to get the value of pagesetup.zoom in my VBA code. This is easy when the option button next to that (labeled "Adjust to:" in the "scaling"section) is selected, but when the "Fit to:" option is selected the zoom property is False, even though the percentage is visible.
    It appears to me that if I change the number of pages to fit to, or change the print area, the scaling percentage changes even if the "Adjust to:" option is not selected. For this reason it appears to me that the percentage is not only updated but also valid, and I want it!
    Any ideas?
    Thanks for any and all suggestions!

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: pagesetup.zoom (2000 sp3)

    If you set the pagesetup to Adjust to instead of Fit to in the code and then pull the percentage and change pagesetup back to Fit to, are you able to pull the percentage and keep the original Fit To dimensions as well?

  3. #3
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: pagesetup.zoom (2000 sp3)

    Nice idea but I couldn't figure out how to set it to "Adjust to:" in the code. Any ideas?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: pagesetup.zoom (2000 sp3)

    Try this:

    With Application.Dialogs(xlDialogPageSetup)
    SendKeys "%A~"
    .Show
    Debug.Print ActiveSheet.PageSetup.Zoom
    SendKeys "%F~"
    .Show
    End With

    SendKeys is a bit tricky, but it usually works. The code opens the Page Setup dialog, and sends the keystrokes Alt+A (of Adjust) followed by Enter to it. The zoom percentage is printed to the Immediate window; in real code you'd assign it to a variable. Then it opens the dialog again and sends Alt+F (for Fit) followed by Enter to restore the original setting.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: pagesetup.zoom (2000 sp3)

    I don't think that will work. This amounts to the same thing as setting the zoom to 100 directly with the PageSetup.zoom property.

    The problem seems to be that when the ZOOM is not selected it is FALSE and = 100 in the PageSetup dialog. If you run the "Setup" while in PrintPreview mode, then the ZOOM is calculated for what it will be when printed.

    The problem is that when you are in printpreview mode, you can't use sendkeys or access the zoom in VB (you are "locked" in a "User Interface mode" The code stops until you leave this mode. Once you leave the preview mode the calculated zoom is not available. It seems to be one of the numbers that XL calculates "on the fly" and does not store anywhere (like autofilter lists). It seems that it is determined whenever it is going to be printed, it is used and then discared.

    Steve

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: pagesetup.zoom (2000 sp3)

    I'm using Excel 2002 SP3. When Fit to m by n pages is selected, the Zoom percentage is grayed out but calculated on the basis of the m by n. The VBA PageSetup.Zoom property will return False. Pressing Alt+A(djust) in the Page Setup dialog will enable the Zoom percentage, still set to the value calculated from the m by n pages. PageSetup.Zoom will now return the correct value. Opening the Page Setup dialog again and pressing Alt_F(it) will restore the Fit to m by n pages setting.
    Perhaps it doesn't work that way in other versions of Excel.

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: pagesetup.zoom (2000 sp3)

    I'm using Excel 2000 SP3 - that's how it works on my machine, too (at least in the page setup dialogue). The zoom values are retained when switching from fit to and adjust.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: pagesetup.zoom (2000 sp3)

    I also am using XL2002 SP3. When I fit, the zoom is not "greyed out", the option is just not pressed but the zoom is available to set (changing it will change the option button).

    It seems to work when I have to reduce the size due it too long, but if it just too wide, the pageSetup.zoom often stays 100 (even though the "setup" in Preview changes...

    Steve

  9. #9
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: pagesetup.zoom (2000 sp3)

    I haven't read through the rest of the posts yet (I was in a meeting) but I can see that the keystrokes work manually (you are good! Thanks) but I haven't tried it in code yet.
    One question though, any idea if this is language dependent?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: pagesetup.zoom (2000 sp3)

    Yes, it is language dependent. I have to use Alt+V and Alt+A instead of Alt+A and Alt+F in my Dutch language version of Excel.

    The zoom percentage is not exposed in the VBA object model if "Fit to" has been selected. The use of SendKeys is a clunky workaround, it is language-dependent and apparently it doesn't always work - see Steve's replies. So I would hesitate before using it in a critical application.

  11. #11
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: pagesetup.zoom (2000 sp3)

    Hans, buddy! You are Da Man! Not only does this work really well, but you also taught me about Debug.Print which, for some reason, I never saw before. Thank You very much!
    I'm still a bit concerned about the possibility that Alt-A and Alt-F might change in different countries but for now, this is great! Thank you again.

  12. #12
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: pagesetup.zoom (2000 sp3)

    Oh, I almost hate to ask this next question: Can I interrogate for the language and is there a list of the commands for each Language? Somewhere along the line I remember seeing an article about language dependencies. I should have saved it. I think I saw a huge list in the "hidden methods" of languages which could make attempting this impractical.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: pagesetup.zoom (2000 sp3)

    Excel has an International property of the Application object. You can use this to obtain information about regional settings, for example

    Application.International(xlCountryCode)

    will return a number code for the language version of Excel (USA=1, UK=44, etc.)

    But I don't know if a list of all accelerator keys for dialogs in different languages is available. How many languages do you have to support?

  14. #14
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: pagesetup.zoom (2000 sp3)

    I'm not completely sure how many but a rough estimate is 10 to 15.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: pagesetup.zoom (2000 sp3)

    That shouldn't be too bad - you could ask a user/customer from each country what accelerator keys their Page Setup dialog uses.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •