How to create excel form in office 2007

November 24, 2012 @ No comments yet

While forms are mostly used in html documents, to provide interactive static or dynamic content, for an example a set of comment forms on the bottom of this page. On MS excel 2007, you can create excel form which work similar to the html form but with different process and purpose.

Adding excel form to the Quick Access Toolbar

Creating an excel form in windows 2007 to produce a dynamic document is a bit tricky in the first time, you can’t find anything related to excel form every where on the default excel 2007 toolbar. Therefore we need to find it somewhere else on the excel command and put it on the toolbar for later use.

1. Click Customize Quick Access Toolbar, it’s a tiny button on the top left MS excel 2007, and click “More commands”.

Customize quick access toolbar - excel form

2. In the “Choose commands from” selection, choose “All Commands” to view all available commands on MS excel.

all command to show available commands - excel forms

3. From the list, scroll down to find excel forms and add them to Quick Access Toolbar.  Choose only command which only has (Form Control) on the side of the command name. Some of excel forms you can add to toolbar (all with Form Control); Button,  Check Box, Combo Box, Combo Drop-down, Combo List, Label, List Box, Option Button, Scroll Bar, Spin Button and Text Field. Click ok when you done selecting, and the excel form button will appear on the top Quick access toolbar.

selecting commands - excel forms

Designing excel forms layout

Click any of the form button from quick access toolbar, the form will be on ready state, click on the empty cell to draw the form into the sheet. You can re-size the form in edit mode to suit your need, and then click on empty cells to apply the setting. If you wanna go to edit mode again, simply right click the form and the form will be turned into edit mode.

forms layout

Add another excel form to the sheet, move and re-size, copy paste to duplicate and you can also set to row height to match the form and rows to suit your layout design.

Setting various formats and options of excel form

Let’s take a Combo Box as an example. At this moment you will see nothing on it, it’s because we still have not set any values on it. To set values for the form we need to set an input range on the Format Control window. To open format control windows simply right click the form and click on “Format Control“.  But first you need to set values on any cells range which will be set as the combo box values. Now you will see the value list on the combo box.

final form - excel from

One more setting we need to do is setting a “Cell Link“, which will be the output cell of the selected value from Combo Box. On the Format Control, click on Cell Link and put it on any cells that you want to set it as an output. Please note that, the result shown on this cell is only the index of the list not the values shown on the Combo Box lists. On the above image you can see that i use vlookup to get the “January” value using the index and days value within the month cells list, since days and index are the same value.

radio button - excel forms

Last, open the “Format Control” windows to see what you can do with the others form type. The key of excel form is to play with index value, which is the output of the form. You can also set the other options such as size and shading on several type of form. It is wise to put the “Input Range” value on the other sheet (and even hide the sheet) to keep the form layout clean.

M Riza

Article by M Riza

Is a blogger since 2007, founder and editor of Oa Ultimate. Working as a network administrator, computer technician, database and web maintainer | Twitter | G+

Leave a Reply

*

Loading Google+ Comments ...
Loading Facebook Comments ...