Page Setup/Print Preview vba question

[dgolds]

I am testing some code to have my sheets print consistently on different computers. I have

    With ActiveSheet.PageSetup
        .CenterHorizontally = True
        .Orientation = xlPortrait
        .FitToPagesWide = 1
    End With
    ActiveSheet.PrintPreview

but it still previews at more than 1 page wide. Any suggestions?
Thanks,
Dave

 

[RalphA]

The "FitToPagesWide = 1" is probably doing just that! There might be a dot or a letter "in the wrong place", causing the width to expand beyond what you expect.

Highlight the columns to the right of the last column you are expecting to see in the first page, and delete all columns, then, try again.

And, please let us know if this solved the problem.

 

[dgolds]

Sorry, I probably didn't explain very well in my first post. The sheet in question has a print area already defined (the height or number of rows in the print area is dynamic but the width or number of columns of the print area is constant). The print area has always worked as expected and does in this case too, but the problem I'm running into when I test the above code is that I was hoping that the code would fit all of the columns in the print area to 1 sheet wide and it doesn't. It still correctly prints only columns A - E as defined in the print area, but although the current length of the sheet is 2 pages it uses four pages and prints columns A-D on pages 1-2 and column E on pages 3-4. This is precisely the problem that I was hoping the line "FitToPagesWide = 1" would take care of.
Thanks for any suggestions
Dave G.

 

[RalphA]

I now understand your problem. To try to get to the crux of the matter, I changed your Sub to this one:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    ActiveSheet.PageSetup.FitToPagesWide = 1
    ActiveSheet.PrintPreview
End Sub

I set File/Page Setup, Page, Option Button Adjust to 100% normal size.

I clicked the sheet, and...yes, I got the normal, one page. I then remarked out the second line in the above code, and clicked on the spreadsheet again. Yes, same result.

Only when I did a File/Page Setup, Page, Option Button Tit to: "1 page(s) wide by 1 tall". Now, I get the whole active part of the spreadsheet in the Print Preview.

Hmm, can it be that the code line
ActiveSheet.PageSetup.FitToPagesWide = 1
produces NO action whatsoever? It certainly seems so, from my brief encounter with it.

Perhaps one of the gurus watching us poor strugglers will dip in and help?

 

[dgolds]

Hi Ralph,
That was my experience too. I originally trapped the code by recording a macro and then I deleted the numerous lines of code that were simply default settings. I also deleted the line 'FitToPagesTall = 1' because the number of pages tall will vary, but I wanted the number of pages wide to always be 1. Perhaps like the option buttons that I used when I recorded the macro, it needs to know how tall to make it too. I'll test further.

After further testing, if I include the line 'FitToPagesTall = 1' then the code works as I originally hoped and squeezes everything onto one page. The problem is, I want the number of pages tall to vary with the number of rows of data that are entered. I wonder if there is some code I could use to count the number of page breaks (from top to bottom but not from side to side) before running the page setup code and then passing that number as a variable to the line 'FitToPagesTall = variablename'
Thanks again for any ideas or suggestions,
Dave

 

[Juan Pablo González]

I think you have to set the Zoom property to FALSE first, then set the FitToPagesWidth and/or the FitToPagesHeight properties.

 

[dgolds]

Thanks, Juan Pablo. I'll give it a try when I get back home tomorrow and let you know how it goes.
-Dave

 

[dgolds]

Thanks, Juan Pablo. I finally got a chance to test your suggestion and it now works exactly how I want. I changed my code to

    With ActiveSheet.PageSetup
        .Zoom = False
        .CenterHorizontally = True
        .Orientation = xlLandscape
        .FitToPagesWide = 1
        .FitToPagesTall = False
    End With
    ActiveSheet.PrintPreview

I have a related question. I want to put this code into a custom print preview button but I don't want the user to have the option to mess with the page setup options while they're in Print Preview. Is there any way (through VBA or otherwise) to disable everything in Print Preview except the window that shows the preview?
Thanks,
Dave

 

[Juan Pablo González]

You can use

ActiveSheet.PrintPreview EnableChanges:=False

to disable that.

 

[dgolds]

Thanks!
That took care of it. The only problem I'm having now is that after I added the line

EnableChanges:=False

the active sheet prints every time I click on the print preview button.
Dave