PDA

View Full Version : Solved: Simpliest Way to Reconcile Checkbook



Opv
01-26-2012, 12:49 PM
I have a checkbook that I've maintained in Excel for a while now and I have it working pretty much the way I want it to work. The way I currently reconcile transactions is to filter so that only unreconciled transactions are visible and then I go down the transactions and DOUBLE CLICK the cell in the appropriate column for each transaction to toggle between R and "". Of course, that process is done using a Private Sub Worksheet_BeforeDoubleClick function.

I've been pondering whether there is an easier way to accomplish this. I recall when I use to use Quicken, I could just go down the list of unreconciled transactions and press the ENTER key to show them as reconciled, or the ARROW KEY to skip over a transaction. I like that but I'm not sure the ENTER key can be captured in a keypress event. I haven't been able to find a way to do it at any rate. Can anyone think of an way to achieve the desired effect that is easier than Worksheet_BeforeDoubleClick?

Bob Phillips
01-26-2012, 05:16 PM
The Worksheet_SelectionChange event will invoke you code just by being selected. As a toggle, it is simple(for the user) to reverse it.

Opv
01-26-2012, 05:29 PM
The Worksheet_SelectionChange event will invoke you code just by being selected. As a toggle, it is simple(for the user) to reverse it.

I thought of that. Wouldn't Worksheet_SelectionChange effect a change every time I move the cursor within the target cells? Wouldn't that potentially change cells that shouldn't be changed?

Bob Phillips
01-27-2012, 09:35 AM
NO, because you test which cells are selected, and if they are not within your cells of interest, just do nothing.

Opv
01-27-2012, 10:25 AM
NO, because you test which cells are selected, and if they are not within your cells of interest, just do nothing.

I just ran a test and you are correct; I can use the mouse to click one cell at a time within the defined range. And, that may end up being what I have to do.

I was originally thinking about a possible solution that would allow me to use the keyboard to navigate down the list of cells within the defined range. For example, using the ENTER key to change the cell and an ARROW KEY to bypass the cells that shouldn't be changed. If, for example, Range("A1:A30") is the hypothetical range for the registry entries to be reconciled and only every other row contains a transaction that should be marked as "R" reconciled, I can't think of a way to use the keyboard to reconcile the appropriate transactions without having to navigate around each cell that should not be changed. If something like this is not possible, then all I know to do is use the mouse.

Opv
01-27-2012, 10:34 AM
On second thought, the Application.OnKey option appears promising.

Bob Phillips
01-27-2012, 10:36 AM
There are many ways to do it, such as add a a flag column, and enter R for rows to be changed, and say X as the trigger to do the change. You could then use Crl-click to select all cells in the fall column, and Ctrl-Enter to feed the value R into all of them.

Opv
01-27-2012, 11:05 AM
There are many ways to do it, such as add a a flag column, and enter R for rows to be changed, and say X as the trigger to do the change. You could then use Crl-click to select all cells in the fall column, and Ctrl-Enter to feed the value R into all of them.

Thanks for all of your suggestions. Your suggestion concerning Worksheet_SelectionChange combined with Application.OnKey appears to be achieving the desired result. I appreciate your help.

Opv
01-27-2012, 11:16 AM
Thanks again. I've solved my problem using XLD's suggestion concerning Worksheet_SelectionChange with Application.Onkey.