Auto-Fill Userform Text Boxes based on a Combobox selection

  • I have created a userform in Excel 2016 for our end users to be able to send in maintenance requests. I have all of the comboboxes referencing their respective data from another worksheet called "Lists". I am trying to get three text boxes to autofill with unique information for the selection from the combobox.


    Being more specific: When our end users choose their plant name from the combobox (titled Plant_Name), the "Plant_Number", "Purchasing_Group", and "Profit_Center" textboxes should auto fill with that particular plants information.

  • Re: Auto-Fill Userform Text Boxes based on a Combobox selection


    One of these examples should help. You can use the ListBox code with a ComboBox, just change the ListBox reference to ComboBox in the code

  • Re: Auto-Fill Userform Text Boxes based on a Combobox selection


    Thanks for the response Roy, I have tried adapting the code from the ComboBox_Lookup file that you sent. I am attaching an image of my current code. I keep getting error. Please keep in mind. I am new to this side of Excel. Thanks in advance for ALL your help!
    [ATTACH=CONFIG]72883[/ATTACH]

  • Re: Auto-Fill Userform Text Boxes based on a Combobox selection


    I am looking to have the Plant Number, Purchasing Group, and Profit Center fields on my user form (ADD-EXTEND) to automatically populate with the respective data to the choice in the Plant Name combobox. All of the information is on the "LISTS" worksheet.

  • Re: Auto-Fill Userform Text Boxes based on a Combobox selection


    When a plant name is selected in the ComboBox, I need the plant Number, Purchasing Group, and Profit Center TextBoxes to auto populate with their respective data from the "Lists" worksheet. The plant number is in column 2, the purchasing group is in column 4, and the profit center is in column 5. if you notice, each plant has its own information.

  • Re: Auto-Fill Userform Text Boxes based on a Combobox selection


    YES!! That is it!! You are a great big bowl of awesome sauce!! The only problem now, is that when I click my submit button to push the entered data to the worksheet, I get an error message


    Run-time error '424':
    Object Required.


    Any thoughts?

  • Re: Auto-Fill Userform Text Boxes based on a Combobox selection


    This has nothing to do with my code

    Quote

    Run-time error '424':
    Object Required.


    I am reworking all of the code.
    Tomorrow you have a example.

  • Re: Auto-Fill Userform Text Boxes based on a Combobox selection


    This is truly a work of art. I seriously hope it did not take up too much of your time. I would have never figured this out in the time frame I have. I do have two questions concerning this form.


    1. How and where would I place code that will populate the "Lot Size" textbox with "HB" if "VB" is chosen in combobox "MRP Type"? If the user choses "ND" in the combobox "MRP Type" then the "Lot Size" textbox is to remain blank.
    2. If I wanted to lock down this userform so that only certain users could access it, how and where would I structure that code? The list of users in on the "LISTS" works sheet column "O".


    Oh, and you can clear that password to get into the workbook. I can reestablish that security when this project is completely finished.

  • Re: Auto-Fill Userform Text Boxes based on a Combobox selection


    Quote from Robert Conklin;794755

    Thanks for the response Roy, I have tried adapting the code from the ComboBox_Lookup file that you sent. I am attaching an image of my current code. I keep getting error. Please keep in mind. I am new to this side of Excel. Thanks in advance for ALL your help!
    [ATTACH=CONFIG]72883[/ATTACH]


    Sorry about the delay, I've had no Internet due to a fault for a while.


    I've added code to do this. It should be quite simple to follow but let me know if it's not clear.


    Code
    1. Private Sub Plant_Name_Click()
    2. With Me
    3. .Plant_Number.Value = LISTS.Cells(.Plant_Name.ListIndex + 2, 2).Value
    4. .Purchasing_Group.Value = LISTS.Cells(.Plant_Name.ListIndex + 2, 4).Value
    5. .Profit_Center.Value = LISTS.Cells(.Plant_Name.ListIndex + 2, 5).Value
    6. End With
    7. End Sub
  • Re: Auto-Fill Userform Text Boxes based on a Combobox selection


    No problem Roy, I completely understand! I am currently without internet at my home due to a lightning strike. So I understand. Dotchiejack has been helping me out. Take a look at his spreadsheet above and let me know what you think?

  • Re: Auto-Fill Userform Text Boxes based on a Combobox selection


    Quote

    1. How and where would I place code that will populate the "Lot Size" textbox with "HB" if "VB" is chosen in combobox "MRP Type"? If the user choses "ND" in the combobox "MRP Type" then the "Lot Size" textbox is to remain blank.


    For this question add this code

    Code
    1. Private Sub C_04_Click()
    2. If C_04.Value = "VB" Then T_11.Value = "HB"
    3. If C_04.Value <> "VB" Then T_11.Value = ""
    4. End Sub
  • Re: Auto-Fill Userform Text Boxes based on a Combobox selection


    Ok, I have moved all of my other worksheets to the working copy that you sent me. I have not changed anything in your code other than adding the date and time to the Userform_Initialized sub and make some corrections to the cell placement in LB_01_Click sub. Now when I click an entry in the "Click to Change" box at the bottom, I get the following error.


    Run-time Error '-2147352571 (80020005)'
    Could not set the value property. Type Mismatch


    The error message is in the LB_01_Click sub, line T_21.Value = LB_01.Column(33). I looked at both the value on the worksheet and the value in the property box for the textbox and they match. What am I missing?