I heard there is a way to make a sort've database in Excel with a user interface and a separate spreadsheet to capture the data. Does anyone know how to do this?

I know Excel is not intended to be a database but it's the best option I have that will possibly ensure some sort've data integrity and control over data entry. I will be importing all the data into a database for the reports, I just need a way to capture it and Excel is the only program that all the users have.

Any assistance would be appreciated.

Hello,

See the following:

http://support.microsoft.com/kb/257819
http://support.microsoft.com/kb/295646
http://support.microsoft.com/kb/278973

If you're going to do this, you need to be very disciplined in how you structure your Excel files. They need to look like Access tables, really.

And do not open a Connection to an Open Workbook, this generates a Memory Leak.

Thanks. This is kind've what I need but really wasn't looking at it being that complex.

I heard there is a way to make a user interface with combo boxes, list boxes, text boxes etc in Excel and that's what I need to do. I can see where those articles will come in hand though for the actual back-end. Thanks.

Perhaps you mean a userform?

If you do just goto the VBE (Alt+F11) and Insert>Userform.

A looong time ago we've had a discussion with Carole on how to simulate a form via a worksheet...

Perhaps it helps?

Old Discussion

(scroll a bit down the thread)

Martin