Auto-Fill Userform Text Boxes based on a Combobox selection

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


    I have looked at the code. Do you understand it? It's not very clear in my opinion, I hate code that is written like that. It should be easy to understand in case you need to re-visit it and especially when writing for someone else.

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


    Thanks to the phenomenal coding by dotchiejack, I was able to take what I learned from his code (which to be honest was a bit intimidating), but was able to apply some of it to royUK's more condensed code almost to completion.


    The only thing I lack is the ID field and how it works.
    The LBL_Review field at the bottom of the userform and how it works.
    The close button should close the userform.
    The clear fields button should clear the userform, but keep the same ID number.
    Also, instead of a Change button, I would like to change it to an Approve button that would fill in the Approved By field with the windows username. The kicker is, that button should only be used certain people who have authorization to approve the requests.


    The workbook labeled "Long Code" is what dotchiejack created, and it works like a champ. My goal is to get the same thing working in the "Short Code" workbook but in a condensed easy to understand code. Again, I REALLY appreciate everything that you guys have done and are doing to help!!

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


    Now why would I be getting an error message when I copy/pasted the code from the "Short Code" spreadsheet above to another spreadsheet and set it up exactly like the above spreadsheet. When I click the Add-Extend button I get the following error message:


    Run-time Error '424'
    Object Required


    It points to the 'Sub Userform_Activate()' and references line 'Me.Plant_Name.List = Range(Cells(2, 2), .Cells(.Rows.Count,1).end(X1UP)).Value'


    Both workbooks are identical, but the newer version gives the error message.

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


    Robert Conklin
    Here is the code. Short enough???


    A big remark: You can lead a horse to the water, but you can not force it to drink.
    @ royUK, as super moderator, you should know better

    Quote

    It looks like you have either renamed or removed this control



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


    That remark in blue is nothing to do with me, unless you are aiming it at me. In which case i have no idea what your problem is.

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


    @ royUK
    It has nothing to do with the blue remarks, these are for TS
    but with this, I mean you, you should know better.

    Quote

    It looks like you have either renamed or removed this control


    And this

    Code
    1. I have looked at the code. Do you understand it? It's not very clear in my opinion


    This was easy code for TS
    Where do you think where you wil have more typo's ??
    If you use C_01 or Plant_Name for a combobox
    VBA is a language you have to learn.

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


    Dotchiejack, if you don't like my opinions then tough. Everyone is allowed their opinions.You can avoid typos by using Option Explicit and when providing code for beginners it should be easy for them to understand and using Square Brackets has always been considered poor coding.

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


    Robert


    Your error is caused because when using the code in a different workbook you haven't change the worksheet's Code Name to LISTS as it was in the original workbook. Read this

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


    Gentlemen, this was not supposed to turn into a pissing match. I apologize if I turned it into one. From what I have learned, there are many different ways to get to what you want to do in VBA, and you two both have a different way to get there. They are both right, and they both work. But for my immediate needs, the shorter and clearer the better. Both of you have helped me more than I can ever say. I have learned from both of you. As I have told royUK, I am learning VBA, but the time frame that I have to complete this project is too short for me to get a solid grasp on the language in order to complete it myself. This is the reason I have been asking so many questions. As of today, I only have two more weeks to complete the project and what we have been working on is the bulk. I can hopefully take what you guys have helped with and what I have learned and apply it to the rest of the larger workbook. Again, I apologize if I started any trouble.

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


    This is certainly not what I want

    Quote

    turn into a pissing match


    This is true

    Quote

    you two both have a different way to get there


    But:
    Two progamers need to be different from time to time :thumbcoo:


    My remarks,
    1. you are downgrading if you don't use tables (or if you prefer defined names ranges) Because they are dynamic.
    2. Save your workbook als XLSB instead of XLSM, size is smaller and starts faster.

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


    Thanks for the tip dotchiejack! I tried to incorporate your code into my workbook to fill my LBL_Review field like you did your LB_01 field, but I am missing something...



    Also, I tried to use your code for what I think is the "Clear Fields" command button, but that is not working either.

    Code
    1. Private Sub_CMB_Clear_Click()'Clear ALL Fields
    2. For Each Ctrl In Controls
    3. If TypeName(Ctrl) = "TextBox" Or TypeName(Ctrl) = "Combobox" Then Ctrl.Value = ""
    4. Next Ctrl
    5. LBL_Review.ListIndex = -1
    6. LBL_Review.TopIndex = 0
    7. Call UserForm_Initialize
    8. End Sub


    I was however able to get the "Approve" button to work, but not exactly like I would have it. I would like it to post to the Approved By field in the userform, and its respective cell in the ADD-EXTEND worksheet as well. As it stands right now, when I click Approve, it posts it to the Approved By field, and then I click submit to post the same to the worksheet.


    I also was not able to decipher the code to get the ID to post automatically or the userform to clear whenever the "Submit Button" is clicked. I liked the pop up confirmation messages also.

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


    Quote from dotchiejack;796113

    @ royUK,
    if you don't like my coding then tough


    Maybe you need a new Title - Internet Warrior.


    If you want to argue don't clutter this Thread.


    Your comments about Tables is correct they are Dynamic, but I don't see any table specific code in your coding. Lists cannot be a Table because it contains empty columns.


    Robert, I'm not carrying on here. My code for data entry works, is legible and I have provided solutions for hundreds of UserForm users, some way more complicated than this. In my opinion you areconfusing the issue by trying to use two totally different approaches. As a beginner you need clear, documented code. Good luck

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


    Hello Robert,


    1. Your LBL_Review is a label, change it to a listbox.
    2. For clearing the fields change this: Call UserForm_Initialize because you use Activate instead.
    3. In your UserForm_Activate code you have to add a line of code to populate the listbox.
    Something like

    Code
    1. LBL_Review.List = Sheets("ADD-EXTEND").Range("A2:AC1000").Value


    but I should use a table.

    Code
    1. LBL_Review.List = Sheets("ADD-EXTEND").Range("YOURTABLENAME").Value
  • Re: Auto-Fill Userform Text Boxes based on a Combobox selection


    Ok, I tried the code in my spreadsheet, but it is still not working. I will make this simple on both of us. I am going to use your original spreadsheet. Everything is already working and I am running out of time. I would like to make a few changes.


    1. I would like to condensed the Equip. Functional Location fields (down from 6 to 1. I will have the end users separate each number with a comma. That will cut out the concatenated field on the user form and the extra columns in the worksheet.


    2. The "Lot Size" (T_11) needs to autofill with "HB" when "VB" is selected in the "MRP Type" combobox.


    3. I would like to change the "Change" command button to "Approve" and when clicked autofill the "Approved By" field (T_24) and the cells in column X on the ADD-Extend worksheet.


    4. Can you label what each sub does so I can reference it for the other worksheets involved in this project?

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


    GREAT NEWS!! I was able to figure out myself how do correct all of the above. I left the "Change" Command button because I figured out how to use it. I added code to fill in the "Lot Size" field. I also added the "Approve" command button and the code to make it work. Will take a look at the code and make sure that everything looks ok?

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


    Hey Dotchiejack, I have begun implementing code to the rest of my project. Can you take a look at this code and tell me why I am unable to enter data into my "New Part Description" field (T_10)?


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


    Ok, I figured out the above. I just deleted the field and created a new one. Now it works fine. Now, for some reason in the code above, the ID field does not cycle to the next number after an entry is sent and the List Box "Click to Review" field is not populating. Any suggestions?


    I also noticed that you inserted a table into the worksheet. How did you set that up?