Guys,

I'm tearing my hair out over this one - I just can't see what I'm doing
wrong.

I'm trying to alter the PageSetup properties in a Workbook_BeforePrint
event, but no matter what I do I can't seem to get VBA to actually
change the settings. After the code is run, the page looks the same as
it always has. I've run a watch on the PageSetup object and its
properties seem unaffected by my code. Yet ActiveSheet.Name returns
the name of the sheet I'm trying to alter. What's going on??


' QUOTE

' Code in Module1
' -----

Public PrintMini as Boolean


Sub TogglePrint()

PrintMini = True
ActiveSheet.PrintOut preview:=True

End Sub


' Code in ThisWorkbook
' -----

Sub Workbook_BeforePrint(Cancel As Boolean)

Application.ScreenUpdating = False

With ActiveSheet.PageSetup

GetLastRow ActiveSheet ' Returns a value for public integer LastRow

If PrintMini = True Then
.PrintArea = "A1:I" & LastRow + 4
PrintMini = False
Else
.PrintArea = "A1:AF" & LastRow + 4
End If

.FitToPagesTall = Int(LastRow / 40) + 1
.Zoom = False
.FitToPagesWide = 1
.CenterHorizontally = True
.Orientation = xlLandscape
.PrintTitleRows = ActiveSheet.Range("1:2").Address

End With

Application.ScreenUpdating = True

End Sub

' END QUOTE

Thanks in advance,

Tristan

Your code certainly effects the page layout, but without know the value of
LastRow, hard to say what should happen.
Add a "Debug.Print LastRow" to see if it the expected value.

NickHK

wrote in message
oups.com...
Guys,

I'm tearing my hair out over this one - I just can't see what I'm doing
wrong.

I'm trying to alter the PageSetup properties in a Workbook_BeforePrint
event, but no matter what I do I can't seem to get VBA to actually
change the settings. After the code is run, the page looks the same as
it always has. I've run a watch on the PageSetup object and its
properties seem unaffected by my code. Yet ActiveSheet.Name returns
the name of the sheet I'm trying to alter. What's going on??


' QUOTE

' Code in Module1
' -----

Public PrintMini as Boolean


Sub TogglePrint()

PrintMini = True
ActiveSheet.PrintOut preview:=True

End Sub


' Code in ThisWorkbook
' -----

Sub Workbook_BeforePrint(Cancel As Boolean)

Application.ScreenUpdating = False

With ActiveSheet.PageSetup

GetLastRow ActiveSheet ' Returns a value for public integer LastRow

If PrintMini = True Then
.PrintArea = "A1:I" & LastRow + 4
PrintMini = False
Else
.PrintArea = "A1:AF" & LastRow + 4
End If

.FitToPagesTall = Int(LastRow / 40) + 1
.Zoom = False
.FitToPagesWide = 1
.CenterHorizontally = True
.Orientation = xlLandscape
.PrintTitleRows = ActiveSheet.Range("1:2").Address

End With

Application.ScreenUpdating = True

End Sub

' END QUOTE

Thanks in advance,

Tristan

Nick,

LastRow returns 110 in the case of ActiveSheet. This is as it should
be. Still no change to the PageSetup properties, however.

Tristan



NickHK wrote:
Your code certainly effects the page layout, but without know the value of
LastRow, hard to say what should happen.
Add a "Debug.Print LastRow" to see if it the expected value.

NickHK

In fact, by way of proof that it has no effect, here is a Debug.Print
of ActiveSheet.PageSetup.PrintArea before and after this line

.PrintArea = "A1:I" & LastRow + 4

is executed:

BEFORE

?ActiveSheet.PageSetup.PrintArea
$A$1:$AF$114

AFTER

?ActiveSheet.PageSetup.PrintArea
$A$1:$AF$114

Tristan

What happens when you set a breakpoint in Workbook_BeforePrint and step
through the code?

In article .com,
" wrote:

Guys,

I'm tearing my hair out over this one - I just can't see what I'm doing
wrong.

I'm trying to alter the PageSetup properties in a Workbook_BeforePrint
event, but no matter what I do I can't seem to get VBA to actually
change the settings. After the code is run, the page looks the same as
it always has. I've run a watch on the PageSetup object and its
properties seem unaffected by my code. Yet ActiveSheet.Name returns
the name of the sheet I'm trying to alter. What's going on??


' QUOTE

' Code in Module1
' -----

Public PrintMini as Boolean


Sub TogglePrint()

PrintMini = True
ActiveSheet.PrintOut preview:=True

End Sub


' Code in ThisWorkbook
' -----

Sub Workbook_BeforePrint(Cancel As Boolean)

Application.ScreenUpdating = False

With ActiveSheet.PageSetup

GetLastRow ActiveSheet ' Returns a value for public integer LastRow

If PrintMini = True Then
.PrintArea = "A1:I" & LastRow + 4
PrintMini = False
Else
.PrintArea = "A1:AF" & LastRow + 4
End If

.FitToPagesTall = Int(LastRow / 40) + 1
.Zoom = False
.FitToPagesWide = 1
.CenterHorizontally = True
.Orientation = xlLandscape
.PrintTitleRows = ActiveSheet.Range("1:2").Address

End With

Application.ScreenUpdating = True

End Sub

' END QUOTE

Thanks in advance,

Tristan

What happens when you set a breakpoint in Workbook_BeforePrint and step
through the code?

It steps through fine. VBA doesn't throw up any errors, it all seems
to execute without a hitch - it's just that the properties don't seem
to be in the least bit affected by the code.

One thing to mention is that this did work okay once upon a time - the
current PageSetup properties were initially set by this code. But now
they won't change, so if I alter, say, Orientation to xlPortrait, it
still comes out as landscape. The main aim of this code is to change
the PrintArea depending on whether it is just being Printed or Print
Previewed normally or whether it has been called through the
TogglePrint routine, but I can't change it at all.

I've also tried using ThisWorkbook.Names to delete and then add a
"Print_Area" name with the appropriate string value. But though it
deleted the Name and didn't throw up an error at the .Names.Add line,
no new Name appeared.

Very peculiar.

Tristan