+ Reply to Thread
Results 1 to 7 of 7

Before Print and UnDo

  1. #1
    Soniya
    Guest

    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.

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


    TIA
    Soniya


  2. #2
    Bob Phillips
    Guest

    Re: Before Print and UnDo

    See reply in excel.misc


    --

    HTH

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


    "Soniya" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    >




  3. #3
    Soniya
    Guest

    Re: Before Print and UnDo

    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



  4. #4
    Bob Phillips
    Guest

    Re: Before Print and UnDo


    "Soniya" <[email protected]> wrote in message
    news:[email protected]...

    > 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




  5. #5
    Soniya
    Guest

    Re: Before Print and UnDo

    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


  6. #6
    Bob Phillips
    Guest

    Re: Before Print and UnDo


    "Soniya" <[email protected]> wrote in message
    news:[email protected]...
    > 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?



  7. #7
    Soniya
    Guest

    Re: Before Print and UnDo

    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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1