Combo Box fill with multiple items associated to the same search term

[mharper90]

ws1 has Names in column B, and Serial Numbers in column C. Some names are duplicated due to a single person being associated with multiple serial numbers.

I have a user form that uses the selected cell to populate TextBox1 upon activation. I want ComboBox1 to list all of the Serial Numbers associated with the name in TextBox1 (which is still the selected cell value, as well).

Additionally, the selected cell with a name in it must be chosen prior to starting the macro which shows the user form. How can I have an error pop up if the selected cell upon starting the user form does not contain a value from ws1.Range("B7:B" & Lr)?

Also, because TextBox1 is populated with the selected cell value, I want it to appear in normal black text (I already tried some things that caused it to gray out), but I don't want the user to be able to click inside the text box or edit the value.

Lastly, how do I also prevent a user typed entry into ComboBox1? I only want the automatically generated Serial Numbers to be possible values. Thanks!

 

[My Aswer Is This]

Put this script in your Userform

Private Sub UserForm_Initialize()
'Modified  12/30/2018  10:31:27 PM  EST
Dim ans As String
Dim x As Long
x = 0
ans = ActiveCell.Value
Dim r As Range
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row
   For Each r In Range("B1:B" & Lastrow)
    If r.Value = ans Then
     ComboBox1.AddItem r.Offset(, 1).Value
     x = x + 1
    End If
   Next
TextBox1.Value = ans
TextBox1.Enabled = False
If x = 0 Then MsgBox "The name  " & ans & "  Not found"

End Sub