Help with code for dropdown box

[littleguy1810]

Hi All,

I have this code running in a user form, I would like to be able to let the user select a category then, the PartIDList will choose from a list of items within that category only. you see the PartIDList list is hundreds long I want to shorten this to help the user. I can split the list into 5 columns easily and name the range, just need help with the code

The items fall into 5 categories,

1 - Flavour
2 - Oil
3 - Pellet
4 - Film
5 - Cases

Any suggestions?

Thanks

Mark

Private Sub UserForm_Initialize()
Dim cPart As Range
Dim cLoc As Range
Dim cEmp As Range
Dim cCat As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupLists")



For Each cEmp In ws.Range("Employee")
  With Me.cboEmployee
    .AddItem cEmp.Value
  End With
Next cEmp

For Each cCat In ws.Range("Category")
  With Me.cboCategory
    .AddItem cCat.Value
  End With
Next cCat

For Each cPart In ws.Range("PartIDList")
    With Me.cboPart
        .AddItem cPart.Value
        .List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value
    End With
Next cPart

For Each cLoc In ws.Range("LocationList")
  With Me.cboLocation
    .AddItem cLoc.Value
  End With
Next cLoc

Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtQty.Value = ""
Me.cboPart.SetFocus

End Sub

 

[AFPathfinder]

For simplicity, this is built assuming the category type is in column 2 of your table and the part ID is in column 1. You'll want to change that to your actual situation.

This code hasn't been tested and may need a few changes to make it work, but this is how I would write it.

Private Sub cboCategory_Change()
    'Populate partID combobox when user selects a category

    Dim i As Integer, x As Integer, finalRow As Integer
    
    'Exit sub if a category isn't selected
    If cboCategory.Value = "" Then
        Exit Sub
    End If

    'Clear partID combobox in case user changes category
    Do While partID.ListCount > 0
        partID.RemoveItem (0)
    Loop

    'Define the last row in the table for the For/Next loop
    finalRow = Cells(Rows.Count, 1).End(xlUp).Row

    'Loop through the table and add any part IDs that match the category
    For i = 2 To finalRow
        If Cells(i, 2).Value = cboCategory.Value Then
            partID.AddItem Cells(i, 1).Value
            x = x + 1
        End If
    Next i

End Sub

 

[littleguy1810]

Hi AFPathfinder,

Many thanks for your valuable help, I will test this out later today, I will post the results. Again thank you for taking the time

Mark

 

[littleguy1810]

Hi,

Just to let everyone I have been given the best help ever from AFPathfinder, great help thanks