They can have macros attached to them and don’t require any VBA coding to make them work. Form Controls provide a way for non-technical users to add powerful features to their spreadsheets. Using ActiveX Controlsįorm controls have been part of Excel since version 5. So if somebody selects Gold, because Gold is the 2nd item in the drop down list, the value stored in B3 is 2. This value represents the item selected by the user. Note that what is stored in B3 is a value – 1,2 or 3. By selecting this cell, the value that the user selects is obscured by the drop-down itself, however, formulas in other cells can still reference B3. In the above screenshot I have selected B3. The cell link is a single cell (in the current sheet or another sheet in this workbook) that stores the value that the user has selected. In the above screenshot I have selected F1:F3 The Input Range is a range of cells (from the current sheet or another sheet in this workbook) that store the values that you want to appear on the drop-down list. To define the values for the drop-down list, right click on the drop down list and select Format Control to display the following dialog box: In the screenshot below, the drop down appears to be located inside a cell, however the drop down is an object that “floats” on an imaginary layer above the spreadsheet, therefore it is not embedded inside a cell but can “straddle” across cell boundaries if desired. Place your mouse where you want the top left hand corner of the drop down to be, hold the left mouse button down and drag down and across to define the size and location of the drop down. The mouse pointer becomes a little black cross. Put a tick in Show Developer tab in the Ribbon (3rd checkbox down)Ĭlick the Combo Box tool in the Form Controls section (the second icon on the top row of the Form Controls palette) To display it:Ĭlick the Office Button (big round button at the top left)Ĭlick the Excel Options button at the bottom of the dialog box By default the Developer tab is not displayed. In addition to using Data validation, there’s a couple of other ways to create a drop down list in Excel.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |