Before Print and UnDo

[Soniya]

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

[Bob Phillips]

See reply in excel.misc


--

HTH

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


"Soniya" <Melepoil@gmail.com> wrote in message
news:1129370369.545629.199280@g47g2000cwa.googlegroups.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
>

[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

[Bob Phillips]

"Soniya" <Melepoil@gmail.com> wrote in message
news:1129373934.142759.110890@g49g2000cwa.googlegroups.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

[Soniya]

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

[Bob Phillips]

"Soniya" <Melepoil@gmail.com> wrote in message
news:1129377840.823157.31080@g43g2000cwa.googlegroups.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?