Access: Select All Items in a List Box

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.

5 comments:

  1. 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?

    ReplyDelete
    Replies
    1. I 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.

      If that's right, then you should be able to just change the 0 to a 1 and leave everything else the same.

      Delete
  2. 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?

    ReplyDelete
    Replies
    1. Oh, 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:

      For i = 1 To Forms!frmFormName!lstListBoxName.ListCount - 2

      Delete
  3. Stacy,
    My 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!

    ReplyDelete