PDA

View Full Version : Solved: Listbox With Checkboxes inside



taporctv
06-15-2007, 08:49 AM
Ive been searching the forum for code on how to have a list box and then have a check box next to the text within the listbox? Is this possible?

JKwan
06-15-2007, 08:55 AM
Change your ListStyle to fmListStyleOptionn and your MultiSelect To fmMultiSelectExtended.

Now, you should have CheckBoxes.

lucas
06-15-2007, 08:57 AM
I don't think so...but you can change the multiselect propterty of the listbox so they can select more than one item.

taporctv
06-15-2007, 08:58 AM
Private Sub Userform_Activate()

With QueryListBox
.AddItem "Query 1"
.AddItem "Query 2"
.AddItem "Query 4"
.AddItem "Query 5"
.AddItem "Query 6"
.AddItem "Query 7"
.AddItem "Query 8"
.AddItem "Query 9"
.AddItem "Query 10"
.AddItem "Query 11"
.AddItem "Query 12"
.AddItem "Query 13"
.AddItem "Query 14"
.AddItem "Query 15"
.AddItem "Query 16"
End With

My Listbox is not populating. Am I using the wrong event?

JKwan
06-15-2007, 09:01 AM
I pasted your code into my form, no problem.

JKwan
06-15-2007, 09:02 AM
If you don't change to fmMultiSelectExtended, you get Option boxes, not Check Boxes.

lucas
06-15-2007, 09:02 AM
That works for me....in fact just plain multiselect works with it too for multiple selections.

lucas
06-15-2007, 09:09 AM
double check that the name of your lisbox matches QueryListBox

activate code has to be in the userform code module

JKwan
06-15-2007, 09:11 AM
That works for me....in fact just plain multiselect works with it too for multiple selections.

Lucas, you are correct. Probably using plain multiselect is a better choice (no need to hold control key or shift key)

taporctv
06-15-2007, 09:23 AM
With this code:
Private Sub Userform_Activate()

With QueryListBox
.AddItem "Query 1"
.AddItem "Query 2"
.AddItem "Query 4"
.AddItem "Query 5"
.AddItem "Query 6"
.AddItem "Query 7"
.AddItem "Query 8"
.AddItem "Query 9"
.AddItem "Query 10"
.AddItem "Query 11"
.AddItem "Query 12"
.AddItem "Query 13"
.AddItem "Query 14"
.AddItem "Query 15"
.AddItem "Query 16"
End With
The listbox briefly flickers and is empty.

lucas
06-15-2007, 09:25 AM
why don't you post your workbook and this can be fixed easily I'm sure.

lucas
06-15-2007, 09:26 AM
this listbox is on a userform and not on a sheet correct?

taporctv
06-15-2007, 09:35 AM
Oops, it was on a worksheet. I feel stupid now.

taporctv
06-15-2007, 09:44 AM
why don't you post your workbook and this can be fixed easily I'm sure.

Like the actual file? or code?

Its now under a workbook_open event. As soon as i open my excel sheet i see the list box populate then become empty. And the size of the list box get s bigger than what i set it when i designed it. How can i prevent this?

lucas
06-15-2007, 10:00 AM
post the file....click on post reply at the bottom left of the last post..type your message then scroll down and look for a button that says manage attachments

Your problem depends on several things...
is the lisbox created from the forms toolbar or the visual basic toolbox...?

taporctv
06-15-2007, 10:24 AM
I drew the listbox on sheet1 using the control toolbar.

Norie
06-15-2007, 11:05 AM
Why is the workbook open event in a worksheet module?

lucas
06-15-2007, 11:10 AM
Set your listfillrange property for the listbox to AA1:AA3 as shown
This is the easiest way....
Workbook open code should go in the thisworkbook module and your public subs that are not command buttons should go in a standard module.

taporctv
06-15-2007, 02:08 PM
Is there a way I can assign the items within the listbox using code? Using the way you gave me, can i still still get the index(i.e. 1,2,3) of the ones checked)

lucas
06-15-2007, 02:29 PM
Are you going to want to run a macro according to which ones are selected....because I don't think a listbox is the best way to do that..

If you want to copy the text to a worksheet...yes.

taporctv
06-18-2007, 04:45 AM
Are you going to want to run a macro according to which ones are selected....because I don't think a listbox is the best way to do that..

If you want to copy the text to a worksheet...yes.

I wanted the user to select which querie(s) they wanted to run. Now that I think about it, a listbox w/ checkboxes might be confusing to the user. Then again, I'll have 20+ queries to choose from. I don't want the sheet to be crowded from all the possible selections.

lucas
06-18-2007, 06:09 AM
Why not use a userform instead of doing this on the sheet?

taporctv
06-18-2007, 12:40 PM
For now I will go with a Listbox with checkboxes inside. This workbook is mainly for internal company use. How would I go about running the queries that were chosen in the listbox? A Select Case using the index values of the listbox items?

taporctv
06-19-2007, 06:22 AM
Here's my code for my listbox:
Dim Item As Integer
For Item = 0 To QueryListBox.ListCount - 1
If QueryListBox.Selected(Item) = True Then
Select Case QueryListBox.ListIndex
Case 0
Query1
Case 1
Query2
Case 2
Query3
Case Else
QueryListBox.Selected(Index) = False
End Select
End If
Next

When I select all three options from the listbox .ListIndex is always at 2. However if I choose just one option .ListIndex is the correct value. How do I fix this?

taporctv
06-20-2007, 04:57 AM
Thanks guys solved my problem. Turned out I didnt need the querylistbox.listindex in the select statement. Here's what I did
Dim Item As Integer
For Item = 0 To QueryListBox.ListCount - 1
If QueryListBox.Selected(Item) = True Then
Select Case Item
Case 0
Query1
Case 1
Query2
Case 2
Query3
Case Else
QueryListBox.Selected(Index) = False
End Select
End If
Next