+ Reply to Thread
Results 1 to 13 of 13

Userform Combobox value not returned when selected from drop-down list

  1. #1
    Forum Contributor
    Join Date
    02-09-2010
    Location
    Constanta
    MS-Off Ver
    Excel 2007
    Posts
    128

    Userform Combobox value not returned when selected from drop-down list

    Hello,

    I worked on the improvements recommended in this link. Unfortunately, I have hit a road-block.

    In the attached sample file, if I select a value from the combo-box drop-down list, it is not populated in the relevant cell. However, if I enter a new value in the combo-box, it is populated in the relevant cell. I have been staring at it for a while but have not made any progress in resolving it.

    Any guidance would be greatly appreciated.

    Many thanks
    Asha


    Attached Files Attached Files
    Last edited by asha3010; 10-08-2010 at 07:52 AM.

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Userform Combobox value not returned when selected from drop-down list

    Don't use the exit event to run the code.

    What exactly do you want to do? I haven't time to follow all the links to find out.

    Where is the selection to post to/
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Userform Combobox value not returned when selected from drop-down list

    I've changed the code where I think it's relevant
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    02-09-2010
    Location
    Constanta
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Userform Combobox value not returned when selected from drop-down list

    Thanks for taking a look, royUK.

    What exactly do you want to do? I haven't time to follow all the links to find out.
    Worksheet DE_CN is an input worksheet which contains a list of companies. I would like to check if the companies in the input sheet exist in the database (DB_Trade) worksheet. If it does, the sector info (in the input worksheet) is populated from DB_Trade.

    For every unique company that does not exist in DB_Trade, combobox userform prompts the user to input the sector info. When initialised the combobox will contain only unique sorted values of sector data taken from DB_Trade. The combobox list will expand & sort for any new sector data that is added until the userform is unloaded.

    Where is the selection to post to
    Any value either entered in or selected from the combobox should be populated in Col D of DE_CN against the corresponding company (whose name appears in the Textbox).

    Don't use the exit event to run the code.
    Is the afterupdate event a better choice?

    Please let me know if you have more questions.
    Last edited by asha3010; 10-07-2010 at 07:47 AM.

  5. #5
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Userform Combobox value not returned when selected from drop-down list

    I've added an example above, see if this does what you want.

  6. #6
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Userform Combobox value not returned when selected from drop-down list

    Your problem is the fact that you are resetting the combobox in the exit event and hence it has no value when you press the OK button.
    Remember what the dormouse said
    Feed your head

  7. #7
    Forum Contributor
    Join Date
    02-09-2010
    Location
    Constanta
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Userform Combobox value not returned when selected from drop-down list

    Thanks for your responses.

    @romperstomper: Thanks for identifying & explaining the cause of the error, based on which I moved the lines

    Please Login or Register  to view this content.
    to the Commandbutton Exit event and it appears to work.

    @royUK: Yes, your solution also works, thank you!

    royUK had advised against the use of the Exit event, so I was wondering if taking the above approach would give unexpected / unwanted results - though it seems ok now!

    Thanks again
    Asha

  8. #8
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Userform Combobox value not returned when selected from drop-down list

    Why not use the button's click event for it all? Seems the most logical.

  9. #9
    Forum Contributor
    Join Date
    02-09-2010
    Location
    Constanta
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Userform Combobox value not returned when selected from drop-down list

    Thanks for your reply, romperstomper.

    O dear just when I think I am sorted.......when I place those lines in the button's click event, I am back to square one!

    Is it something to do with the way I have written the code in the standard module?

    As per my understanding, the combobox value is returned to the relevant cell when the userform is hidden by clicking the commandbox.

    Here's standard module code relevant to userform2

    Please Login or Register  to view this content.
    Thanks a lot
    Asha

  10. #10
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Userform Combobox value not returned when selected from drop-down list

    You appear to be trying to do too much work and at the wrong times. All you should need is:
    1. The form's Activate event performs the sort of the test range and then loads the combobox
    2. The combo's exit event adds new data to the list range if needed and to its own list
    3. The button hides the form.

    Nothing else should be required since the calling code will retrieve the values it needs anyway.

  11. #11
    Forum Contributor
    Join Date
    02-09-2010
    Location
    Constanta
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Userform Combobox value not returned when selected from drop-down list

    Many thanks, romperstomper for your continued support & more importantly, patience.

    This is one of those days where I feel that for every baby step forward that I have taken towards understanding VBA, I have taken 5 giant leaps backwards!

    I have followed your recommendations (and it works) but am apprehensive to post the final code, in case it is still convoluted!

    Once again, I am very grateful for your invaluable guidance.
    Asha


  12. #12
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Userform Combobox value not returned when selected from drop-down list

    I added a button to fire the code & removed all the unnecessary coding.

  13. #13
    Forum Contributor
    Join Date
    02-09-2010
    Location
    Constanta
    MS-Off Ver
    Excel 2007
    Posts
    128

    Re: Userform Combobox value not returned when selected from drop-down list

    Thanks for the follow up, royUK. Sorry, I am traveling so could not respond earlier.

    Yes, your solution was fine. However I was trying to see if I could have just one commandbutton instead of two. I was able to eventually figure it out.

    Many thanks
    Asha

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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