Snasui Excel Forum

Code: Select all

=--ISNUMBER(IFERROR(SEARCH($F$2,B2,1),""))

Code: Select all

=IF(G2=1,COUNTIF($G$2:G2,1),"")

Code: Select all

=IFERROR(INDEX($B$2:$B$7538,MATCH(ROWS($H$2:H2),$H$2:$H$7538,0)),"")

Code: Select all

Private Sub ComboBox1_GotFocus()
    ComboBox1.ListFillRange = "DropDownList"
    Me.ComboBox1.DropDown
End Sub

Code: Select all

Private Sub ComboBox1_Change()
Dim a
    a = Filter(Application.Transpose(Sheets("Province").[B2:B7538]), ComboBox1.Value)
    If UBound(a) > 0 Then
        Sheets("Province").[I2:I7538] = Application.Transpose(a)
        ComboBox1.ListFillRange = "DropDownList"
    Else
        ComboBox1.ListFillRange = ""
    End If
            Me.ComboBox1.DropDown
End Sub

Code: Select all

Private Sub ComboBox1_Change()
Dim i As Long
    With Me.ComboBox1
        If Not IsArrow Then .List = Range("customerlist").Value 'ติดบรรทัดนี้ครับ
        If .ListIndex = -1 And Len(.Text) Then
            For i = .ListCount - 1 To 0 Step -1
                If InStr(1, .List(i), .Text, 1) = 0 Then .RemoveItem i
            Next i
            .DropDown
        End If
    End With
End Sub

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    IsArrow = KeyCode = vbKeyUp Or KeyCode = vbKeyDown
    If KeyCode = vbKeyReturn Then Me.ComboBox1.List = Range("customerlist").Value
End Sub

Code: Select all

Dim custList As Range
Dim IsArrow As Boolean

Private Sub ComboBox1_Change()
    Dim i As Long
    With Sheets("Customers")
        Set custList = .Range("d3", .Range("d" & .Rows.Count).End(xlUp))
    End With
    If Not IsArrow Then
        With Me.ComboBox1
            .List = custList.Value
            .ListRows = Application.WorksheetFunction.Min(custList.Count, .ListCount)
            .DropDown
            If Len(.Text) Then
                For i = .ListCount - 1 To 0 Step -1
                    If InStr(1, .List(i), .Text, 1) = 0 Then .RemoveItem i
                Next i
                .DropDown
            End If
        End With
    End If
End Sub

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
    ByVal Shift As Integer)
    IsArrow = (KeyCode = vbKeyUp) Or (KeyCode = vbKeyDown)
    If KeyCode = vbKeyReturn Then Me.ComboBox1.List = custList.Value
End Sub