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

 

[unityjon]

I'm no expert but from trying to solve my own problem with combo boxes i have learnt that you can have multi column combos and which ever you select in the first will influence the rest so create your range across multiple columns then add that number of columns to the "column count" variable in the controls for the combo box, i cant give you any code as i didnt read any more than that but "multiple column combo boxes" search should get you the results you need.

 

[littleguy1810]

Thanks Unityjon,

Im looking to do this within a userform, if someone could help please

Cheers

Mark

 

[littleguy1810]

Bump

 

[littleguy1810]

bump

 

[littleguy1810]

bump

 

[AFPathfinder]

What control are you using for the user to select from the 5 options and what control are you using to populate after a choice is made?

You'll want to fill the second control on the first control's click event. The only code you are listing is the userform_initialize event.

 

[littleguy1810]

Hi AFPathfinder,

This is the whole code in the userform, there is no other code. The userform opens up and the user then enters data from dropdown boxes. The partID dropdown box is controlled by a list, which is hundreds long and it takes to long for the user to identify the part, each part is assigned a category in the list if I could get the user to input the category then the Partid dropdown box would then show only those parts which are in that category, I could do this in the lists but I want to be able to control this by the use of the userform and not have the user mess around the worksheet with the lists. I hope I have explained this well enough.

Please can you help?

Option Explicit

Private Sub cboPart_Change()

End Sub

Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
Dim ws As Worksheet
Set ws = Worksheets("PartsData")

'find  first empty row in database
''lRow = ws.Cells(Rows.Count, 1) _
''  .End(xlUp).Offset(1, 0).Row

'revised code to avoid problems with
'Excel lists and tables in newer versions
lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
    
lPart = Me.cboPart.ListIndex

'check for a part number
If Trim(Me.cboPart.Value) = "" Then
  Me.cboPart.SetFocus
  MsgBox "Please enter a part number"
  Exit Sub
End If

'copy the data to the database
With ws
  .Cells(lRow, 1).Value = Me.txtDate.Value
  .Cells(lRow, 2).Value = Me.cboPart.Value
  .Cells(lRow, 3).Value = Me.cboPart.List(lPart, 1)
  .Cells(lRow, 4).Value = Me.cboCategory.Value
  .Cells(lRow, 5).Value = Me.cboLocation.Value
  .Cells(lRow, 6).Value = Me.txtQty.Value
  .Cells(lRow, 7).Value = Me.cboEmployee.Value
End With

'clear the data
Me.cboEmployee.Value = ""
Me.cboPart.Value = ""
Me.cboLocation.Value = ""
Me.cboCategory.Value = ""
Me.txtDate.Value = Format(Date, "Medium Date")
Me.txtQty.Value = ""
Me.cboPart.SetFocus

End Sub

Private Sub cmdClose_Click()
  Unload Me
End Sub

Private Sub ComboBox1_Change()

End Sub

Private Sub Label6_Click()

End Sub

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]

So cboCategory is the drop-down box with the 5 options? You'll want a "cboCategory Change" event I believe if that is the case (Private Sub cboCategory_Change()).

When cboCategory value changes, you want an If statement to check if it's not blank ("") first. Next, clear the partID combobox in case the user changes category. Then, have a loop that loops through the table and adds all the part IDs to the partID combobox that match the correct category.

 

[littleguy1810]

Hi AFPathfinder,

Thanks for the explanation, I'm not good with VBA (newly started), how would I do that. sorry to be a pain, but it sounds complicated.

Cheers

Mark