Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MultiSelect Listbox | Excel Programming | |||
How do I use a multiselect listbox | Excel Programming | |||
change combobox values one by one based on selection in multiselect listbox | Excel Programming | |||
Multiselect Listbox use | Excel Discussion (Misc queries) | |||
Multiselect Listbox | Excel Programming |