+ Reply to Thread
Results 1 to 15 of 15

Textbox to populate based on combobox selection

  1. #1
    Forum Contributor
    Join Date
    07-25-2012
    Location
    Winterville, NC
    MS-Off Ver
    Excel 2013
    Posts
    141

    Textbox to populate based on combobox selection

    Hello! I need some assistance!

    Need: Combobox selection causes textbox below it to autofill. For example, using my sample, if someone makes the choice of C6 in the combobox, D6 will autopopulate into the textbox.

    Issues: I have two lists in Cells C5:D7. There is lots of other stuff all around these lists so I can't use the entire column range in any formulas/VBA. In the real workbook the list is a dynamic data extraction so the length of the lists changes all the time (longer and shorter). If you look at the code for the combobox rowsource (which I'm sure I 'borrowed' from this site somewhere, you'll see how the list changes length. Maybe that's irrelevant if row # could be used. So that's what I was thinking....to pull by row #.....but I have gotten myself so confused that now I can't think straight. Any suggestions would be GREATLY appreciated! THANKS!

    Sample workbook: Test.xlsm


    If this post was helpful or at least brought a smile to your face , please click the Add Reputation button as a way to say thank you!!!!!!

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Textbox to populate based on combobox selection

    1) Never activate a sheet and select a cell to read its contents, that's what humans do, and the macro recorder records human actions, so you need to get in the habit of removing all those extra steps.

    I've rewritten your cbLista initialize code to use ALL the values in column C starting at C6 going down as far as there are values. The sheet is never "activated". In fact this source sheet can be hidden if you wish.

    2) The code suggestion:

    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Contributor
    Join Date
    07-25-2012
    Location
    Winterville, NC
    MS-Off Ver
    Excel 2013
    Posts
    141

    Re: Textbox to populate based on combobox selection

    I appreciate your advice. I'm still stuck though.

    Please Login or Register  to view this content.
    This gives me a "Run-time error '1004': Unable to get VLookup protperty of the WorksheetFunction class"

    Please Login or Register  to view this content.
    This gave me "Run-time error '1004': No cells were found" even though there is a list. Further suggestions? Thanks

  4. #4
    Forum Contributor
    Join Date
    07-25-2012
    Location
    Winterville, NC
    MS-Off Ver
    Excel 2013
    Posts
    141

    Re: Textbox to populate based on combobox selection

    I still don't have a solution....any other suggestions out there? Thanks

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Textbox to populate based on combobox selection

    Any chance your values in column C are not constants? Notice the updated code is looking for constants, not formulas. If you post your workbook WITH the edits and data that is causing the errors, I can most likely spot the problem.

  6. #6
    Forum Contributor
    Join Date
    07-25-2012
    Location
    Winterville, NC
    MS-Off Ver
    Excel 2013
    Posts
    141

    Re: Textbox to populate based on combobox selection

    You are correct...both columns C & D in the real workbook are dynamic data extraction. There is an array formula in both columns and the values are constantly changing.

  7. #7
    Forum Contributor
    Join Date
    07-25-2012
    Location
    Winterville, NC
    MS-Off Ver
    Excel 2013
    Posts
    141

    Re: Textbox to populate based on combobox selection

    Do you think the vlookup is also not working because it's actually formulas in those cells?

  8. #8
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Textbox to populate based on combobox selection

    Then change this:

    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    07-25-2012
    Location
    Winterville, NC
    MS-Off Ver
    Excel 2013
    Posts
    141

    Re: Textbox to populate based on combobox selection

    Thank you...I'll try it. But in the meantime, I already had working code for that....the other poster just was choosing to make it better code. The code I really need to get working is this part:

    Please Login or Register  to view this content.
    It's still giving me an error

  10. #10
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Textbox to populate based on combobox selection

    VLOOKUP is about as basic a function as it gets. Can you post a workbook with that failing so I can review what is happening directly?

  11. #11
    Forum Contributor
    Join Date
    07-25-2012
    Location
    Winterville, NC
    MS-Off Ver
    Excel 2013
    Posts
    141

    Re: Textbox to populate based on combobox selection

    Here it is...it's userform ShiftFilled and it's the 2nd sub from the top. Thanks

  12. #12
    Forum Contributor
    Join Date
    07-25-2012
    Location
    Winterville, NC
    MS-Off Ver
    Excel 2013
    Posts
    141

    Re: Textbox to populate based on combobox selection

    oops...forgot the attachment
    Last edited by acroley1; 06-11-2015 at 03:01 PM.

  13. #13
    Forum Contributor
    Join Date
    07-25-2012
    Location
    Winterville, NC
    MS-Off Ver
    Excel 2013
    Posts
    141

    Re: Textbox to populate based on combobox selection

    I really think this has something to do with columns G & H having array formulas in them. Here are the fomulas: =IF(ROWS(G$64:G64)<=$B$62,INDEX(INDIRECT(G$63),SMALL(IF(Shift_Date=$B$1,IF(LEFT(Employee_Job_Title,2)=$C$2,IF(Shift_Type="Regular",IF(Unit_Location_Worked="01905000 / Central Staffing Core -VMC",IF(Assigned="Not Assigned",ROW(Shift_Date)-ROW(AvailableStaff!$A$2)+1))))),ROWS(G$64:G64))),"")

    Maybe we need to avoid the vlookup option altogether. Any other ideas?

  14. #14
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Textbox to populate based on combobox selection

    Waiting to look at a workbook.

  15. #15
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Textbox to populate based on combobox selection

    Can't you use the ListIndex of the combobox to get the row the selection is from?
    If posting code please use code tags, see here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Populate textbox based off ComboBox Selection
    By ladams41 in forum Excel General
    Replies: 1
    Last Post: 04-09-2014, 10:13 AM
  2. Populate TextBox based off ComboBox selection
    By ladams41 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-07-2014, 01:24 PM
  3. Userform Combobox to populate based on selection in another combobox
    By Excelnoub in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-14-2014, 03:34 PM
  4. [SOLVED] Excel Userform: Populate other controls (i.e. textbox & combobox) based on combobox select
    By MileHigh_PhD in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-22-2013, 04:50 PM
  5. [SOLVED] How do I make a selection from a combobox on my VBA userform populate a textbox?
    By ChristianMba in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-16-2012, 01:08 PM

Tags for this Thread

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