Formatting Macro based on Zoom (PageScale)

thundercats9595 · #1 01-29-2014, 04:12 PM

I am new and inexperienced with programing, so I apologize for using the wrong terminology in advance.

I would like to create a Formatting macro that would set the worksheet to (1) Page Wide by (unknown) Tall on Letter size paper Landscape.

Once the formatting is complete, I would like it then to add (+1) page wide if the Zoom (Page Scaling) is under 40%.

If Page Scaling is still under 40% after that add (+1) more page to Wide.

Is this possible with a macro? And if so, what are the basic steps to set up this formula?

I have the below at the moment which sets a worksheet (1) page Wide by (False) Tall, Letter, Landscape.

Code:

 
 
Sub Formatting()
 
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
 
        .Orientation = xlLandscape
        .PaperSize = xlPaperLetter
        .Order = xlOverThenDown
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
 
    End With
    Application.PrintCommunication = True
End Sub

Again, any and all assistance is very much appreciated.

Thank you.

macropod · #2 02-01-2014, 08:49 PM

It's not entirely clear what you mean by the (+1) references. However, assuming you're wanting to increase the zoom in 1% increments, try:

Code:

Sub Formatting()
Application.ScreenUpdating = False
With ActiveSheet.PageSetup
  .Orientation = xlLandscape
  .PaperSize = xlPaperLetter  
  .Zoom = False
  .FitToPagesWide = 1
  .FitToPagesTall = False
  If .Zoom < 40 Then .Zoom = .Zoom + 1
  If .Zoom < 40 Then .Zoom = .Zoom + 1
End With
Application.ScreenUpdating = True
End Sub

BobBridges · #3 02-01-2014, 09:00 PM

Heck, if he wanted to increase Zoom until it was up to 40, all he'd have to do is say

Code:

 If .Zoom < 40 Then .Zoom = 40

(I speak under correction, because I've never done anything with Zoom in VBA.) I think he's saying that he wants to keep on adding pages until the zoom is at least 40%. I was staring at this earlier this evening — feeling sorry for him because none of us had answered him, I guess — and didn't know what to say because I don't know that adding pages (whatever that means) would affect the zoom.

In other words, he wants a loop like this:

Code:

Do While .Zoom < 40
  .FitToPagesWide = .FitToPagesWide + 1
  Loop

But would that affect the zoom? I don't know how that works.

macropod · #4 02-01-2014, 09:05 PM

Quote:

Originally Posted by BobBridges

Heck, if he wanted to increase Zoom until it was up to 40, all he'd have to do is say

Code:

 If .Zoom < 40 Then .Zoom = 40

(I speak under correction, because I've never done anything with Zoom in VBA.) I think he's saying that he wants to keep on adding pages until the zoom is at least 40%.

That's not how I read it. As posted, if the zoom is less than 40%, increase by 1%. Recheck. If the zoom is still less than 40%, increase by 1% and exit. Thus, if the zoom started out at 36%, it would be increased to 38% only. Similarly, if it starts at 39.5% it would be increased to 40.5%.

BobBridges · #5 02-02-2014, 06:59 AM

The key sentence is probably this:

Quote:

If Page Scaling is still under 40% after that add (+1) more page to Wide.

There and previously he said he wanted to add a page ... whatever that means. On the other hand you may be right about doing it only twice; I read into that sentence a sort of "...and so on" to indicate that he wants to keep doing it until the Page Scaling is up to 40%, but he didn't actually say it.

thundercats9595 · #6 02-03-2014, 08:14 AM

Yes. This is what I was after.

Quote:

If Page Scaling is still under 40% after that add (+1) more page to Wide.

Been toying with it, and although it works, the worksheet first needs a Page Wide and a Page Tall to first know the Zoom percentage. At the moment, without those two items, the scaling formula will not work. So im not sure if this is worth pursing further. Thanks for all the input, im learning a lot due to these forums. Thank you.

BobBridges · #7 02-06-2014, 09:49 PM

Seems to me, then, is all you have to do is figure out how to add a page-wide and page-tall in your program, and then start the loop. Do you know how? You should be able to do it by recording a macro, and then modifying it from there.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Word VBA Macro to Find and Replace based on the Alt Text of an Image	bennymc	Word VBA	1	01-27-2014 04:23 PM
MACRO - Insert row based on Form Field Criteria	Elan05	Word VBA	5	04-16-2013 06:39 AM
Conditional formatting question based on cell date	Cosmo	Excel	2	04-08-2013 12:12 PM
Macro based on cell value	ubns	Excel Programming	1	05-07-2012 04:03 AM
Conditional Formatting Expiration Dates Based on Text in Adjacent Cell	Frogggg	Excel	1	10-25-2011 08:44 PM