CODEWITHSUNDEEP
vbalistboxuserform
ACCtionMan
ACCtionMan

Reputation: 511

VBA ListBox - Selected Value is Returning a Blank

I have a UserForm with some radio buttons and two single column ListBox's. I'm having trouble returning the value from the second ListBox under certain circumstances.

If the user selects one of the radio buttons they get a series of items to select from in the first ListBox. Once they select from the first ListBox, the 2nd ListBox gets populated with items.

If they select the other radio buttons, the two ListBox's just get populated with a single value "Not Applicable" and I'm selecting it straight away.

This is the code I'm using to set the two ListBox's up with "Not Applicable"

ListBox_First.Clear
ListBox_Second.Clear
ListBox_First.List = Array("Not Applicable")
ListBox_First.Selected(0) = True
ListBox_Second.List = Array("Not Applicable")
ListBox_Second.Selected(0) = True

This is my code to get the value selected

Dim firstValue As String
Dim secondValue As String
firstValue = ListBox_First.Value
secondValue = ListBox_Second.Value

firstValue is ok as it equals "Not Applicable", however secondValue is equal to "". When you look at the form, the value in each ListBox looks like it's selected so I don't understand why it's blank. I have checked the ListCount property and each ListBox only has one item so it should be correctly selected.

If I manually select "Not Applicable" in the second ListBox using the mouse is works fine, but I'm trying to avoid the user having to select it when it's the only value.

I don't know if this is a bug or if I've done something wrong with my code.

I'm working with a product called WRQ Reflections.

Upvotes: 1

Views: 1539

Answers (3)

Michael
Michael

Reputation: 4883

If you don't want to have to SetFocus, here is a function that will return the first selected value from a listbox:

Private Function ListValue(c As Control) As Variant

    Dim i As Long

    For i = 0 To c.ListCount - 1
        If c.Selected(i) Then
            ListValue = c.List(i)
            Exit Function
        End If
    Next

End Function

Usage:

firstValue = ListValue(ListBox_First)
secondValue = ListValue(ListBox_Second)

Upvotes: 1

T.M.
T.M.

Reputation: 9948

Caveats of MS Forms Value property

In addition to Dy.Lees valid answer it might be helpful to note that it's preferrable to avoid .Value and to refer e.g. to ListBox2.List(0, 0) instead.

Furthermore note that due to MS help reference at Value property MS Forms

  • .Value reflects only the "value in the ►BoundColumn of the currently selected rows" and that
  • value cannot be used with a multi-select list box

Upvotes: 4

Dy.Lee
Dy.Lee

Reputation: 7567

The value is recognized by adding the setfocus command.

firstValue = ListBox_First.Value
ListBox_Second.SetFocus
secondValue = ListBox_Second.Value

Upvotes: 3

Related Questions