Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Nov 2001
    Location
    Carson City, Nevada, USA
    Posts
    46
    Thanks
    2
    Thanked 0 Times in 0 Posts

    ControlSource field being cleared (2000, SP-3)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ControlSource field being cleared (2000, SP-3)

    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

  3. #3
    Lounger
    Join Date
    Nov 2001
    Location
    Carson City, Nevada, USA
    Posts
    46
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: ControlSource field being cleared (2000, SP-3)

    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??

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ControlSource field being cleared (2000, SP-3)

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

  5. #5
    Lounger
    Join Date
    Nov 2001
    Location
    Carson City, Nevada, USA
    Posts
    46
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: ControlSource field being cleared (2000, SP-3)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •