Listboxes on worksheets

[Dave H]

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!!

 

[Tom Ogilvy]

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

 

[Jim Rech]

You can set a linked cell to hold the selected item's index:

With Worksheets(1).Shapes.AddFormControl(xlListBox, 100, ActiveCell.Top,
100, 150)
.ControlFormat.ListFillRange = "j1:j18"
.ControlFormat.LinkedCell = "h1"
End With

--
Jim Rech
Excel MVP
| 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!!

 

[Tom Ogilvy]

Just note that the linked cell will display the index to the selected item
in the list rather than the value of the item in the list (as displayed).

 

[Tom Ogilvy]

Jim did say that, so I guess I should have said

As a point of emphasis and as stated by Jim, note . . .

In any event, to show the displayed value, you can use the macro I
suggested.

 

[Dave H]

Thank you gentlemen. Your suggestions work perfectly! I tried for several
hours to find some info on this in VBA Help; that wasn't much help but your
answers fixed my problem!

Dave