Listbox selected items

CsJHUN

Active Member
Joined
Jan 13, 2015
Messages
360
Office Version
  1. 365
  2. 2021
  3. 2019
Platform
  1. Windows
  2. Mobile
Hi guys,
Here is the code which i know of, which loop thru listbox items
VBA Code:
for i = 0 to listbox_something.listitems-1
...
next i

Is there any faster way to check if the item is selected? I currently have 4 listbox, with combined 600+ possibilities (all cascade other one until 4th level) and its feels pretty slow.

The code below just checking if anything is selected:
VBA Code:
For i = 0 To box_group.ListCount - 1
If box_group.Selected(i) = True Then group_count = group_count + 1
Next i
For i = 0 To box_one.ListCount - 1
If box_one.Selected(i) = True Then process_count = process_count + 1
Next i
For i = 0 To box_language.ListCount - 1
If box_language.Selected(i) = True Then language_count = language_count + 1
Next i
For i = 0 To box_printitem.ListCount - 1
If box_printitem.Selected(i) = True Then printitem_count = printitem_count + 1
Next i
If printitem_count < 1 Or group_count < 1 Or process_count < 1 Or language_count < 1 Then Exit Sub
And this is just the warmup part.
I also turned off: app.displayalerts, .screenupdating, .calulcation, .enableevents
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
If they are all multi-select listboxes, then I think looping through them is the only option.
I also turned off: app.displayalerts, .screenupdating, .calulcation, .enableevents
This won't make any difference
 
Upvote 0
Solution
Hi,
If you code is just checking that a selection has been made in each listbox, then should be able to condense it a little by using a single function

Place code either in standard module or your forms code page

VBA Code:
Function AllSelected(ByVal Form As Object) As Boolean
    Dim i As Long
    Dim ListBox As Variant
   
    For Each ListBox In Array(Form.box_group, Form.box_one, Form.box_language, Form.box_printitem)
        For i = 0 To ListBox.ListCount - 1
          AllSelected = ListBox.Selected(i)
          If AllSelected Then Exit For
        Next i
          If Not AllSelected Then Exit For
    Next ListBox
End Function

and to use

VBA Code:
If Not AllSelected(Me) Then Exit Sub

Solution not tested and I am afraid, still loops through each list (I personally, am not aware of any other approach) so may not resolve your speed issue.

Hope Helpful

Dave
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top