strange behaviour in UserForm code

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
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
 
Last edited:

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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).
 
Upvote 0
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:

Rich (BB code):
Private Sub CommandButton1_Click()
    Range("A1").Value = ListBox1.Value
    Range("A2").Value = ListBox2.Value
End Sub
 
Last edited:
Upvote 0
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?
 
Upvote 0
Hi, Andrew
Thank you for your answer. Well indeed, the value does the thing for me too.
Still wondering what happens...

beste regards,
Erik
 
Upvote 0
Yes, I've replayed this behavior. But I've discovered another interesting thing. Alter your code like this:
Code:
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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:
Code:
MsgBox ListBox2
(or Debug.Print)

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

PS for Rick: thanks for showing me how to write "weird" ;-)
 
Upvote 0
There're other twists I wrote about. Look here. :)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top