XL: Macro to Insert Data from a List Box into the Active Cell (140034)

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. Before you create the sample macro, follow these steps:

1. In a worksheet, click List Box on the Forms toolbar, and draw a list box on the worksheet.
2. Click the list box to select it. In Microsoft Excel 97 or Microsoft Excel 98, click Control on the Format menu. In Microsoft Excel 5.0 or 7.0, click Object on the Format menu.
3. Click the Control tab, click the Input Range box, and then type or select the cells that contain the information you want to display in the list box.
4. Type the following macro in a Visual Basic module.

Sample Visual Basic Procedure

  Sub Test()

       Dim Lbox As Object

       ' Create the Lbox object, which will be the list box on the Active
       ' sheet.
       ' Activesheet can be changed to sheets("test") where test is the
       ' name of the sheet on which you want the information.
       Set Lbox = ActiveSheet.ListBoxes(1)

       ' Check to see the value (or what is selected) of the list box. If
       ' zero, exit the Sub.
       If Lbox.Value = 0 Then Exit Sub

       ' Change the value of the Active cell to be what is selected in the
       ' list box
       ActiveCell.Value = Lbox.List(Lbox.Value)

       ' Set the list box back to zero or nothing selected.
       Lbox.Value = 0

   End Sub
				

To assign the macro to the list box control, follow these steps:

1. Press and hold the CTRL key, and then click the list box control.
   
   This step selects the list box.
2. On the Tools menu, click Assign Macro. Assign the "Test" macro to the list box.
   
   In Microsoft Excel 97 or Microsoft Excel 98, right-click or CONTROL- click the list box, and click Assign Macro on the shortcut menu. Then, select the Test macro, and click OK.

To test the macro, select a cell in the worksheet. When you click an item in the list box, the item you clicked should appear in the active cell.