ControlSource field being cleared (2000, SP-3)

Topic

New Reply

I have an Excel project that has a problem if I use the ControlSource property of a list box in a userform to populate a cell on a worksheet. The problem ONLY occurs if the VBA editor is also open when the listbox is used. The problem occurs after selecting one of the list entries and exiting the userform. The reference cell contains the selected item but clears almost immediatly when data is entered into a completely unrelated cell. The attached (Zipped) example demonstrates the problem. Note that the problem does not occur until you open the VBA Editor. Also, I noticed that once the problem occurs, I don’t even have to use the userform! Just enter data into the cell, A2, and move the cursor.

This isn’t really critical since the user wouldn’t have VBA opened anyway, It’s just very inconveinient while developing and debuging.

Any help or suggestions would be appreciated.

Reply | Quote

Replies

The list box is populated in code. When the userform is unloaded (i.e. removed from memory), the row source disappears, and hence the control source is cleared too. There are several alternatives:

1) Leave the ControlSource property blank, and set it in the UserForm_Initialize routine:

lb.ControlSource = “Data!A2”

2) Set the RowSource property to a range on one of the worksheets, instead of populating the list box in code.

3) Don’t unload the form, but hide it:

Me.Hide

Reply | Quote

Thanks again Hans, all three suggestions work and I believe I understand why but if it clears the Controlsource for the listbox when I unload the userform, why not the textbox references also? Is it because I populated the list box in code but not the text boxes??

Also why would this happen only when the VBA Editor is open?

Sorry about all these questions but I really need to understand because I plan on doing more work of this type.

Is there some definitive documentation available that really explains all the ins and outs of userforms in excel??

Reply | Quote

I’m sorry, I have no idea, really.

Reply | Quote

OK, I really appreciate all of your help. You all provide a tremendous service to the rest of us.

Reply | Quote