Consulting

Results 1 to 10 of 10

Thread: Userform Dependent List Boxes

  1. #1

    Userform Dependent List Boxes

    Hi,

    I created a template about dependent listboxes on the userform and wanted to share it.

    Vba-dependent-listbox.jpg

    Even if the data and product categories are sorted mixed, data are grouped according to the selection from first listbox with dependent list boxes and fast results are obtained.
    Attached Files Attached Files

  2. #2
    Quote Originally Posted by kadrl View Post
    Hi,

    I created a template about dependent listboxes on the userform and wanted to share it.

    Vba-dependent-listbox.jpg

    Even if the data and product categories are sorted mixed, data are grouped according to the selection from first listbox with dependent list boxes and fast results are obtained.

    Thank you - this is very close to what I have been looking for. One small question - I need to repeat this 10x using the same database data. Can I reuse any of the variables or would i need to repeat all of the code 10x? Basically - i need 10 rows of similar drop down boxes (3 dependent and 2 text per row). They will all use the same dataset but then once all of the boxes are filled, I will export that 'config' to another spreadsheet. Could you give me some advice on that?

    Thanks

    Apollo
    Apollo….
    Know just enough to be dangerous!

  3. #3
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,704
    Location
    I like to use the Worksheet code to perform all "listing", This keeps the UserForm code neat and concise. It does mean that the Worksheet Code module most be treated like a Hybrid Class Module: Hybrid since it is still closely tied to the Worksheet; Class module since it will have UserDefined Properties and Methods.

    Start by laying out the Data Base sheet with Headers (Here after I call Headers "Fields"), Make sure the Fields are properly spelled , It's a pain to change them half way thru the project. Change the CodeName of the DataBase Sheet to either something simple, like "DB" or something meaningful Like "DBSteps". Simple is fine when there is only one DataBase Style Sheet, Keep the "DB" prefix if you use something Meaningful so that all DB objects are listed together in the VBA Project Explorer window pane. The CodeName is what will be used in all code, instead of "Sheets("DataBase")"

    Next design the layout of the UserForm, BUT, with just one Row of Controls, If you use Kadri's style of having Labels above the Row, that is Ideal; Name the Controls according to the Label Captions, but suffix a number after. So the First List/ComboBox will be named "Suppliers1," the next, "Cateory1", The "Product1" etc. The remaining 9 Rows will be added later.

    Since you will have 10 Rows of UserForm Controls, I would Write one UserDefined DB Property, "GetList", that could handle 0 to many Parameters, Retrieve a list depending on which Parameters were present
    Public Property Get GetList(Optional MainTxt As String, Optional Text1 As String, Optional Text2 As String, Optional Text3 As String, Optional Text4 As String) As Variant
       If MainText Is Null then GetList = MainList
    
       'Code to generate other list arrays goes here
       'Or call a Sub Function to make said arrays
    End Property
    Finally, you will want a Method "Initialze" to create the first Main list, so that it is ready as soon as the UserForm is Showing. Call this Method from the Sub UserForm_Initialize (or form the Sub Workbook_Open.)

    Back to the UserForm: The Form's Initialize sub should first initialize the DB then load the main list into the first List/Combo Box
    Sub UserForm_Initialize()
        DB.Intialize
        Me.ComboBox1.List = Db.GetList()
    End Sub
    The Codes for each Control is very similar. Remember the numbers after control names?
    '=====Row 1======
    Sub Suppliers1_Exit()
       Me.Category1.List = DB.GetList(Suppliers1.Value)
       End Sub
    Sub Category1_Exit()
       Me.Products1.List = DB.GetList(Me.Supplers1.Value, Me.Category1.Value)
       End Sub
    Sub Products1_Exit()
       Me.Next1.List = DB.GetList(Me.Supplers1.Value, Me.Category1.Value, Me.Products1.Value)
       End Sub
    
    'Repeat this pattern for all Controls in Row 1, adding a reference to the previous control's value as needed
    
    '======Row 2======
    After you have tested the DB Module code and are satisfied, merely copy the first Row's code 10 times and change the numbers after the Control names. Alternately, you can write a "With Events" module and follow this pattern to only use one (set of) sub Procedure(s).
    Last edited by SamT; 06-01-2021 at 03:34 PM.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  4. #4

    Userform Dependent List boxes

    SamT,

    Thank you for the advice. I have inserted and updated the file and code, but am stuck on the first item. I named the sheet GC and copied the 'Public Property' code to the sheet. I am a little confused on how to call out the array and the headers. How does the subsequent code now which column is which? Do I need to set each column as a range? Not quite understanding how GETLIST in the combo box code knows which data to use. Can you expand on that? I've imported and updated all the code on the userform as well.

    File for Reference: Fingerprint Generator 2.xlsm

    Thanks
    Apollo….
    Know just enough to be dangerous!

  5. #5
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,704
    Location
    so far
    Attached Files Attached Files
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  6. #6
    SamT,

    Dude - you ROCK!. Thank you soo much. Looks like the code was a bit over my abilities. OK a lot :-) I owe you a beer or a few! Well, I guess a digital beer.

    Couple items to note that i may have left out -

    1. The Position drop down does not have to be dependent. It's just a short list of numbers/letters.
    2. The Platform Dropdown needs to be dependent, but it applies to all rows of data. (same value used for all other dropdowns).
    3. Maybe obvious, but I deleted all of the dropdowns past the first row. I wasn't sure if i needed to or not. You mentioned to just create the first row for now.
    4. The FI Type, Loadport Type and Loadlock Type are all also non-dependent drop downs.
    5. Ignore anything that refers to a 'Filter' sheet. That was my old unsuccessful way of dependent drop downs. Had done it with Advanced Filter, but it would not work properly. Oddly - the dropdowns woujld show sporadic amounts of the data even though the Advanced filter worked.
    6. if possible - the drop downs should show the data as unique values even though the data is not unique. Each row is ultimately a different item. None of the columns have unique values, but the combination of values on each line are unique.
    7. The GOT Code and FP text fields are of course just the result of choosing the other dependent drop downs.

    Seriously - thank you for the help. I have spent 3 weeks trying to make this work.

    Apollo
    Apollo….
    Know just enough to be dangerous!

  7. #7
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,704
    Location
    I have spent 3 weeks trying to make this work.
    Don't worry about it, I spent 10 years bumping into the issue. Of course, I started with Office 97. I understand MS now has new features that do this for you.

    1. MainList is/are the Position(s) List

    Names are so important I probably spend half my coding time insuring that Control, Field, and Variable Names are both meaningful and consistent. I want the same bit of data to have basically the same Name no matter where it is. Examples... Control:= ioProduct; Variable:= strProduct; Field:= Product. Generally, I remove punctuation from Field Names and replace spaces with underscore. Example... Companies' Products:= Companies_Products, then Controls and variables become cmbCompaniesProducts and strCompaniesProducts. Sometimes when dealing with Data Tables, I create User Defined Enumerations
    Enum SheetColumns
       colProducts = 1
       EmptyColumn1
       colItem
       EmptyColumn2
       colCost
    End Enum
    The only real purpose for this is to make bad code look bad. Compare two good codes that mean the same
    .Range("A" & Rownum) = ComboBox1
    .Range("C" & RowNum) = ComboBox2
    .Range("E" & RowNum) = TextBox1
    
    'These use meaningful Names and the Enumerated Constants
    .Cells(RowNum, colProducts) = cmbProducts
    .Cells(RowNum, colItem) =  cmbItem
    .Cells(RowNum, colCost) = txbCost
    It's really nice when transfering an array of data to a table; I create an Array index Enum and a Column Enum Then
    'UserForm code
       Array(arrItem) = cmbItem
    'WorkSheet Code
       Cells(Rownum, colItem) = Array(arrItem)
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  8. #8
    SamT,

    I feel really weird asking, but have you had any luck making that code work? I tried to expand on your code but had no luck. I did find a couple typo 'errors' in the code. Just trying to do my homework

    Thanks again

    Apollo
    Apollo….
    Know just enough to be dangerous!

  9. #9
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,704
    Location
    have you had any luck making that code work?
    No. It was incomplete. That's why i said "So Far." It was only addressing dependent lists.

    The main reason I had to stop there is that your attached workbook is far from finished and I can't figure out what is actually supposed to be going on.

    Another problem is that this looks like it would be a major undertaking to restructure and complete and I don't foresee having any large periods of free time for the rest of this summer.
    I'm so sorry.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  10. #10
    Completely understandable. The good thing is that now I know it was not just my inexperience that was making this hard Was definitely banging my head trying to figure it out. Seems i got myself into a bit of a mess. I'll try to find another simpler way to do this.

    Thank you for trying though. I really appreciate the help.


    Apollo

    Keep up the fight.
    Apollo….
    Know just enough to be dangerous!

Tags for this Thread

Posting Permissions

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