How to assign a task to individual element of list in excel?

I have added the list from the form which comes from menu bar > View > Toolbar > Forms

I want to assign some task on selection of individual element in the list but when i assign some macro to it its get applied on the whole list.

Say i have a list contains 3 colores - red,green,blue and i want my sheet to be colored depending upo开发者_如何转开发n individual selection. If i select red then the sheet should reflect red color.

You can't do much with the control elements found in the "Forms" window unless you tie them to cells in your sheet.

For the FORMS.Listbox you would need to right click and choose "Format Control...", then

• specify a multi-row range containing the selection elements (Input Range)
• specify a single-cell range containing the selected element (Cell Link)

Then you must right-click again and

• "Assign Macro..." that evaluates the value of the Cell link and acts accordingly(Sub ListBox1_Change())

Note that the Cell link displays the index of the selected option, not the value

Example:

A1 contains "Red"
A2 contains "Green"
A3 contains "Blue"

Forms / Listbox1 / Input Range = $A$1..$A$3
Forms / Listbox1 / Linked Cell = $B$1

Sub ListBox1_Change()
    Select Case [B1]
    Case 1:
        MsgBox "Gimme RED"
    Case 2:
        MsgBox "Gimme GREEN"
    Case 3:
        MsgBox "Gimme BLUE"
    Case Else:
        MsgBox "unknown case"
    End Select
End Sub

Hope that helps