Last week I was working on a Microsoft Access database I've been building and was making some changes to a form that allows users to select one or many items from a list box in order to set criteria for a report. By default, I wanted everything in the list box to be selected upon opening the form. Unfortunately there didn't seem to be a built-in "select all by default" option, so I put a little bit of code into the Form_Open event.
It did the trick for me, so I thought I'd share it with you today in case you're looking for the same thing:
Dim i As Integer
For i = 0 To Forms!frmFormName!lstListBoxName.ListCount - 1
Forms!frmFormName!lstListBoxName.Selected(i) = True
Next i
For the sake of any VBA beginners out there, I'll just remind you that you're going to want to change "frmFormName" to the name of your form, and "lstListBoxName" to the name of your list box.
It did the trick for me, so I thought I'd share it with you today in case you're looking for the same thing:
Dim i As Integer
For i = 0 To Forms!frmFormName!lstListBoxName.ListCount - 1
Forms!frmFormName!lstListBoxName.Selected(i) = True
Next i
For the sake of any VBA beginners out there, I'll just remind you that you're going to want to change "frmFormName" to the name of your form, and "lstListBoxName" to the name of your list box.
Posts
Hi Stacy, I have a list that includes column heads, this seems to select the column head, would you know how to keep the loop from selecting the column head?
ReplyDeleteI don't have anything handy to test this out right now, but my best guess is that you could have your loop start at i = 1 instead of i = 0. If I understand your question right, it's the first item in the list that you don't want selected, as it's a column header. That would be the item in position 0. If your loop never visits 0, then it won't ever be selected.
DeleteIf that's right, then you should be able to just change the 0 to a 1 and leave everything else the same.
Yes, that worked, thanks! However I have another issue. The reason I included column heads is because I am using multiple columns in the list. I am evaluating the value in columns (0) and (3). The code works when I manually multi-select, but returns an error of invalid use of null when I have selected the data using the select all method. It seems that the select all code is not returning all column values. Would you know how to make the select all include multiple columns?
ReplyDeleteOh, my mistake. It's probably having issues because it's now looping through one more time than it needs to, and unable to select the very last item (because there's nothing there!). I think it will work if you change the last number on this line to a 2:
DeleteFor i = 1 To Forms!frmFormName!lstListBoxName.ListCount - 2
Stacy,
ReplyDeleteMy sincere thanks! I've been banging my head against a problem for 3 weeks that this just solved. I'm doing my first VBA programming and picking up on some half written code. This just replaced 2 functions the old programmer had been using!
Dear stacy..... i am making a report based on
ReplyDeletecompany name combo box
region name combo box
territory name list box
but as i am new in access / vba. i am facing lots of problems. is it possible that you check my db where i am making mistake.
hoping for positive response.
irsmalik
Just coming across this VERY helpful tip in 2023. THANK YOU STACY for taking the time to post it. It works perfectly and saved me hours if not days of research. I scoured the web for a solution for this issue that usually included union queries and creating functions and adding it to a combo box row source. For someone using Access without being VBA savvy I found their solutions were overly complex and intimidating. Plus, I already had code in my combo box's row resource and then didn't know how to add a function on top of what was already there (which no one ever addressed that I could find).
ReplyDeleteYour solution is clean and direct and your instructions were easy to follow. Including where to place it (in the form;s Open Event). Many tips I come across omit that step and just provide sample code. My fault for not knowing the software well enough, but seems like someone looking for help should be considered a basic user. Thanks again!
Thank you very much for your feedback. Every now and then I toy with the idea of taking down my site because I don't really update it so much these days, but I still get messages like this and I can see from my visitor stats that these old posts are still helping some people out there. It's really nice to know I could help out. Have a nice day!
Delete