Hello, Your answer lies in the properties of list box which are given below. Add a list box (Form control)
1. If the *Developer* tab is not available, display it. [image: Show]Display the *Developer* tab<http://office.microsoft.com/client/helppreview.aspx?AssetId=HP102366819990&lcid=1033&NS=EXCEL&Version=12&respos=2&CTT=1&queryid=ab78b10c5e264b0cb938f8bff734e9ea> 1. Click the *Microsoft Office Button* [image: Button image], and then click *Excel Options*. 1. In the *Popular* category, under *Top options for working with Excel*, select the *Show Developer tab in the Ribbon* check box, and then click * OK*. * Note * The Ribbon is a component of the Microsoft Office Fluent user interface. 1. On the *Developer* tab, in the *Controls* group, click *Insert*, and then under *Form Controls*, click *List box* [image: Button image]. [image: Controls group] 1. Click the worksheet location where you want the upper-left corner of the list box to appear. 2. On the *Developer* tab, in the *Controls* group, click *Properties* [image: Edit code button]. * Tip * You can also right-click the control, and then click *Format Control*. To specify the control properties, do the following: 1. In the *Input range* box, enter a cell reference to a range that contains the values to display in the list box. 2. In the *Cell link* box, enter a cell reference that contains the list box selection. The linked cell returns the number of the selected item in the list box. The first item in the range returns a value of 1, the second item in the range returns a value of 2, and so on. Use this number in a formula to return the actual item from the input range. For example, a dessert preference form has a list box that is linked to cell C1, the input range for the list is D1:D5, and the items in the range are: "Ice Cream" (D1), "Cake" (D2), "Liqueur" (D3), "Candy" (D4), and "Chocolate" (D5). The following formula, entered in cell B1, returns the value "Liqueur" from range D1:D5 if the value of C1 is 3, based on the current selection in the list box. =INDEX(D1:D5,C1) 1. Under *Selection type*, specify how items can be selected in the list box by doing one of the following: - To create a single-selection list box, click *Single*. - To create a multiple-selection list box, click *Multi*. - To create an extended-selection list box, click *Extend*. * Note * If you set the selection type to *Multi* or *Extend*, the cell that is specified in the *Cell link* box returns a value of 0 and is ignored. The *Multi* and *Extend* selection types require the use of Microsoft Visual Basic for Applications (VBA) code. In these cases, consider using the ActiveX list box control. [image: Top of Page]<http://office.microsoft.com/client/helppreview.aspx?AssetId=HP102366819990&lcid=1033&NS=EXCEL&Version=12&respos=2&CTT=1&queryid=ab78b10c5e264b0cb938f8bff734e9ea#top> Top of Page<http://office.microsoft.com/client/helppreview.aspx?AssetId=HP102366819990&lcid=1033&NS=EXCEL&Version=12&respos=2&CTT=1&queryid=ab78b10c5e264b0cb938f8bff734e9ea#top> Add a list box (ActiveX control) 1. If the *Developer* tab is not available, display it. [image: Show]Display the *Developer* tab<http://office.microsoft.com/client/helppreview.aspx?AssetId=HP102366819990&lcid=1033&NS=EXCEL&Version=12&respos=2&CTT=1&queryid=ab78b10c5e264b0cb938f8bff734e9ea> 1. Click the *Microsoft Office Button* [image: Button image], and then click *Excel Options*. 1. In the *Popular* category, under *Top options for working with Excel*, select the *Show Developer tab in the Ribbon* check box, and then click * OK*. * Note * The Ribbon is a component of the Microsoft Office Fluent user interface. 1. On the *Developer* tab, in the *Controls* group, click *Insert*, and then under *ActiveX Controls*, click *List Box* [image: Button image]. [image: Controls group] 1. Click the worksheet location where you want the upper-left corner of the list box to appear. 2. To edit the ActiveX control, make sure that you are in design mode. On the *Developer* tab, in the *Controls* group, turn on *Design Mode* [image: Button image]. 3. To specify the control properties, on the *Developer* tab, in the * Controls* group, click *Properties* [image: Edit code button]. * Tip * You can also right-click the control, and then click *Properties*. The *Properties* dialog box appears. For detailed information about each property, select the property, and then press F1 to display a Visual Basic Help (Visual Basic Help: To get Help for Visual Basic, point to *Macro *on the *Tools *menu, and then click *Visual Basic Editor*. On the *Help* menu, click *Microsoft Visual Basic Help*.)<http://office.microsoft.com/client/helppreview.aspx?AssetId=HP102366819990&lcid=1033&NS=EXCEL&Version=12&respos=2&CTT=1&queryid=ab78b10c5e264b0cb938f8bff734e9ea>topic. You can also type the property name in the Visual Basic Help *Search* box. The following section summarizes the properties that are available. Summary of properties by functional categories *If you want to specify * *Use this property * *General:* Whether the control is loaded when the workbook is opened. (Ignored for ActiveX controls.) *AutoLoad* (Excel) Whether the control can receive the focus and respond to user-generated events. *Enabled* (Form) Whether the control can be edited. *Locked* (Form) The name of the control. *Name* (Form) The way the control is attached to the cells below it (free floating, move but do not size, or move and size). *Placement* (Excel) Whether the control can be printed. *PrintObject* (Excel) Whether the control is visible or hidden. * Visible* (Form) *Text:* Font attributes (bold, italic, size, strikethrough, underline, and weight). *Bold*, *Italic*, *Size*, * StrikeThrough*, *Underline*, *Weight* (Form) The default run time mode of the Input Method Editor (IME). *IMEMode* (Form) Whether the size of the control adjusts to display full or partial lines of text. *IntegralHeight*(Form) Whether multiple selections of items are permitted. *MultiSelect* (Form) The text in the control. *Text* (Form) How text is aligned in the control (left, center, or right). *TextAlign* (Form) *Data and Binding:* The range that is linked to the control's value. *LinkedCell* (Excel) The content or state of the control. *Value* (Form) *Size and Position:* The height or width in points. *Height*, *Width* (Form) The distance between the control and the left or top edge of the worksheet. *Left*, *Top* (Form) *Formatting:* The background color. *BackColor* (Form) The color of the border. * BorderColor* (Form) The type of border (none or single-line). *BorderStyle*(Form) The foreground color. *ForeColor* (Form) Whether the control has a shadow. * Shadow* (Excel) The visual appearance of the border (flat, raised, sunken, etched, or bump). *SpecialEffect* (Form) *Keyboard and Mouse:* A custom mouse icon. *MouseIcon* (Form) The type of pointer that is displayed when the user positions the mouse over a particular object (for example, standard, arrow, or I-beam). *MousePointer* (Form) *Specific to List Box:* The source of data for multiple columns. *BoundColumn* (Form) The number of columns to display. *ColumnCount* (Form) A single row as a column heading. *ColumnHeads* (Form) The width of each column. *ColumnWidths*(Form) The range that is used to populate the list. *ListFillRange* (Excel) The list style (plain, with option buttons, or with check boxes). *ListStyle* (Form) How the control searches its list while the user types (first letter, complete entry, or none) *MatchEntry* (Form) The column to store in the *Text*property when the user selects a row. *TextColumn* (Form) The item that appears in the topmost position in the list. *TopIndex* (Form) * Notes * - To create a list box with multiple selection or extended-selection enabled, use the *MultiSelect* property. In this case, the *LinkedCell*property returns a *#N/A* value. You must use VBA code to process the multiple selections. - To create a two-column list box with column headers, set *ColumnCount*to *2*, *ColumnHeads* to *True*, *ColumnWidths* to the width that you want for each column (for example, *72pt**;72pt*), *ListFillRange* to the range that is used to populate the list (for example, B2:C6), * BoundColumn* to either *1* or *2* to indicate which column value to save, and *LinkedCell* to a cell address that contains the selected value. By default, the column label is used as the column header (for example, Column B and Column C). To use your own column headers, place them immediately above the first value specified in *ListFillRange* (for example, B1 and C1) before you close the *Properties* dialog box. Finally, resize the list box to display both columns. - To create a list box that displays one value in the list box but saves another value in the linked cell, create a two-column list box, and then hide one of the columns by setting its *ColumnWidths* value to *0*. For example, you can set up a two-column list box that contains the names of holidays in one column and dates associated with the holidays in a second column. To present the holiday names to users, specify the first column as the *TextColumn*. To store the dates of the holidays, specify the second column as the *BoundColumn*. To hide the dates of the holidays, set the * ColumnWidths* property of the second column to *0*. Regards, VIJAY KUMAR -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
