strange behaviour in UserForm code

[erik.van.geit]

Hi,
Happy New Year!

The following occured within a file and I can replicate this error in new files.

I created a userform with 2 listboxes and a button.
Then pasted this code

Private Sub CommandButton1_Click()
Range("A1") = ListBox1
Range("A2") = ListBox2
End Sub

Private Sub UserForm_Initialize()
ListBox1.List = Array(2, 3, 4, 5, 6, 7)
ListBox2.List = Array(2, 3, 4, 5, 6, 7)
    With ListBox1
    .ListIndex = Application.Match(Range("A1"), .List, 0) - 1
    End With
    With ListBox2
    .ListIndex = Application.Match(Range("A2"), .List, 0) - 1
    End With
End Sub

Put a number between 2 and 7 in A1 and A2.

Run the userform.
When clicking the button: cell A2 get's empty value.
EDIT: forgot to mention: this only occurs when you do not click within ListBox2 before you hit the button. When you choose another value within ListBox2, you get the correct value in A2.

You can try this out in 1 minute: do you get this too???

kind regards,
Erik

EDIT: sometimes only occurs when you show the userform the second time

 

[RoryA]

I very much doubt it. I usually use .List(.Listindex) instead.

 

[NdNoviceHlp]

https://support.microsoft.com/en-us/kb/138819
Maybe this is that initialize vs activate thing. Maybe trial using the Activate event? HTH. Dave

 

[erik.van.geit]

I very much doubt it. I usually use .List(.Listindex) instead.

Hello Rory,
Thank you for your input: it is a useful not to difficult workaround.

So you did experience this problem before, I guess, else you wouldn't use the code construction: .List(.Listindex) instead of .Value.
In the mean time I also tried using strings: same problem occured.

 

[erik.van.geit]

https://support.microsoft.com/en-us/kb/138819
Maybe this is that initialize vs activate thing. Maybe trial using the Activate event? HTH. Dave

Thank you, Dave, I tried some variations on the theme using "activate", but that didn't lead to anything for me in this case.

 

[NdNoviceHlp]

Eric this seems to be a fix. I've had some tribulations with Listboxes here....
http://www.mrexcel.com/forum/excel-questions/466168-phantom-listbox-selection.html
Please note that this works only for the Activate event and not the initialize event.... hmmmm?

Private Sub CommandButton1_Click()
Sheets("sheet1").Range("A1").Value = UserForm1.ListBox1.Value
Sheets("sheet1").Range("A2").Value = UserForm1.ListBox2.Value
End Sub

Private Sub UserForm_Activate()
UserForm1.ListBox1.List = Array(2, 3, 4, 5, 6, 7)
With UserForm1.ListBox1
.ListIndex = Application.Match(Sheets("sheet1").Range("A1"), .List, 0) - 1
End With
UserForm1.ListBox2.List = Array(2, 3, 4, 5, 6, 7)
With UserForm1.ListBox2
.ListIndex = Application.Match(Sheets("sheet1").Range("A2"), .List, 0) - 1
End With

'***this is the fix
UserForm1.ListBox2.SetFocus

End Sub

Have a nice day! Dave

 

[erik.van.geit]

Yes, Dave, works for me too. Thanks.

I still would use Rorys suggestion, because that fix seems more logic to me, it makes more sense within code.

.List(.Listindex)

 

[Sektor]

.List(.ListIndex) - this is the same (and only) way an item can be fetched from dreaded Dropdown Form Control.