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

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)

Solution

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.

Comments

[Bohdan Ivanchenko]

Access might be better for this

[Dave]

How much VBa do you know? I am certain you will need scripting as this is a very bespoke request.

[eightbitdino]

I don't know VBA, but I'm sure I could figure it out. Before I started scripting, I wanted to make sure there wasn't a more obvious solution. And yes, Access (or really any database) would be better suited to this, but everybody at my work has Excel and if they needed to could continue to manually manipulate a spreadsheet. We're talking a place that is still pretty low tech and lives off the three big Microsoft Office products.

[Nick Perkins]

@HoldenFenner I'm just wondering if my answer was of any use.

[Prasanna]

@NickPerkins Does not look like an answer to me. Looks like guidance