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

Ah5522

New Member
Joined
Jun 17, 2014
Messages
22
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
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
How about
Code:
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
 
Upvote 0
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.
 
Upvote 0
Ok, how about
Code:
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
 
Upvote 0
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!
 
Upvote 0
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?
 
Upvote 0
Glad you got it sorted & thanks for the feedback
 
Last edited:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top