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]

Do you have Staff names in the table that do not appear in the MacroData sheet?

 

[Ah5522]

I may have accidentally added a space to one of the staff name cells in the table or something along those lines.

I cleared the contents and deleted the table rows existing and its running again. Thank you. I am going to lock the user interface once this is operational so the userforms will be the only way a user can interact with the data tables without unlocking the sheets, so this shouldn't happen again unless I'm editing and make a mistake.

Once again thank you for being able to answer the right questions to help me on my way!

 

[Fluff]

One way to help ensure that leading/trailing spaces don't cause a problem is

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([COLOR=#ff0000]trim[/COLOR](Cl.Value)) Then dic.Add [COLOR=#ff0000]trim[/COLOR](Cl.Value), CreateObject("scripting.dictionary")
      Next Cl
   End With
   
   With Sheets("CorporateCoordination")
      For Each Cl In .Range("TableCorpCoord[Staff]")
         v1 = [COLOR=#ff0000]trim[/COLOR](Cl.Value): v2 = [COLOR=#ff0000]trim[/COLOR](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