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.

I would like to have this facility through an AddIn so I can make it
available globaly


TIA
Soniya

See reply in excel.misc


--

HTH

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


"Soniya" wrote in message
oups.com...
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.

I would like to have this facility through an AddIn so I can make it
available globaly


TIA
Soniya

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


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

"Soniya" wrote in message
oups.com...

only column width changes and not row height?!!

Uh?

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

Add this to the addin

Option Explicit

Dim WithEvents app As Application

Private Sub Workbook_Open()
Set app = Application
End Sub

Private Sub app_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)
Dim aryWidths(1 To 13)
Dim nRow As Double
Dim i As Long
With Wb.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
.Printout
.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

Thanks Again. It works fine
except as I noted earlier Row heigh sees not changing
Also when I try to print a sheet using the code

Sheets("ABC").PrintOut instead of ActiveSheet, how I cud modify the
code to get it work?

Thanks again

"Soniya" wrote in message
ups.com...
Thanks Again. It works fine
except as I noted earlier Row heigh sees not changing

It works for me. Perhaps you need a bigger height.

Also when I try to print a sheet using the code

Sheets("ABC").PrintOut instead of ActiveSheet, how I cud modify the
code to get it work?

Why would you not want to print the activesheet?

Normally it will be the ActiveSheet. But I (and some users) have
already some working files in which there are several lines of code
where I used sheets("SheetName").PrintOut. at that time i thought it
will be more fast and saving lines of coding. Especially when you have
to do the printing from another sheet and come back to the same sheet
after the printing I think it is easy to print without selecting the
sheet itself.

I use the activesheet to fill a form and based on a value in my cell it
prints a specific sheet.It can be one out of several sheets.

I think there would be a wayout to do this rather than modifying code
in other sheets. Exxcel could identify which sheet is going to print
and in that sheet before printing i want to adjust the widths.


thanks for your assistance.

Soniya