[RESOLVED] Set The Scaling In Excel Spreadsheet

In the Page Setup of an Excel Worksheet, there is a frame called "Scaling"

There are two option buttons:

1. Auto adjust to [number up/down control]% normal size
2. Fit to: [number up/down control] page(s) wide by [number up/down control] tall.

I need to know how to programmatically set Number two.

Here is what I have. It does not generate any errors, but it also does not work.

VB Code:

bkWorkBook.Worksheets(1).PageSetup.FitToPagesWide = 1
        bkWorkBook.Worksheets(1).PageSetup.FitToPagesTall = 1

I also tried

VB Code:

shWorkSheet.PageSetup.FitToPagesWide = 1
        shWorkSheet.PageSetup.FitToPagesTall = 1

Again, this generated no errors, but also did not work.

The following declarations apply

VB Code:

Private bkWorkBook As Workbook
Private shWorkSheet As Worksheet

Hack
This is an easy one. These properties of the pagesetup object are ignored if the Zoom property is set to TRUE. You need to change that property to FALSE before you set these. Other than that, either synthax above is good. (I've been caught by this many times...)

VB Code:

With bkWorkBook.Worksheets(1).PageSetup
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 1
End With

:lol: ZOOM Property??

What the heck is the ZOOM property?

Zoom is the percentage value that the user can enter in the Auto adjust to [number up/down control]% normal size control on the Page Setup.
If Excel sees any value in that property it will adjust the page size to that value. The only way to force it to use your FitToPage values is to set that property to FALSE.

Quote:

---

Originally Posted by DKenny

Zoom is the percentage value that the user can enter in the Auto adjust to [number up/down control]% normal size control on the Page Setup.
If Excel sees any value in that property it will adjust the page size to that value. The only way to force it to use your FitToPage values is to set that property to FALSE.

---

VBA can be a humbling experience. :D

Thanks again.