Print 3-up checks from Excel

Topic

New Reply

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?

Reply | Quote

Replies

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?

Before you wonder "Am I doing things right," ask "Am I doing the right things?"

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

46087-Checks0

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.

46080-Checks1

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

46081-Checks2

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)
46082-Checks3

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.

46083-Checks4

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

46084-Checks5

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

46085-Checks6

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.

46086-Checks7

Reply | Quote

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

Reply | Quote

FYI: if you delete a payer on the Ledger sheet (c column) if you don’t want someone there, the macro breaks.

Reply | Quote

Good pick up. In my notes to fix.

Maud

….Fixed

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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)?

Three Hundred and Seventy-Three Thousand, Three Hundred and Seventy-Three Dollars and 00/100 (92 characters)

Reply | Quote

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)

Reply | Quote

This is really great, Zeddy, as the general one I’ve seen most use comes from Microsoft and (incorrectly, at least in the US) doesn’t use the hyphen which you did (25 is twenty-five).

Reply | Quote

Hi KW

..a compliment indeed to say mine is better than Microsoft’s.
..well, you didn’t technically say that, but, as it’s my birthday week, I can hear whatever I like whenever I read anything.

zeddy

Reply | Quote

Happy Birthday, Sagittarius. Mine is 29th of this month – Capricorn.

And, yes, yours is better than Microsoft’s.

Reply | Quote

You know the star signs are out by about a month.

/hijack

cheers, Paul

Reply | Quote

Thanks Paul!

great link!
..I’ve gone from being a saggy-whotsit to a, to a..
Ophiuchus !!!!

..I’m taking antibiotics for that

zeddy

Reply | Quote