Adding multiple values to a combo box based off of a value in another cell

[Ah5522]

Hello

I have a userform dumping data into an excel table. I'd like for a user to be able to select their name and have a list of their records populate in a record ID combo box.

I wrote the following code that seems to almost be doing what I need, except that it is pulling all of the record ID's in the table not just the ones that match the cboStaff.value ?

Private Sub cboStaff_Change()
Dim x As Range

Set rng = Worksheets("CorporateCoordination").Range("TableCorpCoord[Staff]")

Set x = Worksheets("CorporateCoordination").Range("TableCorpCoord[Staff]").Find(What:=cboStaff.Value, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=True)

On Error GoTo 0

For Each x In rng
With cboRecordId
.AddItem x.Offset(, 1).Value
End With
Next x
End Sub


Could someone help me understand where I have misstepped here? The items are names so do I need to be declaring something as a string to help in the find operation? The userform is the only way staff can enter their names so I don't think its a MatchCase issue..

Thanks

 

[Fluff]

How about

Option Explicit
Private dic As Object

Private Sub cboStaff_click()
   cboRecordId.Clear
   cboRecordId.List = dic(cboStaff.Value).keys
End Sub

Private Sub UserForm_Initialize()

   Dim v1 As String, v2 As String, v3 As String
   Dim Cl As Range
   Set dic = CreateObject("scripting.dictionary")
   dic.CompareMode = vbTextCompare

   With Sheets("CorporateCoordination")
      For Each Cl In .Range("TableCorpCoord[Staff]")
         v1 = Cl.Value: v2 = Cl.Offset(, 1).Value
         If Not dic.exists(v1) Then
            dic.Add v1, CreateObject("scripting.dictionary")
            dic(v1).Add v2, CreateObject("scripting.dictionary")
         ElseIf Not dic(v1).exists(v2) Then
            dic(v1).Add v2, CreateObject("scripting.dictionary")
         End If
      Next Cl
   End With
   cboStaff.List = dic.keys
End Sub

 

[Ah5522]

Hey,

Thanks for the reply! Very close, I have not explored creating dictionaries before, looks to be a useful tool I'll have to do some more reading on it. The code you have provided is functioning correctly, but I've lost the rest of the staff list in cboStaff.

I failed to mention that on initializing I was pulling from a set staff list on a source data sheet:

with cboStaff
For i = 3 To 14
.AddItem Worksheets("MacroData").Range("G" & i).Value

I have the form set up so any staff member can submit a new record in a new row within the table. What I'd like to be able to do is have the user select their name, and then be able to view a list of their recordID's, if the user has no records they will have no options within cboRecordID.

Thanks again for your help!

When I first was creating the tool my coworker failed to mention that that they also wanted the ability to retrieve and edit an existing record, so I'm trying to retrofit my code to do so. I realize an Access Database would likely be a better tool but a lot of the fields and relationships are in flux still. Once we have a better idea of how the tool will be used I may suggest we switch applications.

 

[Fluff]

Ok, how about

Private Sub UserForm_Initialize()

   Dim v1 As String, v2 As String, v3 As String
   Dim Cl As Range
   Set dic = CreateObject("scripting.dictionary")
   dic.CompareMode = vbTextCompare

   With Sheets("MacroData")
      For Each Cl In .Range("G3", .Range("G" & Rows.count).End(xlUp))
         If Not dic.exists(Cl.Value) Then dic.Add Cl.Value, CreateObject("scripting.dictionary")
      Next Cl
   End With
   
   With Sheets("CorporateCoordination")
      For Each Cl In .Range("TableCorpCoord[Staff]")
         v1 = Cl.Value: v2 = Cl.Offset(, 1).Value
         If Not dic(v1).exists(v2) Then
            dic(v1).Add v2, Nothing
         End If
      Next Cl
   End With
   cboStaff.List = dic.keys
End Sub

 

[Ah5522]

Thank you - this works perfectly now. I realize that I did not allow for the staff source list to be a dynamic range, I knew that it was an oversight and had plans to go back in and tweak things later - so thank you for your assistance again. I'll do some more reading on some of the methods used here - I've got a couple of more forms to do similar things to so I'll use this as a framework moving forward and hopefully learn from the experience.

Thanks again very much for your help!

 

[Fluff]

Glad to help & thanks for the feedback.

For more info on dictionaries have a look here https://excelmacromastery.com/vba-dictionary/

 

[Ah5522]

Glad to help & thanks for the feedback.

For more info on dictionaries have a look here https://excelmacromastery.com/vba-dictionary/

One follow up question... for some reason I can only get the code to run once, if I try and launce the form a second time after its executed once successfully I get an error "Object Required".

If I remove the Sub Userform Initialize () code you provided the form then loads fine. Do I need to remove the dictionary when the userform unloads each time?

 

[Ah5522]

I figured it out - I forgot to copy over option explicit and missed declaring an object

 

[Fluff]

Glad you got it sorted & thanks for the feedback

 

[Ah5522]

Hi again,

I seem to have spoken to soon - after running correctly once again after inserting the code in an older version of my worksheet it errors out again on the second run:

When stepping through the code I get the object error on:
If Not dic(v1).exists(v2) Then