Snasui Excel Forum

Code: Select all

Option Explicit
Dim f As Boolean

Private Sub ComboBox1_Change()
    Dim rAll As Range
    Dim rt As Range
    With Sheets("Name")
        Set rAll = .Range("B2", .Range("B" & Rows.Count).End(xlUp))
        If Application.CountIf(rAll, ComboBox1.Text) > 0 And f = True Then
            Sheets("Name").Range("E2") = ComboBox1.Text
            Sheets("Form").Range("B2") = ComboBox1.Text
            ComboBox1 = ""
            Me.Hide
            Exit Sub
        End If
        .Range("E2") = ComboBox1.Text
        Sheets("Name").Range("C:C").ClearContents
        rAll.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Range( _
            "F1:F2"), CopyToRange:=.Range("C2"), Unique:=False
        Set rt = .Range("C3", .Range("C" & Rows.Count).End(xlUp))
    End With
    ComboBox1.RowSource = "Name!" & rt.Address
    ComboBox1.DropDown
End Sub

Private Sub CommandButton1_Click()
    f = True
    Sheets("Name").Range("E2") = ComboBox1.Text
    Sheets("Form").Range("B2") = ComboBox1.Text
    ComboBox1 = ""
    Me.Hide
End Sub

Private Sub UserForm_Initialize()
    f = False
    Me.ComboBox1.RowSource = ("Name!B3:B50")
End Sub