CODEWITHSUNDEEP
vbams-accesslistbox
MoondogsMaDawg
MoondogsMaDawg

Reputation: 1714

Access VBA listbox method .Selected versus .ItemData

While working on an application I had trouble getting a selected item in my listbox to actually output the correct row value. I was using the .Selected method to choose which row needed to be selected in order to proceed to the next step, which would pull the bound column's value from the listbox for further processing, without the need of the user to interact with the listbox.

It turns out that .Selected doesn't work the same way as clicking on a listbox row, as its output value remains the last "manually" selected row's bound column value.

I realized I had to follow up the .Selected method with a .ItemData method to actually have the listbox output value to equal the row that I selected in the .Selected method.

Here is the code I used to copy selections on a Vendor PN Lookup Form to a Main Part Search Form:

Private Sub cmdAddTofrmPartSearch_Click()
Dim i as Integer
... [truncated]
Forms("frmPartSearch").lstSearchResults.Selected(i - 1) = True
... [truncated]
Forms("frmPartSearch").txtHiddenPN.Value 
  = Forms("frmPartSearch").lstSearchResults.ItemData(i - 1)
...[truncated]

End Sub

As above, why do I need to use: 

Forms("frmPartSearch").lstSearchResults.Selected(i - 1) = True

AND

Forms("frmPartSearch").txtHiddenPN.Value 
  = Forms("frmPartSearch").lstSearchResults.ItemData(i - 1)

To get txtHiddenPN to receive the correct row value instead of simply setting txtHiddenPN's control source to =[lstSearchResults] and have it update to the row value after .Selected?

What use is .Selected if it doesn't change the output value of the listbox (other than handily, but futilely highlighting a row)? And similarly, why doesn't .ItemData also highlight the row whose bound value is being outputted? Is this bad design, or is there another method that achieves both results simultaneously? Is it best practice to automatically include both methods sequentially? Are there situations where only one method needs to be called?

I'm new to VBA and coding in general, so maybe this is a common occurrence in the field, but it just seems obtuse.

Upvotes: 1

Views: 6302

Answers (1)

user3598756
user3598756

Reputation: 29421

"Selected" ListBox property doesn't affect "Value" one

it doesn't even trigger events

"Selected" property is mainly used to check which listbox elements is selected

you may want to check this

so you wouldn't need Forms("frmPartSearch").lstSearchResults.Selected(i - 1) = True codeline since it has no effect to your code

to have a more thorough knowledge of listbox functioning you may also want to play with it as follows

add this code in a module of your Project

Option Explicit

Sub PlayWithListBox()

With UserForm4
    With .ListBox1
        .AddItem "a1"
        .AddItem "a2"
        .AddItem "a3"
        .AddItem "a4"
    End With
    .Show

    MsgBox "going to use Select property"
    With .ListBox1
        .Selected(2) = True
        Call ShowValueAndListIndex(.value, .List(.ListIndex))
    End With

End With
Unload UserForm4
End Sub


Sub ShowValueAndListIndex(valueStrng As String, listIndexStr As String)
MsgBox "Value: " & valueStrng
MsgBox "listIndex: " & listIndexStr
End Sub

then add to your Project

  • a Userform named after "UserForm4"
  • a ListBox named after "ListBox1" in "Userform4"

and finally add the following code in "UserForm4" code module

Private Sub ListBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
MsgBox "BeforeUpdate"
With Me.ListBox1
    If .ListIndex >= 0 Then Call ShowValueAndListIndex(.value, .List(.ListIndex))
End With

End Sub

Private Sub ListBox1_Change()
MsgBox "Change"
With Me.ListBox1
    If .ListIndex >= 0 Then Call ShowValueAndListIndex(.value, .List(.ListIndex))
End With
End Sub

Private Sub ListBox1_Click()
MsgBox "Click"
With Me.ListBox1
    If .ListIndex >= 0 Then Call ShowValueAndListIndex(.value, .List(.ListIndex))
End With
End Sub

Private Sub ListBox1_Enter()
MsgBox "Enter"
With Me.ListBox1
    If .ListIndex >= 0 Then Call ShowValueAndListIndex(.value, .List(.ListIndex))
End With
End Sub

Private Sub ListBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
MsgBox "KeyDown"
With Me.ListBox1
    If .ListIndex >= 0 Then Call ShowValueAndListIndex(.value, .List(.ListIndex))
End With
End Sub

Private Sub ListBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
MsgBox "MouseDown"
With Me.ListBox1
    If .ListIndex >= 0 Then Call ShowValueAndListIndex(.value, .List(.ListIndex))
End With
End Sub

Upvotes: 1

Related Questions