Feasible is a relative term. It is certainly possible to use Excel as the
front end to the database, I've done it myself more than once. It can
actually provide some benefits such as providing the 'flat-file' buffer for
the data: you can pre-validate it in Excel before moving it off into the
database. But the total effort may be time consuming and initially
maintenance/tweaking heavy.

I'm thinking you might want to rethink your approach and take more control
of the workbook you've already developed. At the same time, make it
friendlier to the user. You probably/should have some sheets in the book
that the end users can only enter specific data into and perhaps some other
'standard' sheets or functions that report this data in various fashions.
The end users should not be altering these sheets with their own added
formulas and features. If they've got special reports to prepare those
should probably be on added sheets for those special uses, whether added by
whoever maintains the workbook structure or by the end user him/herself.

You could add command buttons (or text boxes associated with macros) to
provide specific functionality on some sheets and require that all changes to
controlled sheets be performed through user forms that those buttons/boxes
bring into view. You could add comments to cells that are using formulas to
derive information explaining where the info came from and how it the
underlying formula works. That's for pure math type functions, for
VLOOKUPS() and such, simply explain what the data represents as "date
associated with invoice # in column A". Possibly even writing a User's Guide
would provide great benefit.

I've got something that's probably similar that I deal with: a very complex
labor utilization tracking workbook that also prepares a complex invoice
based on labor utilization, purchased materials and travel expenses. The end
user (the accounting department) is actually not permitted to 'touch' a
worksheet except for one column on one sheet where current hours for the
employees is entered. All other information is added to, deleted from or
edited via userforms called up by either buttons/textboxes or validation
lists that they choose from to perform a specific action such as adding a new
employee to the labor sheet or current material/travel expenses to those
sheets and even to preparing the final invoice. Everything else is locked
down and protected from accidental change. When I was first given the task
of managing this workbook, it was completely open and people often overwrote
formulas with hard values, put the wrong information into cells, and
generally simply made it an unmanageable, undependable mess that only got
worse over time. It has now been in use over 3 years without change and is a
reliable tool presenting consistent results - to the point that the invoices
have passed numerous audits by government auditing agencies.

"Kobus" wrote:

I need to automate existing excel spreadsheets that we use for bills
of quantities. The sheets contain items, rates, quantities and totals
with various reports and monthly payment columns. The problem is that
the system becomes elaborate and easily breaks. It is difficult for
other users to work on the sheets because they are not sure how the
links and formulas work.

One solution is to abandon Excel and write a database program but
this
take the Excel feeling away and users will not be able to add their
own special reports or formulas. I thought it should be possible to
write a database program (using VBA) with Excel as the front-end.
This
will enhance reliability but retain flexibility compared to Excel
alone.

Is it feasible to have Excel as a front-end ("form") linked to the
database data (in Access) and to be able to add, delete, edit and
link
the data? All this must happen while the database and business rules
are enforced.