Excel – Create a form as a worksheet in Excel that adds records to another worksheet

microsoft excelmicrosoft-excel-2011

I am trying to create a form in Excel for vehicle requests. What I want to happen is to create one worksheet that is a recreation of the current paper form, but have a clickable button that will enter all the data for the vehicle request as a record on a second worksheet. Conversely, if you know the record number, I want the form to auto-flll the information from that record number. I have the second part figured out, that should be a simple HLOOKUP, but I don't know how to generate the clickable button. Will I need to use scripting for this, or is there an pre-built way of doing this? (As an aside, I know there are other and better ways of accomplishing database work, but the nature of my work makes Excel the best choice)

Best Answer

To do it exactly how you want to do it, you would need to use VBA. If you want to go down a simpler path and use an inbuilt feature of Excel, consider using and Excel form.

Excel can create a fill in form for a table of data. In Excel 2007 and 2010 (and presumably 2011 - I don't have a mac to check) the feature doesn't appear in the ribbon menu by default so you have to customise it. I found some information on this over at http://www.addictivetips.com/microsoft-office/excel-2010-data-input-forms/

In Excel 2003, you can simply select your data table, go to the Tools menu and select "Form".

You could then create a command button to run a small macro to load this form each time it is required for data entry.

Related Question