Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Before Print and UnDo
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 |
#2
|
|||
|
|||
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) wrote in message ups.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. TIA Soniya |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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. wrote: 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 |
#5
|
|||
|
|||
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. In article .com, wrote: 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? |
#6
|
|||
|
|||
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 wrote: 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. In article .com, wrote: 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? -- Dave Peterson |
#7
|
|||
|
|||
Yeah, I normally don't. I usually use modified hungarian, even for
constants. In article , Dave Peterson wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|