Solved

Speed up selecting all items in list box.

Posted on 2004-08-25
16
417 Views
Last Modified: 2012-06-21
I asked this question about a month ago but didn't really get an answer.  Thought I'd try again:

Is there a faster way to select all items in an extended multiselect listbox with a high ListCount than?

Me.Painting = False
For i = 0 To ProcedureList.ListCount - 1
    ProcedureList.Selected(i) = True
Next i
Me.Painting = True

When ProcedureList.ListCount is higher than ~2000, the above procedure takes 10+ seconds.  

Clicking on the first item, scrolling to the bottom and shift clicking on the last item selects all items almost instantly so I tried to simulate that in code.  These attempts did not work but maybe they will give someone an idea:

ProcedureList.SetFocus
hWndSB = GetFocus
ProcedureList.ListIndex = 0
SendKeys " "
ProcedureList.ListIndex = ProcedureList.ListCount - 1
SendKeys "+ "

ProcedureList.SetFocus
hWndSB = GetFocus
ProcedureList.ListIndex = 0
LngThumb = MakeDWord(SB_THUMBPOSITION, CInt(ProcedureList.ListCount - 1))
lngRet = SendMessage(hWndSB, WM_VSCROLL, LngThumb, 0&)
SendKeys "+ "
0
Comment
Question by:thenelson
  • 7
  • 5
  • 2
  • +2
16 Comments
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 11894378
Where is it getting it's data? Could you run an update query and then requery?
0
 
LVL 39

Author Comment

by:thenelson
ID: 11894453
I could run an update query and requery but all the items would be deselected.  When you requery a listbox all items are deselected by defalt.
0
 
LVL 50

Accepted Solution

by:
Steve Bink earned 500 total points
ID: 11894487
Sorry, no other way.  The ItemsSelected collection for a listbox is a hidden, read-only collection.  The only way to alter it is by mouse-click or spacebar.  I would not recommend trying to use SendKeys - it's dangerous to depend on it to work properly in all situations - but you'll need to make sure the listbox's MultiSelect property is set to Extended if you want to try them anyways.
0
 
LVL 84
ID: 11894626
You could try adapting some of the Listbox code at stephen Lebans site:

www.lebans.com

He does some pretty cool stuff with listboxes ...
0
 
LVL 39

Author Comment

by:thenelson
ID: 11895165
Yes, Stephen does do some pretty cool stuff.  I looked at his site again after reading your suggestion but, unfortunately he doesn't do this.
0
 
LVL 84
ID: 11895263
True ... thought perhaps you could adapt his code pertaining to listbox selections to your use ...  I seem to recall code that iterated the listitems, but could be wrong ..
0
 
LVL 39

Author Comment

by:thenelson
ID: 11895889
I just discovered that:

ProcedureList.SetFocus
SendKeys "^{END}"
SendKeys "^+{HOME}"

Highlights all the items very quickly but it does not select the items (ProcedureList.Selected (anyitem) = False even though it is highlighted.)
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 11896093
Maybe if you SendKeys(" ") right after it?  Still have to object to the use of SendKeys, but whatever works, right?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 39

Author Comment

by:thenelson
ID: 11897016
SendKeys (" ") doesn't work either.  I don't like using SendKeys but if Bill's boys don't give us the tools to get something done....
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 11897275
I feel your pain.  :(  Here's another idea...thinking out of the box here, so excuse my vagueness...

How about using a checkbox instead of a command button for your "Select All" item?  If the checkbox is checked, grey out (disable) the listbox.  If the box is unchecked, enable the listbox.  Since you can retrieve the complete list of items from the listbox at any time to use in an update query, etc., you do not specifically NEED the user to check each individual row... you'll just need to know if they want all items or not.  A possibility?
0
 
LVL 39

Author Comment

by:thenelson
ID: 11898164
I figured something out.

Since there is no need to have nothing selected in ProcedureList, I check to see if anything is selected.  If nothing is selected, I highlight everything:

strIn = ""

For Each varItm In ProcedureList.ItemsSelected
    strIn = strIn & ProcedureList.Column(8, varItm) & ","
Next varItm

If strIn = "" then
   ProcedureList.SetFocus
   SendKeys "^{END}"
   SendKeys "^+{HOME}"
   'no filter
Else
   'create the filter
End If
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 11903474
From what you said earlier, that will not solve the problem of being able to detect the selection.  That gets everything highlighted, though, which is a start.  :)  Maybe the Selected property is changed after the control loses focus or otherwise has a chance to update...?
0
 
LVL 39

Author Comment

by:thenelson
ID: 11905675
Actually, this is what I ended up with. This simulates setting every item true and does it very quickly:

Dim TextBoxIsSelected As Boolean

Private Sub Form_Open(Cancel As Integer)
TextBoxIsSelected  = False
....
End Sub

Private Sub TextBoxRequeryButton_Click()
TextBoxIsSelected  = False
....
End Sub

' The SendKeys routine activates the TextBox click and update events,
' but does not change the TextBox.ItemsSelected collection.
' The MouseDown event signals the user has clicked in the TextBox

Private Sub TextBox_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
TextBoxIsSelected  =  True
End Sub

' Then at the point in code where I create the filter:

Private Function CreateReportFilter() As String
Dim strIn As String, varItm As Variant

strIn = ""

For Each varItm In ProcedureList.ItemsSelected
    strIn = strIn & ProcedureList.Column(8, varItm) & ","
Next varItm

If strIn <> "" Then
    CreateReportFilter = "LedgerID In(" & Left(strIn, Len(strIn) - 1) & ")"
Else
    If SelectedProcedure Then
        CreateReportFilter = "LedgerID = 0"  'no records
    Else
        CreateReportFilter = "LedgerID > 0"   'all records
        TextBox.SetFocus
        SendKeys "^{END}"
        SendKeys "^+{HOME}"
    End If
End If
End Function

0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 11906293
Outstanding!  I am guessing that SelectedProcedure is a checkbox or similar item the users can select to indicate they want everything from the listbox...?  What is the purpose of TextBoxIsSelected?  I understand it is a boolean flag for the user clicking in the textbox, but is there any logic applied to the value in your form?

A very creative way to solve an annoying problem.  To those about to debug, I salute you!  :þ
0
 
LVL 39

Author Comment

by:thenelson
ID: 11908179
Oops! In pasting the code, I got the references mixed up.  Here it is again corrected:  

This is what I ended up with. This simulates setting every item true and
does it very quickly:

Dim ListBoxIsSelected As Boolean

Private Sub Form_Open(Cancel As Integer)
ListBoxIsSelected  = False
....
End Sub

Private Sub ListBoxRequeryButton_Click()
ListBoxIsSelected  = False
....
End Sub

' The SendKeys routine activates the ListBox click and update events,
' but does not change the ListBox.ItemsSelected collection.
' The MouseDown event signals that the user has clicked in the ListBox

Private Sub ListBox_MouseDown(Button As Integer, Shift As Integer, X As
Single, Y As Single)
ListBoxIsSelected  =  True
End Sub

' Then at the point in code where I create the filter:

Private Function CreateReportFilter() As String
Dim strIn As String, varItm As Variant

strIn = ""

For Each varItm In ListBox.ItemsSelected
    strIn = strIn & ListBox.Column(8, varItm) & ","
Next varItm

If strIn <> "" Then
    CreateReportFilter = "ctlFilter In(" & Left(strIn, Len(strIn) - 1) & ")"
Else
    If TextBoxIsSelected Then
        CreateReportFilter = "ctlFilter = 0"  'no records
    Else
        CreateReportFilter = "ctlFilter > 0"   'all records
        ListBox.SetFocus
        SendKeys "^{END}"
        SendKeys "^+{HOME}"
    End If
End If
End Function

0
 
LVL 4

Expert Comment

by:Colonel32
ID: 11920549
I thought there was a chance that we might use the API here
http://www.devx.com/vb2themax/Tip/19236

but as warned by MVPS (and I discovered) Access controls are too phoney to permit this approach:
http://www.mvps.org/access/api/api0027.htm

SendKeys it is :-/
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now