+ Reply to Thread
Results 1 to 4 of 4

Check boxes hide rows only during printing

Hybrid View

  1. #1
    Registered User
    Join Date
    10-23-2012
    Location
    LA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Check boxes hide rows only during printing

    I have a spreadsheet that is used find out what items a client wants to order.
    What I want is a column of check boxes, if a check box is unchecked, it's row is hidden in the print preview.
    If the item is checked, then it's row shows up to print. I only want the unchecked rows to be hidden for the print though.
    I need a macro that can do this, every time I try to modify and piece together commands to try and accomplish this there
    is a fault with my coding.
    Has anyone done this before?
    Thanks

  2. #2
    Forum Contributor
    Join Date
    10-18-2012
    Location
    Telford, England
    MS-Off Ver
    Excel 2010 (2003)
    Posts
    294

    Re: Check boxes hide rows only during printing

    Sounds tricky but here is an approach;
    Associate each check box with a cell on the same line. The cell need not be within the print area so it's not printed (or even seen!).
    Before printing apply a filter to select only the checked rows, remove the filter afterwards. You'd have to probide a macro to do these steps and have the user not use the usual print shortcuts.

  3. #3
    Registered User
    Join Date
    10-23-2012
    Location
    LA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Check boxes hide rows only during printing

    That sounds like what I want, unfortunately I keep trying to formulate a code to accomplish this, and there is fault with what I write. Is it really that hard to do? It seems like a million people have had to do this and I have a deadline at work. So can anyone provide a code similar to what bryanbaker discribed, or just some macro that sort of works, with with some tips on how to adjust it to suit my needs?

  4. #4
    Forum Contributor
    Join Date
    10-18-2012
    Location
    Telford, England
    MS-Off Ver
    Excel 2010 (2003)
    Posts
    294

    Re: Check boxes hide rows only during printing

    Ok, here goes. You have to define a (large?) number of checkboxes, and align them carefully within the row that they apply to.
    Each is associated with a cell to hold the current value. For simplicty, I will use column B but it could be well off screen in a high column outside the print area. Now you don't absolutely need a macro when running, but users are simple creatures so we write code in the before_print event to handle it for them.
    Private Sub Workbook_BeforePrint(Cancel As Boolean)
        
        Columns("B:B").Select
        If Not AutoFilterMode Then Cells.AutoFilter
        Selection.AutoFilter Field:=1, Criteria1:="TRUE"
        
        ActiveWindow.SelectedSheets.PrintOut
    
        Selection.AutoFilter Field:=1, Criteria1:="TRUE"
        Cancel = True           ' we printed: stop excel doing so
        
    End Sub
    I guess you've lots of lines needing a checkbox. They are easy to create but hard to position and associate. If only we could write a loop, but that's object model again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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