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

Posted on

QUESTION :

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)

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.

Leave a Reply

Your email address will not be published.