Userform Dependent List Boxes

[kadrl]

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.

[Ahavelind]

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

[SamT]

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).

[Ahavelind]

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

[SamT]

so far

[Ahavelind]

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

[SamT]

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)

[Ahavelind]

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

[SamT]

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.

[Ahavelind]

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.