Before Print and UnDo

[Melepoil]

Hi all,

Is it possible to change the row height and column width of a range in
a sheet when the user Print the sheet and return it back to the
original state?

like for eg. when the user executes the print command change the row
hight to 20 and column width to 10 on my range A1:M20 and return it to
what ever width and height was it previously.

TIA
Soniya

 

[Bob Phillips]

Hi Soniya,

This should do it


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim aryWidths(1 To 13)
Dim nRow As Double
Dim i As Long
With ActiveSheet
Application.EnableEvents = False
Application.ScreenUpdating = False
For i = 1 To 13
aryWidths(i) = .Columns(i).ColumnWidth
.Columns(i).ColumnWidth = 10
Next i
nRow = .Rows(1).RowHeight
.Rows(1).RowHeight = 20
Cancel = True
.PrintPreview
.Rows(1).RowHeight = nRow
For i = 1 To 13
.Columns(i).ColumnWidth = aryWidths(i)
Next i
Application.ScreenUpdating = True
Application.EnableEvents = True
End With

End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code



--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Melepoil]

thanks for your kind reply..
only column width changes and not row height?!!


is it possible to make this an addin so i can have this on every
workbook?


thanks

 

[Dave Peterson]

Bob's code did change the height of row 1 for me.

You may not have noticed it since it was row 1--it looked like the top margin
had dropped down a bit for me.

If you put borders around row 1 (or give it a nice fill color), it'll be easier
to see.

And Bob's code could be made into an addin that does work with every workbook,
but this seems way to drastic (for even me). If I were you, I'd only add it to
the workbooks that needed that ability.

In fact, I'd be even more restrictive. I'd specify the names of the worksheets
that I want this to work on.

 

[JE McGimpsey]

See

ftp://ftp.mcgimpsey.com/excel/PrintSize.xla

The add-in changes row heights back as well.

It uses a WIthEvents class to operate on all workbooks.

You can set the number of rows, number of columns, print width of
columns, and print height of rows in the "Globals" module.

 

[Dave Peterson]

I don't think I would have used ROWHEIGHT as a constant name

Public Const ROWHEIGHT As Long = 20

It looks too much like the .rowheight property.

Confusing for excel/VBA--maybe not. Confusing for me--definitely.

 

[JE McGimpsey]

Yeah, I normally don't. I usually use modified hungarian, even for
constants.