Printing Cheque of Different sizes from the same Worksheet

External Since: Nov 28, 2008 Posts: 1

Hi All,

Kindly mention yours email ID for the sample file.
>>>>>>>>>>>>>> Present Scenario >>>>>>>>>>>>>>>>>>>>

I have bank account in 4 banks and at present I am printing my cheques
through Excel on a laser printer.

Cheque belonging to each bank are of different size (length and
width). On each cheque there are some basic fields (like "Name of
Payee, Amount in Figures, Amount in Words, Date, Mode of Payment"
etc, which are to be filled by Cheque issuer. Presently the issuer
fills these fields in Data Source sheets (HDFC3772 and SBOP5620) and
data is automatically VLOOKUPed in "Cheque" sheet).

At present I am maintaining a "Cheque" sheet "separately" for each
bank, where fields have been placed in such a manner (at specified
distance and of specified width) that when printed, the value of each
fields comes out properly on a blank cheque. Field Values changes
according to 'Cheque No' selected from Cell E1.

Required Data is taken from Data Base sheets maintained for each bank
(i.e., HDFC3772 and SBOP5620)

>>>>>>>>>>>>> Now here is What I wish >>>>>>>>>>>>>>>

--Sheet named "CPC" (Cheque Printing Configuration) contains, for each
bank's cheque, the physical measurements i.e., Size of the Cheque, the
distance of each field from top edge and left edge of the cheque, and
width of each field. The measurements are to be taken physically by
the user at first use. The defined name for the relevant range is
"CPCrng".

--Instead of separate "Cheque" sheet for each Bank, there is only one
"Cheque" sheet with the above fields on it (or may be Single user form
with Text boxes containing the required fields on it)

--G1 on "Cheque" sheet contains Name of the Bank for which Cheques are
to be printed.

--Whenever we select the Bank Name from cell G1, the sheet / user form
and fields / text boxes thereon are automatically resized /
redistanced as per the dimensions provided for that Bank in sheet CPC.
Off course the Field values will then be based on Cheque No in Cell
E1, as at present.

>>>>>>>>>>>>>> Notes >>>>>>>>>>>>>>>>>>>
--The sample sheet contains the Data Source Sheet for 2 banks only.
--Source Data for each bank surely will continue to reside in separate
sheets.
--Name of Data Source worksheets and Heading of "CPCrng" are exactly
the same.
--Cheque List in cell E1 has been configured to adjust for data source
as per Bank Name selected in G1.
--If the proposed solution needs "user form and text boxes", then
kindly tell how the formulae will be used in those text boxes, as
presently I don't know how to do that.
--A sample file alogwith the desired format will be provided on the
email ID Provided.

Regards
CA Kanwaljit Singh Dhunna

External Since: Nov 13, 2008 Posts: 17

Hi,

One way is to write VBA which picks up the info and resized the check
accordingly. For example suppose you have a range call Bank1 which contains
all the setting for column widths and so on for that banks checks then you
could use code to capture each item and then apply it to the appropriate
place in code. You could record the macro for changing all the items and
then replace the hardcoded entries with those picked up form the Bank1 range.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

" " wrote:

> Hi All,
>
> Kindly mention yours email ID for the sample file.
> >>>>>>>>>>>>>> Present Scenario >>>>>>>>>>>>>>>>>>>>
>
> I have bank account in 4 banks and at present I am printing my cheques
> through Excel on a laser printer.
>
> Cheque belonging to each bank are of different size (length and
> width). On each cheque there are some basic fields (like "Name of
> Payee, Amount in Figures, Amount in Words, Date, Mode of Payment"
> etc, which are to be filled by Cheque issuer. Presently the issuer
> fills these fields in Data Source sheets (HDFC3772 and SBOP5620) and
> data is automatically VLOOKUPed in "Cheque" sheet).
>
> At present I am maintaining a "Cheque" sheet "separately" for each
> bank, where fields have been placed in such a manner (at specified
> distance and of specified width) that when printed, the value of each
> fields comes out properly on a blank cheque. Field Values changes
> according to 'Cheque No' selected from Cell E1.
>
> Required Data is taken from Data Base sheets maintained for each bank
> (i.e., HDFC3772 and SBOP5620)
>
> >>>>>>>>>>>>> Now here is What I wish >>>>>>>>>>>>>>>
>
> --Sheet named "CPC" (Cheque Printing Configuration) contains, for each
> bank's cheque, the physical measurements i.e., Size of the Cheque, the
> distance of each field from top edge and left edge of the cheque, and
> width of each field. The measurements are to be taken physically by
> the user at first use. The defined name for the relevant range is
> "CPCrng".
>
> --Instead of separate "Cheque" sheet for each Bank, there is only one
> "Cheque" sheet with the above fields on it (or may be Single user form
> with Text boxes containing the required fields on it)
>
> --G1 on "Cheque" sheet contains Name of the Bank for which Cheques are
> to be printed.
>
> --Whenever we select the Bank Name from cell G1, the sheet / user form
> and fields / text boxes thereon are automatically resized /
> redistanced as per the dimensions provided for that Bank in sheet CPC.
> Off course the Field values will then be based on Cheque No in Cell
> E1, as at present.
>
> >>>>>>>>>>>>>> Notes >>>>>>>>>>>>>>>>>>>
> --The sample sheet contains the Data Source Sheet for 2 banks only.
> --Source Data for each bank surely will continue to reside in separate
> sheets.
> --Name of Data Source worksheets and Heading of "CPCrng" are exactly
> the same.
> --Cheque List in cell E1 has been configured to adjust for data source
> as per Bank Name selected in G1.
> --If the proposed solution needs "user form and text boxes", then
> kindly tell how the formulae will be used in those text boxes, as
> presently I don't know how to do that.
> --A sample file alogwith the desired format will be provided on the
> email ID Provided.
>
> Regards
> CA Kanwaljit Singh Dhunna
>