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

 

[Rick Rothstein]

No, the cell does not get cleared when I do that. If it helps, I am using Excel 2010 (Version 14.0.7163.5000) on Windows 8.1 (fully up to date).

 

[Andrew Poulsom]

I can reproduce the behaviour you are seeing in Excel 2010, but not for all values.

It doesn't happen if you explicitly reference the objects' properties rather than relying on the default:

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

 

[erik.van.geit]

Hi, Rick
Thank you for responding.
Using Excel 2010 too.

I added this to my post while you were typing.:

sometimes only occurs when you show the userform the second time

Did you try a few times?

This is very wierd: can't remember that I ever had such a bizar problem with such simple code. Can you see a reason?

 

[erik.van.geit]

Hi, Andrew
Thank you for your answer. Well indeed, the value does the thing for me too.
Still wondering what happens...

beste regards,
Erik

 

[Sektor]

Yes, I've replayed this behavior. But I've discovered another interesting thing. Alter your code like this:

Private Sub CommandButton1_Click()
    MsgBox "Value is: '" & ListBox2 & "'"
    Range("A1") = ListBox1
    Range("A2") = ListBox2
End Sub

For the first time I've entered numbers (A1=3 and A2=4), cell A2 became empty after pressing button. After that I enter 4 into A2 again and run Form again. To my surprise, A2 is not emptied! MsgBox shows me "Value is 4". Then I close form and run it again - A2 is emptied out again! And so on and so on.
I use Excel 2016.

 

[Rick Rothstein]

Hi, Rick
Thank you for responding.
Using Excel 2010 too.

I added this to my post while you were typing.:

Did you try a few times?

This is very wierd: can't remember that I ever had such a bizar problem with such simple code. Can you see a reason?

With the .Value properties added (as Andrew suggested)... yes, A2 gets cleared. Also, repeating running the code also cleared A2 for me. But even weirder... I closed Excel, opened it again, set everything up and when I ran the code, this time cell A1 cleared and cell A2 did not! I have not duplicated that result though in two subsequent tests. Maybe something to do with objects which the garbage collection routine did not get a chance to set to Nothing before the next run? Of course that does not explain why using the .Value property causes the problem. Yes, definitely weird indeed.

 

[erik.van.geit]

Sektor,
Thank you for your input.

Another thing I did not write in the original post: when I checked the problem the day before, I got "empty" with:

MsgBox ListBox2

(or Debug.Print)

So, Rick seems to get the inverse problem. Adding Value causes it...
Programming is a risky job

PS for Rick: thanks for showing me how to write "weird"

 

[Sektor]

There're other twists I wrote about. Look here.

 

[erik.van.geit]

There're other twists I wrote about. Look here.

Would it be worth to post this to Microsoft?