Values in multiselect listbox
 

Hello All!

I am a newbee in vba, so be indulgent.
I create a listbox in a sheet:

With Sheets("Step4")
Set list_Box = .Shapes.AddFormControl(xlListBox, w, x, y, z)
list_Box.ControlFormat.AddItem "1"
list_Box.ControlFormat.AddItem "2"
list_Box.ControlFormat.AddItem "3"
list_Box.ControlFormat.AddItem "4"
list_Box.ControlFormat.MultiSelect = xlSimple
'list_Box.ControlFormat.LinkedCell = .Cells(i, 9).Address

It goes fine, the list box appears, it is mutli-select. But now, I want to
retrieve the selected items, by their names or position.
It seems I can not do list_Box.Selected(i) nor
list_Box.ControlFormat.Selected(i) because it is not an ActiveX listbox. I
can not do .value because it is multi-select, and .LinkedCell does not seem
to work : I just get nothing or 0.

So how can I extract the selected values from my listbox? Please help me.

Olivier

Values in multiselect listbox
 

This sub should get you started

Public Sub myList_Change()
Dim msg As String
Dim i As Long
Dim appCaller As String

With ActiveSheet

appCaller = Application.Caller
msg = ""
On Error GoTo end_loop
Do While i < 100
i = i + 1
'.ListBoxes (appCaller)
If .ListBoxes(appCaller).Selected(i) Then
msg = msg & .ListBoxes(appCaller).List(i) & vbNewLine
End If
Loop

end_loop:
On Error GoTo 0
MsgBox msg
End With
End Sub


--
__________________________________
HTH

Bob

"Olivier" wrote in message
...
Hello All!

I am a newbee in vba, so be indulgent.
I create a listbox in a sheet:

With Sheets("Step4")
Set list_Box = .Shapes.AddFormControl(xlListBox, w, x, y, z)
list_Box.ControlFormat.AddItem "1"
list_Box.ControlFormat.AddItem "2"
list_Box.ControlFormat.AddItem "3"
list_Box.ControlFormat.AddItem "4"
list_Box.ControlFormat.MultiSelect = xlSimple
'list_Box.ControlFormat.LinkedCell = .Cells(i, 9).Address

It goes fine, the list box appears, it is mutli-select. But now, I want to
retrieve the selected items, by their names or position.
It seems I can not do list_Box.Selected(i) nor
list_Box.ControlFormat.Selected(i) because it is not an ActiveX listbox. I
can not do .value because it is multi-select, and .LinkedCell does not
seem
to work : I just get nothing or 0.

So how can I extract the selected values from my listbox? Please help me.

Olivier

Values in multiselect listbox
 

Thank you! It helps me a lot.

Best regards,

Olivier

"Bob Phillips" wrote:

This sub should get you started

Public Sub myList_Change()
Dim msg As String
Dim i As Long
Dim appCaller As String

With ActiveSheet

appCaller = Application.Caller
msg = ""
On Error GoTo end_loop
Do While i < 100
i = i + 1
'.ListBoxes (appCaller)
If .ListBoxes(appCaller).Selected(i) Then
msg = msg & .ListBoxes(appCaller).List(i) & vbNewLine
End If
Loop

end_loop:
On Error GoTo 0
MsgBox msg
End With
End Sub


--
__________________________________
HTH

Bob

"Olivier" wrote in message
...
Hello All!

I am a newbee in vba, so be indulgent.
I create a listbox in a sheet:

With Sheets("Step4")
Set list_Box = .Shapes.AddFormControl(xlListBox, w, x, y, z)
list_Box.ControlFormat.AddItem "1"
list_Box.ControlFormat.AddItem "2"
list_Box.ControlFormat.AddItem "3"
list_Box.ControlFormat.AddItem "4"
list_Box.ControlFormat.MultiSelect = xlSimple
'list_Box.ControlFormat.LinkedCell = .Cells(i, 9).Address

It goes fine, the list box appears, it is mutli-select. But now, I want to
retrieve the selected items, by their names or position.
It seems I can not do list_Box.Selected(i) nor
list_Box.ControlFormat.Selected(i) because it is not an ActiveX listbox. I
can not do .value because it is multi-select, and .LinkedCell does not
seem
to work : I just get nothing or 0.

So how can I extract the selected values from my listbox? Please help me.

Olivier