Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Me.ListBoxes.Delete
On Error GoTo 0

If Target.Column = 1 Then
With Worksheets(3).Shapes.AddFormControl(xlListBox, 100, _
ActiveCell.Top, 100, 150)
.Name = "Listbox1"
.ControlFormat.ListFillRange = "Sheet2!a1:a18"

End With
Me.ListBoxes("Listbox1").OnAction = "Box_Click"
End If

End Sub

In a general module (no the module associated with the sheet) put in this
code


Sub Box_Click()
Set lbox = ActiveSheet.ListBoxes(Application.Caller)
sVal = lbox.List(lbox.ListIndex)
ActiveCell.Value = sVal
End Sub

--
Regards,
Tom Ogilvy

"Dave H" wrote in message
...
I'm using the following code to place a pop-up listbox on the worksheet
whenever the user clicks on a cell in column A. The listbox comes up fine
with the correct list. My question is - when the user clicks on the
listbox,
how do I capture their selection? When a listbox is on a userform, I know
I
can use "listbox1.value". I haven't been able to figure out how to do this
when the listbox is not on a form but is "shape" on the worksheet.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If ActiveCell.Column = 1 Then

Worksheets(1).Shapes.AddFormControl(xlListBox, 100, ActiveCell.Top, 100,
150) _
.ControlFormat.ListFillRange = "Sheet2!a1:a18"

End If

End Sub

Thanks!!