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

 

[dgolds]

Juan Pablo,
Actually, I was only having the problem with the sheet printing when I clicked on the regular print preview button (the code was fired because it is called from the before print event, which apparently print preview triggers). When I run it from a custom button, which was my original intent, it works fine without the problem of the sheet printing every time. So anyway, I now have everything working the way I want it to. Thanks again for your help.
Dave

 

[msvec]

This is really helpful. Not as important for me, but does anyone know how to disable Page Break Preview as well?

thx

 

[APOC [T.I.M.]]

ActiveSheet.PrintPreview EnableChanges:=False

Sub Macro1()
    With ActiveSheet.PageSetup 
        .Zoom = False 
        .CenterHorizontally = True 
        .Orientation = xlLandscape 
        .FitToPagesWide = 1 
        .FitToPagesTall = False 
    End With 
    ActiveSheet.PrintPreview EnableChanges:=False
End Sub

No Changes are Disabled, OR am I doing something wrong?
(using Excel 2002 sp3)

With kind regards, Tim.

 

[Richard Schollar]

Hi Tim

I'm using xl2003 and the code works as expected for me (ie changes are disabled - Setup and Margins buttons are greyed out). I can't see xl2002 being any different from 2003 in this regard. I am therefore not sure why this isn't working for you.

 

[APOC [T.I.M.]]

?

I used it in a macro, like stated above, I did it the right way? perhaps I did something wrong, I think not, but you never know.

edit: hmmmm, odd... seems to work now all of a sudden, weird. BUT thanx it seems to work now.

 

[APOC [T.I.M.]]

no exit/cancel?

I use this code now:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim rngToPrint As Range, rngLast As Range

With ThisWorkbook.Worksheets("DVD Lijssie")  ' EDIT with YOUR own Sheet name !!! !!! !!!
    .Activate
    Set rngLast = LastCell
    'establish last cell
    Set rngToPrint = Range(Range("A1"), rngLast)
    'set the actual print area required
End With

ActiveSheet.PageSetup.PrintArea = rngToPrint.Address
'NB use address to return A1 style
    Set rngLast = Nothing
    Set rngToPrint = Nothing
    
With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$3"
    End With
    ActiveSheet.PrintPreview EnableChanges:=False     ' Disable changes in PlotPreview
    With ActiveSheet.PageSetup
            .CenterHorizontally = True
            .CenterVertically = False
            .Orientation = xlLandscape
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = False
    End With
End Sub

Function LastCell() As Range
Dim LastColumn As Integer
Dim LastRow As Long
    If WorksheetFunction.CountA(Cells) > 0 Then
        'Search for any entry, by searching backwards by Rows.
        LastRow = Cells.Find(What:="*", After:=[A1], _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious).Row
        'Search for any entry, by searching backwards by Columns.
                LastColumn = Cells.Find(What:="*", After:=[A1], _
                    SearchOrder:=xlByColumns, _
                    SearchDirection:=xlPrevious).Column
    End If

Set LastCell = Cells(LastRow, LastColumn)
End Function

BUT when I click on plot-preview and when I choose Exit the document automatically gets printed... and that's not what I intent to when I choose Exit. How come this strange behaviour occurres?

This line seems to be the problem: ActiveSheet.PrintPreview EnableChanges:=False (with or without EnableChanges:=False doesn't matter)

 

[Richard Schollar]

That code should only be firing when you click on Print (as it is a Before_Print event) - so it will continue to print after the code has been executed as you haven't set the Cancel property to True.

 

[APOC [T.I.M.]]

How should I do that? What would you advise?
I'd like it to Exit when I want to Exit (cancel) and not to Print when I choose otherwise.

 

[APOC [T.I.M.]]

Cancel = True ???

Can't seem to get it right, yet...
I commented ActiveSheet.PrintPreview EnableChanges:=False out ('   ActiveSheet.PrintPreview EnableChanges:=False) for the moment so the rest of the code works. Now to find a way that Exit really Exits (Cancel) and or Changes are Disabled in PrintPreview

 

[APOC [T.I.M.]]

darn cancel

anyone? I still can't get it right