Listboxes on worksheets
 

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

Listboxes on worksheets
 

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

Listboxes on worksheets
 

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

Listboxes on worksheets
 

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).

--
Regards,
Tom Ogilvy

"Jim Rech" wrote in message
...
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
"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!!

Listboxes on worksheets
 

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.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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).

--
Regards,
Tom Ogilvy

"Jim Rech" wrote in message
...
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
"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!!

Listboxes on worksheets
 

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

"Tom Ogilvy" wrote:

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.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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).

--
Regards,
Tom Ogilvy

"Jim Rech" wrote in message
...
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
"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!!