Listboxes on worksheets

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

Excel31

Excel Programming >> 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

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

Excel Programming >> 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

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

Excel Programming >> 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

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

Excel Programming >> 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

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

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

Excel Programming >> 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

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

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

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