+ Reply to Thread
Results 1 to 18 of 18

Pulling cell values into text boxes in a userform upon selecting a certain combobox value

  1. #1
    Registered User
    Join Date
    08-15-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    9

    Pulling cell values into text boxes in a userform upon selecting a certain combobox value

    Hi,

    In general, the 'sheet' illustrated below possesses five columns: Name, Colour and Varied Scores;
    ...A ......B .....C ...D ...E
    James .Blue ....5 ...6 ...8
    Brian ..Blue ....3 ...9 ...7
    Tim ....Green ..3 ...7 ...11
    Sam ...Red .....5 ...5 ...5

    In a userform I've created, I have two comboboxes and a single row of three textboxes; the first combobox pulls the Colour values and lists them, the second combobox pulls the Name values and lists them, and the single row of textboxes is intended for a Score to be inserted into each (3, in this example).

    What I'm trying to achieve is make it so that when a Colour is selected in the first combobox, the second combobox is populated with a corresponding number of Names associated with that Colour (say for instance, when Blue is selected in the first combobox, the second combobox will list James and Brian) and then the Scores on the same row (so Blue - James - 5, 6, 8 [in three textboxes]) will populate the textboxes, and be modified within those textboxes, before saving the modifications via a Commandbutton. However, when a different name is selected in the second combobox, the textboxes empty and are populated with Scores associated with that name.

    How could I go about achieving this?

    Thank you in advance!
    Last edited by nashle6; 10-12-2012 at 06:56 PM.

  2. #2
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Pulling cell values into text boxes in a userform upon selecting a certain combobox va

    Hi, nashle6,

    In a userform I've created...
    It would be a lot easier of you attach a sample workbook with that form.

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  3. #3
    Registered User
    Join Date
    08-15-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Pulling cell values into text boxes in a userform upon selecting a certain combobox va

    Hi Holger,

    My apologies for not previously attaching a sample; I've attached one to this response.
    Attached Files Attached Files

  4. #4
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Pulling cell values into text boxes in a userform upon selecting a certain combobox va

    Hi, nashle6,

    if you start working with VBA please take your time and have Google search for the Reddick Naming Conventions - Iīd never name a Textbox Frame, really never.

    And I would take into consideration that any item should only appear once in a ComboBox. Last thing: I wouldnīt place 2 Sets of Textboxes which are to be populated on the change of one ComboBox but to be altered on the change of a second - I would just use one set and fill that only after the name of a person has been selected and leave them empty otherwise.

    Hereīs the code:
    Please Login or Register  to view this content.
    Ciao,
    Holger
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    08-15-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Pulling cell values into text boxes in a userform upon selecting a certain combobox va

    Hi Holger,

    Thank you for your contribution, the combobox's unique value-population segment of the code works swimmingly.
    However, when I try to run the other part of the code, I receive the following error:
    "Run-time error '-2147024809 (80070057)':
    Could not find the specified object."
    This occurs in this line:
    Please Login or Register  to view this content.
    Is there a way to amend it?

  6. #6
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Pulling cell values into text boxes in a userform upon selecting a certain combobox va

    Hi, nashle6,

    the workbook posted from me runs without any error on my laptop with 7 Home Premium and Office2007 Professional. I will have to get another Computer with Office2010 to check if and how to maybe overcome the error.

    Iīll be back later with an update on that.

    Ciao,
    Holger

  7. #7
    Registered User
    Join Date
    08-15-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Pulling cell values into text boxes in a userform upon selecting a certain combobox va

    Thanks for that, Holger.

  8. #8
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Pulling cell values into text boxes in a userform upon selecting a certain combobox va

    Hi, nashle6,

    I could only test it on a system with Windows 2003 Server and Office2010 HSE but encountered no errors there. My only guess right now would be to check inside the VBE (go Tools/Links) all Links for that workbook - not that I expect one to be broken because Excel will update older versions automaticly (but not the other way around).

    Another possibility might be http://support.microsoft.com/kb/2598039.

    If you these hints wonīt help with the solution please upload the workbook causing the error - Iīll give that a try with Excel2003 and the conversion Kit to maybe find out what throws up the exception if nobody else comes up with a solution.

    Ciao,
    Holger

  9. #9
    Registered User
    Join Date
    08-15-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Pulling cell values into text boxes in a userform upon selecting a certain combobox va

    Hi Holger,

    I managed to get the worksheet you uploaded working (as it turned out, I was sampling the code you supplied earlier on in my sample worksheet); I've noticed though that when a colour is selected, the textbox's are populated with values pertaining to players associated with that particular colour (kind of making the player combobox somewhat redundant in that it doesn't need anything stored inside of it selected for the scores to be populated in the textbox's), as opposed to only being populated when a certain player is selected, with only the values associated with that player. So basically it's a drilldown of colour -> player of that colour -> textbox's populated with the scores associated with the selected player.

    I've uploaded another workbook with a new form dubbed 'NewScoreModifier' with the additional row of textbox's removed, would the code work better with it?

    Thanks again.
    Attached Files Attached Files
    Last edited by nashle6; 10-13-2012 at 10:57 PM.

  10. #10
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Pulling cell values into text boxes in a userform upon selecting a certain combobox va

    Hi, nashle6,

    you are the one to work with the form or distribute the workbook, I can just explain how I would like things to be set up (which doesnīt mean that it should be solved in that way).

    Code for NewScoreModifier without commands Clear and Modify:
    Please Login or Register  to view this content.
    Ciao,
    Holger

  11. #11
    Registered User
    Join Date
    08-15-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Pulling cell values into text boxes in a userform upon selecting a certain combobox va

    Hi Holger,

    Your solution works brilliantly.

    Would you be able to explain a method of 'reversing' the function of "Private Sub EntrantCB_Change()" to populate cells using values in the textbox's without needing to create a private sub for each textbox, or a procedure which can produce the same, if not similar result?

    Thank you once again.

  12. #12
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Pulling cell values into text boxes in a userform upon selecting a certain combobox va

    Hi, nashle6,

    do you mean a procedure to be started by CommandButton Modify or are you looking for any automatism (which indeed in your case would mean three procedures for the change or afterUpdate of each TextBox)?

    Ciao,
    Holger

  13. #13
    Registered User
    Join Date
    08-15-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Pulling cell values into text boxes in a userform upon selecting a certain combobox va

    Hi Holger,

    I apologise for my briefness; I'm seeking to, or at least am hoping to run the procedure via a CommandButton module (I'm guessing that it'd be easier out of the two to both create and manage).

  14. #14
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Pulling cell values into text boxes in a userform upon selecting a certain combobox va

    Hi, nashle6,

    depending on your example the search for a name will only deliver one hit (or miss) but getting a longer list might result in the wrong personīs data being updated. Two possible ways around that: search for Name, check Colour, if it doesnīt match use FindNext and start from that address. I really find it a lot easier to use an ID for every person and maybe place that in a textbox which is hidden to the user. There can always be just one hit if the numbers are unique.

    Please Login or Register  to view this content.
    Ciao,
    Holger

  15. #15
    Registered User
    Join Date
    08-15-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Pulling cell values into text boxes in a userform upon selecting a certain combobox va

    Hi Holger,

    Using a slightly more modified workbook, the code doesn't function in spite of no error prompts appearing (generally when the modify commandbutton is clicked, the cells aren't modified). I've attached the aforementioned modified workbook to this reply; there are unique ID's for each entrant (I generated them using a module, which I didn't include with this workbook (although if you need it, I will reply with the respective code)).

    I've tried fiddling around with the sub you supplied, which works excellent unmodified, however I can't seem to get it to function with the workbook I've attached.

    Could you please take a look at it?
    Attached Files Attached Files

  16. #16
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Pulling cell values into text boxes in a userform upon selecting a certain combobox va

    Hi, nashle6,

    searching in Column A and using Offset(0, 1) means looking for the value in Column B (thatīs Gender, not Colour). To make the procedure work you should use Offset(0, 2) instead.

    I see you included the ID but itīs nowhere to be found in the code. Iīd suggest you use EntrantCB with 2 Columns and the second holding the ID having a Width of 0 (not being displayed). Do you think you can make that work?

    Ciao,
    Holger

  17. #17
    Registered User
    Join Date
    08-15-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Pulling cell values into text boxes in a userform upon selecting a certain combobox va

    Hi Holger,

    I've managed to get the code working (by correcting the Offset's dimensions).

    As for the ID, would it be better if I include the code for it in a module, to be uploaded here as an attachment, or just post up the sub's code here (that is, if you need to take a look at it)? As for modifying EntrantCB to handle two columns and the sort, I could only modify the BoundColumn, ColumnCount and ColumnWidths properties, the ControlSource property can't seem to be set on the grounds of an invalid property value-input.

  18. #18
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Pulling cell values into text boxes in a userform upon selecting a certain combobox va

    Hi, nashle6,

    change the code behind the userform to:
    Please Login or Register  to view this content.
    Ciao,
    Holger

+ 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1