Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    I have a worksheet with a database of materials types, their finish, and some constants. A simplified spreadhseet is attached.

    I've started to make a userform that would allow the user to select a material and get a list of properties based on a selection of type and finish. Since I don't have much experience with creating such userforms I'd like to ask for help.

    I was able to get the combo-box to list the material name, but now I don't know how to list the material type and finish in the appropriate list-boxes, based on the selection of material. I would also like the use to be able to multi-select from the lists of type/finish, and have it all displayed in the list-box at the bottom of the form.

    If it's not too much to ask, can someone help me get started?

    Thanks.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The RowSource of your combo box is ListSheet!ListRange but there is no such sheet or named range in your workbook.

  3. #3
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hans, I'm not sure how this got there, but I removed it from the properties menu. How do I get to list the types and finishes based on a selection from the combo-box?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Check out Excel -- Data Validation -- Dependent Dropdowns from a Sorted List
    It's written for validation, but the same principle can be user for combo boxes.

  5. #5
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='763401' date='04-Mar-2009 13:18']Check out Excel -- Data Validation -- Dependent Dropdowns from a Sorted List
    It's written for validation, but the same principle can be user for combo boxes.[/quote]

    I've looked over this website. I can kind of see how using validation data in my worksheet can be another way to list and filter various items, but I'm confused as to how it can help me sort out the issue with the combo-box in a userform.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='CFDguru' post='763411' date='04-Mar-2009 20:28']I've looked over this website. I can kind of see how using validation data in my worksheet can be another way to list and filter various items, but I'm confused as to how it can help me sort out the issue with the combo-box in a userform.[/quote]
    You have to change the RowSource of the list box in the AfterUpdate event of the combo box.

    Is there any chance you can do this in Microsoft Access? That's a much more natural application for this kind of problem.

  7. #7
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='763427' date='04-Mar-2009 15:19']You have to change the RowSource of the list box in the AfterUpdate event of the combo box.

    Is there any chance you can do this in Microsoft Access? That's a much more natural application for this kind of problem.[/quote]
    I have never used Access before and would not know where to start.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    What is the relationship between finishes, material types and materials?

    Is there a fixed set of finishes that can be used for all materials? Or for all material types of a specific material? Or is it different for each material type?

  9. #9
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='763464' date='04-Mar-2009 16:41']What is the relationship between finishes, material types and materials?

    Is there a fixed set of finishes that can be used for all materials? Or for all material types of a specific material? Or is it different for each material type?[/quote]
    For each material, there could be a few number of types, and for each type there could be a few number of finishes. However, to simplify things, I could probably limit the number of 'types' for each material that could be entered by the user to, let's say 5.

    For example, for a material named Aluminum, there are 4 types that I'm considering, and for each type I could have data for 1 to 5 different finishes.

    Does it make sense?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I have attached a somewhat simplified version in an Access database. I've left the imported Excel sheet and the queries I used to populate the tables; they can be deleted after studying them.

    [attachment=82640:Materials.zip]
    Attached Files Attached Files

  11. #11
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='763500' date='04-Mar-2009 19:05']I have attached a somewhat simplified version in an Access database. I've left the imported Excel sheet and the queries I used to populate the tables; they can be deleted after studying them.

    [attachment=82640:Materials.zip][/quote]
    Thank you very much Hans. That's exactly what I was looking for. Access is all new to me, and I have a few questions. How did you set up the userform? Where is the data read from?

  12. #12
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts
    By the way, does writing the userform code makes it easier if I set up the properties table as shown in the attached file?
    Attached Files Attached Files

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Yes, that would make it easier - I did the same before importing the table into Access (I also removed the blank rows)

  14. #14
    2 Star Lounger
    Join Date
    Oct 2008
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='763609' date='05-Mar-2009 10:11']Yes, that would make it easier - I did the same before importing the table into Access (I also removed the blank rows)[/quote]

    I still not too clear on how to make this in Excel. Let me simplify it further: If I have the following table in Excel in the range from A5 to D9:
    Aluminum Al 6061 Anodized 0.50
    Aluminum Al 6061 Anodized 1200S 0.49
    Aluminum Al 7075-T6 Clear Anodized 0.81
    Stainless Steel / CRES Stainless Steel Polished 0.42
    Stainless Steel / CRES Stainless Steel Sandblasted 0.58

    What would be the VBA code if I was looking for the list of material types for, let's say, Aluminum?

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Clear the list box.
    Loop through the filled cells in column A.
    Each time the value equals "Aluminum", use AddItem to add the value from column B to the list box.

Page 1 of 2 12 LastLast

Posting Permissions

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