PDA

View Full Version : Using .Value fails when initialising a user form



mobucl
04-24-2017, 04:58 AM
Hi All,

I have a problem using .Value to determine the value in a list box. To demonstrate i setup a simple userform with ListBox1 and ListBox2. I then added the following code to the userform:


Private Sub UserForm_Initialize()
ListBox1.AddItem "bob"
ListBox1.AddItem "bill"
ListBox1.ListIndex = 1
Box1Val = ListBox1.Value


ListBox2.AddItem "Frank"
ListBox2.AddItem "Harry"
ListBox2.ListIndex = 1
Box2Val = ListBox2.Value
End Sub



Running this i get
Box1Val = "Bill" (expected as ListIndex = 1)
Box2Val = "" (Why am i not getting "Harry"???)


If i replace ListBox2.Value with ListBox2.List(1) i get the correct value - but i want to know why .Value is working in one instance and not another and how to fix this.

Thanks for any help

Matt

Paul_Hossler
04-24-2017, 06:38 AM
Try doing it this way instead

Edit -- actually I realized I was working too hard. This seems easier





Option Explicit
Dim Box1Val As String, Box2Val As String
Private Sub CommandButton1_Click()
MsgBox "Box1Val = " & Box1Val
MsgBox "Box2Val = " & Box2Val

End Sub

Private Sub ListBox1_Change()
If ListBox1.ListIndex = -1 Then
Box1Val = "Nothing Selected"
Else
Box1Val = ListBox1.List(ListBox1.ListIndex)
End If
End Sub

Private Sub ListBox2_Change()
If ListBox2.ListIndex = -1 Then
Box2Val = "Nothing Selected"
Else
Box2Val = ListBox2.List(ListBox2.ListIndex)
End If
End Sub

Private Sub UserForm_Initialize()

ListBox1.AddItem "Bob"
ListBox1.AddItem "Bill"
ListBox1.AddItem "aaaa"
ListBox1.AddItem "bbbb"
ListBox1.AddItem "cccc"

ListBox2.AddItem "Frank"
ListBox2.AddItem "Harry"
ListBox2.AddItem "dddd"
ListBox2.AddItem "eeee"
ListBox2.AddItem "ffff"

' Base 0
ListBox1.ListIndex = 1 ' Bill
ListBox2.ListIndex = 0 ' Frank
End Sub





Also

http://www.vbaexpress.com/kb/getarticle.php?kb_id=303

mikerickson
04-24-2017, 06:56 AM
I've found .Value to be an unreliable way to access what is selected from a ListBox. I prefer to use


With ListBox1
If .ListIndex = -1 Then
MsgBox "nothing selected"
Else
MsgBox .List(.ListIndex)
End If
End With

Also, there are situations where .Value will return an "improper use of null" error, the .Text property won't.

mobucl
04-24-2017, 11:44 PM
Hi Paul and Mikerickson,

Thanks for both of your replies. Looks like i am going to have to rewrite parts of my application to remove .Value!

I think this behaviour is something to do with the item not being selected despite using .ListIndex because if i run this code:



Private Sub UserForm_Initialize()
ListBox1.AddItem "bob"
ListBox1.AddItem "bill"
ListBox1.ListIndex = 1

ListBox2.AddItem "Frank"
ListBox2.AddItem "Harry"
ListBox2.ListIndex = 1
End Sub

Private Sub UserForm_Click()
Box1Val = ListBox1.Value
Box2Val = ListBox2.Value
End Sub


Userform1 initialises - then click on "Harry" in ListBox2 and then on the userform to activate UserForm_Click() both Box1Val and Box2Val return the expected values.....It seems to me that on initialisation ListIndex is only selecting the item in Listbox1 and ignoring Listbox2 until physically clicked by the user....

EDIT - Actually you dont even need to click on "Harry". Just initialising and then clicking on the userform seems to work!


Thanks

Matt