• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 434
  • Last Modified:

Speed up selecting all items in list box.

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
thenelson
Asked:
thenelson
  • 7
  • 5
  • 2
  • +2
1 Solution
 
will_scarlet7Commented:
Where is it getting it's data? Could you run an update query and then requery?
0
 
thenelsonAuthor Commented:
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
 
Steve BinkCommented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
thenelsonAuthor Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
thenelsonAuthor Commented:
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
 
Steve BinkCommented:
Maybe if you SendKeys(" ") right after it?  Still have to object to the use of SendKeys, but whatever works, right?
0
 
thenelsonAuthor Commented:
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
 
Steve BinkCommented:
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
 
thenelsonAuthor Commented:
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
 
Steve BinkCommented:
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
 
thenelsonAuthor Commented:
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
 
Steve BinkCommented:
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
 
thenelsonAuthor Commented:
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
 
Colonel32Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 7
  • 5
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now