2007
XLVB App#1 - Automated
ExcelVB Driven Cash Book(Quick-Start User Intro)
1.
MIMIMUM SYSTEM REQUIREMENTS
Click the "About" button
on the main menu form(see screenshot below) for
this program (or click
here) to read details of the PC system requirements
you need to have as a MINIMUM, if this program is
to perform optimally when you use it.
If your PC does not meet the MINIMUM
specified requirements for use of this application,
you are likely to experience major frustrations
using it. We highly recommend if that is the case
that you arrange to have your system upgraded accordingly
in order to afford yourself a positive user experience.
2.
POST WORKBOOK OPENING
When the work book opens, a floating
MENU form(see above) appears with a variety of buttons,
drop menus and other controls. Anywhere you are
in the program, you can always return to this floating
form by pressing the ENTER key on your keyboard,
OR by clicking the Load Menu button wherever it
appears on a worksheet.
3.
GENERAL BUTTON CONTROLS
a.
Load Menu: Clicking the "Load
Menu" button will cause a "Back to Main
Menu" prompt to appear. Click YES on that prompt
will load the menu form.
b.
Print Preview or Preview: Clicking
this button will cause the current worksheet's selected
table or report to be shown in Excel's Print Preview
window. From here you can use the various controls
provided to make any adjustments you want prior
to sending the previewed range to printer.
c.
Reload Form: Click this button anytime
you want to START OVER. For instance maybe you made
a mistake of clicking the wrong button thereby causing
controls you actually wanted to use to be disabled.
Clicking "Reload Form" will return the
form’s controls to their default settings
so you can try again.
d.
Help: Clicking the "Help"
button or Pressing F1 will cause this browser based
"USER QUICK-START INTRO"(that you're currently
viewing) to appear.
e.
Save: Clicking this button brings
up a prompt asking you to confirm you want to save.
Clicking YES and OK at the next prompt that appears
will cause the program to save the workbook. Avoid
clicking your mouse again until the "Please
Wait...” message that appears in the status
bar changes to "Ready".
f.
About: Clicking this button loads
a form which provides details about the developer
of this application, and system requirements for
running it optimally. Please check and ensure that
the computer on which this program is being used
meets the MINIMUM requirements specified - else
you may experience problems using it.
g.
View Quick-Start Intro: Clicking
this button loads a text-only/form based version
of this tutorial you are currently viewing.
4.
USING WORKSHEET DATA AUTO-FILTER DROP MENUS
Click the "Filter ON"
button on ANY worksheet it appears and you will
be able to click the filter drop menus on table
fields/columns to query the spreadsheet database
in the current worksheet.
Note that the "TOTAL"
range (blue background with yellow font) will change
to reflect whatever drop menu selection you make.
This feature is useful for quickly getting a summary
of receipts or payment for a "Particular"
and/or "Type" and/or "Date"
and/or "Month" etc.
5.
PRINTING SELECTED COLUMNS
In the Receipts and Payments
tables for January to December, and any of the other
worksheets, you can select one or more columns you
do NOT wish to print and press "Ctrl+H"
to hide them after which you can click "Preview"
and print the resulting print range. Once finished
however, YOU MUST REMEMBER to select a column BEFORE
to one AFTER the range you hid, and press "Ctrl+U"
to UNHIDE the columns.
6.
CHOOSING A WORKSHEET DESTINATION
Click the drop down menu (on
the floating menu form) on the top left directly
below the phrase "Choose Sheet Destination
:" to select from January to December, the
month you wish to post entries for.
The yellow box to the left of
the drop menu will display the name of the worksheet
location you choose. Make sure you ALWAYS check/confirm
that you are in the correct worksheet BEFORE proceeding
to make entries.
Note:
The frames labelled 1,2,3 etc are
enabled or disabled depending on your chosen worksheet
destination. For instance when you choose any of
the 12 months for Receipts/Payments, the frame labelled
"1. Use The Controls Below To Post Receipts/Payments
Data" becomes enabled.
However if you choose "Reports"
or "Bank Reconciliation" from the drop
menu, the frame labelled "1. Use The Controls
Below To Post Receipts/Payments Data" becomes
DISABLED. Instead, the frame labelled "3.
Prepare/View Reports(HMOs Clearline etc)"
is enabled.
This is a precautionary measure
to prevent users from using controls that are not
needed in a particular worksheet.
7.
POSTING PAYMENTS/RECEIPTS
a. Click the
button labelled "Go To Payments Table"
and WAIT (do NOT click your mouse again) until the
program completes preparation of the entry table.
You will see the status bar initially display "Please
Wait.." and on completion display "Ready".
Only AFTER you see "Ready" in the status
bar should you mover to step b.
Note:
If you get a message like "The
destination you selected does not have a payments
table in it", that would mean you have not
used the drop menu at the top left of the form to
choose one of the 12 months' worksheets.
b. Next click
the yellow tinted button labelled "New CashBook
Entry". Doing this will ENABLE the button blue
tinted button labelled "Load Calendar Display".
c. You will click
the "Load Calendar Display" button
to load a clickable calendar which presents a friendly
graphic interface with which you can click to insert
your preferred date in the worksheet. Note that
you do not need to worry about placing your cursor
before posting data as the program has been designed
to do this for you.
When done, simply click the red
X(close button) at the top right of the calendar
form, to close it and return to the menu form interface.
d. Next, depending
on whether you are posting receipts or payment data,
the drop menus and text boxes following the calendar
button will be enabled as appropriate. Use them
to choose the text you wish to enter into the table.
IMPORTANT
NOTE: It is recommended for ease
of use that ALL text you wish to post into the program
be entered into the appropriate "settings"
worksheet column so that they can be accessed using
the form's drop menus. This is to minimize chances
of typographical errors which could affect the programs
ability to correctly compute some of its formulas.
e. Click the
"Choose Classification" drop
menu and select the name of the specific entry you
wish to post. The program will automatically select
the corresponding cell in the appropriate column
for that classification.
f. Type the Receipt or Payment
amount into the text field labelled "Enter
Amount To Post". Again the program will
insert whatever you type into the appropriate cell.
Note:
The width of each cell your data is posted into,
is always automatically adjusted to ensure the posted
data is visible.
8.
GENERATING/VIEWING MONTHLY SUMMARIES
Click the View Monthly Payments
or Receipts button to go to the report
generation interface that will allow you choose
a month name from a drop menu, so that the program
automatically generates the summation of data posted
for that period under the different classifications/headings.
At the bottom of the resulting
summaries table is also(for the Bank Cash Book)
a Bank Reconciliation Report for the month you chose.
9.
POSTING SUMMARIES FOR YEAR'S REPORT
Start by selecting "YearSummary"
from the drop menu at the top of the main/floating
menu form. The program will take you to the "YearSummary"
worksheet.
a. Click the
button labelled "2. Prepare Year's Cumulative
Summary Using Monthly Summaries " to enable
the blue tinted "Load Calendar Display"
button below/to the left of it. Clicking the latter
button will load a calendar/date entry form.
b. Click the
date you wish to post and click the close button.
c. Doing the
above will enabled the drop menu to the right of
the calendar button. Click the drop menu and select
the text entry you wish to make. Note that this
drop menu normally contains items based on those
entered into the "Particulars" column
for the Receipts or Payments tables.
The item you click gets auto-inserted
into the appropriate cell in the "Details"
column of the "YearSummary" worksheet,
and the formulas under the Debit(Dr) and Credit(Cr)
columns will automatically compute the SUM of all
occurrences of that particular item using the MONTH
derived from the date entered.
d. In certain
cases, you may find an individual or "particular"
will have summations appearing under the DR and
CR columns: this could be due to a "Reversal
Entry" posted in the affected month for that
"particular".
e. The above
is an example of the type of NOTE you may want to
post into the "Type Notes Into Box" field
that appears under the drop menu used to choose
"particulars".
10.
PREPARE/VIEW REPORTS(HMOs, CLEARLINE etc)
This activity is carried out using
the controls in the frame labelled "3.
Prepare/View Reports(HMOs Clearline etc)".
a. Start by selecting
"Reports" from the drop menu at the top
of the main/floating menu form. The program will
take you to the "Reports" worksheet.
b. Note that
either the "Receipts" or "Payments"
buttons may become disabled as soon as the "Reports"
worksheet becomes active. This will happen as a
result of a pre-existing setting arising from previous
usage.
c. You will click
either the "Receipts" or "Payments"
button depending on the type of report you wish
to generate.
Note: This interface
allows you to post items in a drop menu drawn from
entries made into the "Type" column of
the receipts or payment table so that summary totals
are generated for EACH item from January to December
in a ready-to-print table layout.
d. Doing the
above will cause the drop menu to the right to be
enabled. You will select a "Type" item
from the drop menu, which the program will insert
into the appropriate cell in the report table, causing
summations for each month to be done along the same
row, while a columns total row is inserted below.
e. Continue choosing
items from the drop menu, until you are done. Click
"Preview" to see what the table
looks like for printing.
f. Clicking "Erase
All" allows you to erase the entire table's
contents with a few clicks. You will want to do
this when the need arises to prepare another report.
11.
RECORD UNPRESENCTED/UNCREDITED CHEQUES & PRODUCE
BANK RECONCILIATION REPORT
This activity is carried out using
the controls in the frame labelled "4.
Record Unpresented/Uncredited Cheques".
This frame is disabled by default. You will need
to click "Cheques" in the drop
menu(located under the label "Choose Sheet
Destination" on the top left of the main
menu form) to enable it.
a. Start by choosing
"Cheques" from the "Choose
Sheet Destination" drop menu. You will
be taken to the worksheet in which the table for
recording ALL "Unpresented/Uncredited"
cheques is located.
b. Next click
the yellow tinted "Make New Cheque Entry"
button to enable the "Load Calendar Display"
button on its right.
c. Click the
calendar button and enter the appropriate date using
the calendar form that appears.
d. Next, use
the Particulars, Type, PCV, Teller and Cheque No
buttons and text boxes to post appropriate entries
into the cheques table. The program will move the
cursor as needed.
e. Type the actual
amount/value on the cheque into the "Post
Unpresented Cheque" or "Post Uncredited
Cheque" entry field as appropriate.
f. Clicking "Erase
All" allows you to erase the entire table's
contents with a few clicks.
g. Click the
yellow tinted "View/Print Bank Reconciliation
Report" button to go to the worksheet
showing twelve(12) individual monthly reconciliation
summary tables for January to December on a ready
to print table.
At the bottom is a single summary
table showing ALL the reconciliation data monthly/for
the year. You may want to print this page for reference
purposes.
IMPORTANT
NOTE: TO ENTER OPENING BALANCE FOR
JANUARY, you will need to type the AMOUNT into the
cell containing blue colored text. All other data
are automatically updated from the Receipts/Payments
tables.
12.
FIND SPECIFIC RECORDS IN RECEIPTS/PAYMENTS TABLES
This activity is carried out using
the controls in the frame labelled "Use
The Controls Below To Quickly Find A Record(s)".
This frame is disabled by default, and will only
be enabled when you choose any of the 12 months'
worksheets to post receipts/payments.
a. While working
on a receipts/payments table, select an item from
the "Particulars" and/or "Type"
drop menu, and the program will automatically filter
the table's displayed data to show ONLY those corresponding
to the item you selected. This will come in handy
when you need to quickly narrow down to specific
data entries and get summaries/printable range.
b. Clicking the
"Find By Cheque/Teller No" radio
buttons to bring up an input box prompt asking you
to enter the corresponding text you want to use
for your query. When you do so, the program uses
it to filter the table data. Note that the radio
buttons mentioned are enabled or disabled depending
on whether you are working on the Receipts or Payments
table.
d. Clicking the
"Show ALL Records/Reset Filter"
radio button will return all the filtered/hidden
data rows in the table into view. You would use
this to reset any changes you made while trying
to find data using the other controls provided.
13.
THE SETTINGS WORKSHEET
a. Update the
various drop menu items, and table headings by typing
into the appropriate columns in the "Settings"
worksheet.
b. You will also
change the name of the CashBook by typing into the
yellow colored cell in the column labelled "BANK".
c. Study the
items listed in the different columns and compare
with the various data tables to understand how they
relate. The workbook has been designed to minimise
repeat entry making.
d. In order to
change or update any menu items or headings in ALL
tables throughout the workbook, simply type them
into the appropriate cell/columns in the Settings
worksheet.
e. You can change
the YEAR by typing into the yellow colored cell
in the column labelled "YEAR".
f. Click the
Sort 1 and Sort 2 buttons to automatically
data sort certain lists in the Settings worksheet.
*************************
