Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Print 3-up checks from Excel

    In Sheet1, I have the data related to people to whom checks are to be written. In column F there is an "x" or a blank -- an "x" meaning a check is to be printed.
    On Sheet2, I have 3 check images that match 3-up pre-printed checks (i.e., 3 checks per sheet) that can be fed into a printer.
    I'd like to "batch" this process by printing 3 checks per sheet for only those entries in Sheet1 where the F column has an "x".
    (P.S. people begin in row 3 of Sheet1 with header info in rows 1 and 2)

    Is there an easy way to do this totally in Excel or should I be thinking about a mail merge and create check images in a Word document?

  2. #2
    Silver Lounger
    Join Date
    Mar 2014
    Location
    Forever West
    Posts
    2,078
    Thanks
    0
    Thanked 259 Times in 248 Posts
    Quote Originally Posted by kweaver View Post
    In Sheet1, I have the data related to people to whom checks are to be written. In column F there is an "x" or a blank -- an "x" meaning a check is to be printed.
    On Sheet2, I have 3 check images that match 3-up pre-printed checks (i.e., 3 checks per sheet) that can be fed into a printer.
    I'd like to "batch" this process by printing 3 checks per sheet for only those entries in Sheet1 where the F column has an "x".
    (P.S. people begin in row 3 of Sheet1 with header info in rows 1 and 2)

    Is there an easy way to do this totally in Excel or should I be thinking about a mail merge and create check images in a Word document?
    It's probably not the answer you desire but QuickBooks can print to 3 different styles of check with 3-up being one. The downside would be no check stub to include to the recipient. I am one of our church's 2 treasurers and we use the 1 check 2 stubs type. The QB compatible checks can be had from Intuit or any number of check printers. We use a standard black-only Laserjet printer.

    Have you checked Microsoft Office Web site for a Template for your need?

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Thanks, Berton...but QB is not a possible solution.

    I don't really need a template, I don't think...I believe I need a macro that will examine sheet1 and create only fill-ins on sheet2 where there's an "x" in the F column

    Maybe I need to generate ALL of the checks (sigh) and have a helper column that I can use to filter those that aren't to be printed. Hmm.
    Last edited by kweaver; 2016-11-23 at 09:39.

  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,203
    Thanks
    49
    Thanked 989 Times in 919 Posts
    You can use a macro to print, but I'd use the macro to populate a number of sheets ready to print and have the print process manual - don't want it to go haywire mid print and waste pre-printed stationery.

    Do you need a start? If so, post a sample sheet.

    cheers, Paul

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    KW,

    Sounds like you are describing something very similar to Quicken. Sounds as though you have copied an image of a sheet of 3 checks and placed that image on sheet2 to use as a guide for printing. Data is pulled from sheet one (provided there is an "X" in column F) to populate a field on sheet2 then print sheet2 with the data only (no check image image) onto your check sheets that are loaded into the printer tray.

    Are you using textboxes strategically placed on the sheet aligning with the image that when printed will fall in the correct location on your check sheets?

    If this is so, then quite a clever venture and most doable with a macro. Can you post your columns on sheet 1 and the corresponding textboxes or cells that each goes to? Have you given any consideration as to how to print checks not evenly divisible by 3 to reduce wasting checks or starting with a check sheet that has only two (or one) remaining checks on it?

    Maud

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Maud,

    The user created the "check" sheet with formulas placed cells that populate the sheet, spaced to match the 3-up check sheets.
    But the problem w/this is that there's a check image for every individual (e.g., 100 of them and could grow).
    The user wants to flag specific individuals called "active" so that the check images will only reflect those active individuals.

    I found a workable solution, but it made the workbook large. I added a "helper" column that flagged all of the rows in any given check if an individual was "active" and ran a macro to filter based on that column.

    Seems to work for the moment, but we'll see what the user says about this before I cry for additional help.

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    KW,

    Your concept was quite intriguing so I have been playing with this and came up a couple of features you might be interested in adding to your project. I plan to use this personally instead of shelling out $$ for the new version of Quicken so there are a whole lot of features I will be adding.

    HTH,
    Maud

    The Ledger Sheet has alternate rows of shading using conditional formatting. Shading maintained when inserting or deleting rows.

    Checks0.png

    Selecting a cell in the date column on the Ledger sheet can open the Date Picker or as I have coded it, the user can enter "t" for the current date, "t-num" for num days prior to current date, or "t+num" for num days in the future. The num can be any integer.

    Checks1.png

    Clicking on a CHK# field auto inserts next check number or allows a custom check number entry.

    Checks2.png

    A dynamic named range automatically updates the list of payers from a data validation dropbox

    =OFFSET(Settings!$C$2,0,0,COUNTA(Settings!$C$2:$C$ 1000),1)
    Checks3.png

    The Excel Data Form allows input of a payer, demographics, and account information for automatic retrieval when the payer is selected. Click the Add/Edit Payers button.

    Checks4.png

    Numbers to Words function added to the amount field when viewing or printing checks. (see next image)

    Double clicking a record opens a form view of the check. Editing can be done on the form view or directly in the ledger. Checks will include the address to show through envelope window. No need to store images of checks as they will display on the fly. A print button on the form provides a screenshot hardcopy if desired

    Checks5.png

    Check marks toggle in the print column to indicate the checks that will be printed.

    Checks6.png

    Check Sheet gives a display of the checks to be printed. A scanned image of a sheet of checks is the background image of the worksheet and property set not to print. The input fields that receive the check information must be placed directly over the fields so that when printing on the actual sheet of checks, everything is aligned.

    Checks7.png
    Attached Files Attached Files

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    WOW. I'll have to dig into this. I suspect it's GREAT, per your usual development.

    So far, my helper column (hidden) controls the macro and works fine. This might be a better (certainly different) approach.

    Kevin

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    FYI: if you delete a payer on the Ledger sheet (c column) if you don't want someone there, the macro breaks.

  10. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Good pick up. In my notes to fix.

    Maud

    ....Fixed
    Last edited by Maudibe; 2016-11-29 at 06:39.

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Maud, while we're on the same (sort of) topic, I have a column that I'm filtering. It does NOT have a header row.
    So, the 1st row, even if there's no "x" still appears after the filter. Is there a way to not have that happen without resorting to a header row?

    Code:
        Worksheets("Check Writes").Activate
        Columns("K:M").Select
        Selection.EntireColumn.Hidden = False
        ActiveSheet.Range("$L$1:$L$2625").AutoFilter Field:=1, Criteria1:="x"
        Selection.EntireColumn.Hidden = False
        Columns("L:L").Select
        Selection.EntireColumn.Hidden = True
        Range("A1").Select

  12. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    KW,

    You don't have to unhide the column to filter on it. The following code will filter on x's and hide the first row if blank. Filter arrows are hidden.


    Code:
    Public Sub FilterX()
        Worksheets("Check Writes").Activate
        ActiveSheet.Range("$L$1:$L$2625").AutoFilter Field:=1, Visibledropdown:=False, Criteria1:="x"
        If Cells(1, "L") = "" Then ActiveSheet.Rows(1).Hidden = True
        Range("A1").Select
    End Sub
    
    Public Sub Reset()
    ActiveSheet.Rows(1).Hidden = False
    ActiveSheet.Rows(1).AutoFilter
    Range("A1").Select
    End Sub
    HTH,
    Maud
    Last edited by Maudibe; 2016-11-30 at 20:12.

  13. The Following User Says Thank You to Maudibe For This Useful Post:

    zeddy (2016-12-03)

  14. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Maud

    Just letting you know that I love your cheques layout.
    It reminded me of a number-speller method for converting numeric values to 'dollars and cents' that was raised some while ago.
    Someone wanted something like 16.1 to be "Sixteen Dollars and 10/100"
    So, just in case anyone wants that format, the attached file has a

    Function writeDollars(cell)

    There are some examples in the file.

    zeddy
    Attached Files Attached Files

  15. The Following User Says Thank You to zeddy For This Useful Post:

    Maudibe (2016-12-03)

  16. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    That works very nicely Zeddy!

    Challenge question: Using your code, which US currency from $1 to $1,000,000 is the longest spelled out (number of characters) but lowest value (negating the fractional cents)?

    Last edited by Maudibe; 2016-12-03 at 14:34.

  17. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Maud

    ..let' see - by my calculation there are 81 numbers that would have the longest letter count, but I think the answer is the same as my bar bill last time I was in New York i.e. $373,373

    zeddy
    (just kidding - even in the gastronimical centre of the universe it doesn't cost that much for a round of drinks)

Page 1 of 2 12 LastLast

Tags for this Thread

Posting Permissions

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