Print Area to include text box
Hi Paul,
Am Tue, 6 Jan 2015 07:45:06 -0800 (PST) schrieb Paul Doucette:
My current print area is A1 to AG50. The text box sits on A17 to AG50. And it prints fine. However, sometimes the text box gets resized to a17 to AK75 or beyond, so I need the print area to increase so that part of the text box does not get cut off.
or try:
Sub PrintArea2()
Dim boxBottom As Double, boxRight As Double
Dim LRow As Long, LCol As Long
Dim i As Long
Dim LCell As Range
With ActiveSheet
boxBottom = .TextBox21.Top + .TextBox21.Height
boxRight = .TextBox21.Left + .TextBox21.Width
'Checking for last row. Modify i
For i = 48 To 75
If .Range(.Cells(1, 1), .Cells(i, 1)).Height boxBottom Then
LRow = i
Exit For
End If
Next
'Checking for last column. Modify i
For i = 32 To 40
If .Range(.Cells(1, 1), .Cells(1, i)).Width boxRight Then
LCol = i
Exit For
End If
Next
Set LCell = .Cells(LRow, LCol)
.PageSetup.PrintArea = .Range(.Cells(1, 1), LCell).Address
End With
End Sub
Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
|