List box (form control) help

[mrzoogle]

Hi all,


I am having problem with using List box (form control). What I want to do is to be able to multiselect the lists and count the numbers from the lists which users have selected.

So if users have selected 3 then the output will be 3.


I am able to do that with combo box (form control)since "cell link" value from combo box are incremental and I can use if formulae to manipulate the data.


Though "cell link" in List box doesn't seem to work as it is defaulted at "0" whatever selection you make.


I have provided the spreadsheet example for your referencing as well.


http://www.mediafire.com/view/?9j993ir9j35uv9i


Please let me know if you have any questions and looking forward to learn something new again !!


Regards,

Z.

 

[NARAYANK991]

Hi ,


You will need VBA to do this ; enter the following code in a module in your VBA Project Explorer , do multi-selection in your list box , and then run this code and see what you get :

[pre]

Sub MyListBox()
Dim lb As ListBox
Dim i As Long, temp

Set lb = Sheets("Sheet1").ListBoxes(1)

With lb
For i = 1 To .ListCount
If .Selected(i) Then temp = temp & i & " " & .List(i) & Chr(10)
Next i
End With

MsgBox "Selected items were:" & Chr(10) & temp

End Sub

[/pre]
Narayan


P.S. This is from this link : http://www.excelforum.com/excel-programming/704435-form-control-listbox-vba.html

 

[mrzoogle]

Hi NARAYANK991,


Thanks for your time and looking into it. Though this is not exactly what I am looking for. Please refer to the new file where I have explained in more detail.


http://www.mediafire.com/view/?sfbtg1v1arz193t


Hope to hear soon.


Regards,


Z.

 

[NARAYANK991]

Hi ,


Try the following code in a VBA module :

[pre]

Function LC(ByVal listboxname As String)
Application.Volatile
Dim lb As ListBox
Dim c As Integer
Set lb = Sheets("Sheet1").ListBoxes(listboxname)

With lb
For i = 1 To .ListCount
If .Selected(i) Then c = c + 1
Next i
End With

LC = c

Set lb = Nothing
End Function

[/pre]
Use this code by putting in any cell the formula =lc("List Box 2") , since the list box in your worksheet is named "List Box 2".


Narayan

 

[SirJB7]

Hi, mrzoogle!


Give a look at this file:

http://dl.dropbox.com/u/60558749/List%20box%20%28form%20control%29%20help%20-%20test%20%28for%20mrzoogle%20at%20chandoo.org%29.xlsm


In the highlighted yellow cells you'll find formulas to handle the two drop down list controls: your original (form control) and a new one (ActiveX). I'd recommend you to switch from form controls to ActiveX controls as they're easier to handle and to assign VBA code and properties.


When you insert a control, the upper part contains the form controls and the lower part the ActiveX ones.


I modified your code in the same way as NARAYANK991 posted before (he's always looking over my shoulder, it's only he writes faster) and added a new function that perform the same action, just fyi.


UDF functions don't get recalculated immediately so I added code for both lists to get sheet recalculated whenever you select or deselect an item. For the new control you might want to play with property MultiSelect: single, only one selection and linked cell works (useful for cost, notice the two columns and the BoundColumn property); Multi, multiple selection allowed on a one by one basis; Extended, idem but with Ctrl, Shift, End, ...


Just advise if any issue.


Regards!

 

[mrzoogle]

NARAYANK991 & SirJB7, You guys are awesome !!!!!!


Thanks for educating me with UDF and ActiveX controls. This is so cool and currently I understand few of the codes so doing some homework to understand everything in here


Definitely will get back to you with questions


Thanks again for your time and if you could show me how to manipulate data in ActiveX control (ie. being able to delete from the list box) that would be great.


Regards,


Z.

 

[SirJB7]

Hi, mrzoogle!


Glad to help you. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.


And about your new last asked theme, give a look at this file:

http://dl.dropbox.com/u/60558749/List%20box%20%28form%20control%29%20help%20%28for%20mrzoogle%20at%20chandoo.org%29.xlsm


You'll find there the three possible versions of an ActiveX drop-down listbox control: they vary in property MultiSelect (first, Single; second, Multi -more than 1 click allowed-; last, Extended -more than 1 with Ctrl, and supports Shift/End-).


Note that the first have a linked cell for selected item, and both second and third haven't that property (as they return multiple values), so a little of code is needed to display the list of selections. Used control MouseUp and KeyUp events (last step when selecting).


Hope you have a little guide to start playing.


Regards!

 

[mrzoogle]

Hi SirJB7,


Thanks for this as it have given me more clear view on differences and its functionality.


Regards,


Z.