0

I am trying to create a VBA that allows autocomplete when typing in a data validation cell. I have obtained code from the following question What VBA event allows to capture click value of ActiveX combobox?.

Problem is that when I use the below code from that question and have it run off my name range, which is a list of 200 paragraphs that it is searching within, it crashes me out of excel immediately and I am not sure why. Is there an issue with the code or is searching within 200 paragraphs in 200 separate cells just not viable with VBA?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim xCombox As OLEObject
    Dim xStr As String
    Dim xWs As Worksheet
    Dim xArr
    Set xWs = Application.ActiveSheet
    On Error Resume Next
    Set xCombox = xWs.OLEObjects("TempCombo")
    With xCombox
        .ListFillRange = ""
        .LinkedCell = ""
        .Visible = False
    End With
    If Target.Validation.Type = 3 Then
        Target.Validation.InCellDropdown = False
        'Cancel = True
        xStr = Target.Validation.Formula1
        xStr = Right(xStr, Len(xStr) - 1)
        If xStr = "" Then Exit Sub
        With xCombox
            .Visible = True
            .Left = Target.Left
            .Top = Target.Top
            .Width = Target.Width + 5
            .Height = Target.Height + 5
            .ListFillRange = xStr
            If .ListFillRange = "" Then
                xArr = Split(xStr, Application.International(xlListSeparator))
                Me.TempCombo.List = xArr
            End If
            .LinkedCell = Target.Address
        End With
        xCombox.Activate
        Me.TempCombo.DropDown
    End If
End Sub

Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Select Case KeyCode
        Case 9 'tab
            Application.ActiveCell.Offset(0, 1).Activate
        Case 13 'enter
            Application.ActiveCell.Offset(1, 0).Activate
    End Select
End Sub

Private Sub TempCombo_Change()
If Me.TempCombo = "" Then Exit Sub
ActiveSheet.OLEObjects(1).ListFillRange = ""
ActiveSheet.OLEObjects("TempCombo").Object.Clear
ThisWorkbook.ActiveSheet.OLEObjects("TempCombo").Activate

With Me.TempCombo
    If Not .Visible Then Exit Sub
    .Visible = False 'to refresh the drop down
    .Visible = True
    .Activate

'Dump the range into a 2D array
        Dim Arr2D As Variant
        Arr2D = [QoE].Value

'Declare and resize the 1D array
        Dim Arr1D As Variant
        ReDim Arr1D(1 To UBound(Arr2D, 1))

'Convert 2D to 1D
        Dim i As Integer
        For i = 1 To UBound(Arr2D, 1)
            Arr1D(i) = Arr2D(i, 1)
        Next

    Dim itm As Variant 'itm is for iterate purpose
    Dim ShortItemList() As Variant 'ShortItemList() is a variable which stores only filtered items
    i = -1
    For Each itm In Arr1D
        If InStr(1, itm, .Value, vbTextCompare) > 0 Or .Value = "" Then
            Debug.Print itm
             i = i + 1
             ReDim Preserve ShortItemList(i)
             ShortItemList(i) = itm
        End If
    Next itm
    .DropDown
End With

On Error Resume Next 'if we filter too much, there will be no items on ShortItemList
ThisWorkbook.ActiveSheet.OLEObjects("TempCombo").Object.List = ShortItemList

End Sub
Arg0n
  • 1
  • 2
    remove `On Error Resume Next` and edit your question with the error it gives you. – Mech Jul 28 '20 at 19:19
  • I removed it but I am still just getting kicked out when I start typing in the data validation cell. No error shows, it just shuts down excel completely @Mech – Arg0n Jul 28 '20 at 19:32
  • go into your vba code, press F8 until you find the line that it crashes at. – Mech Jul 28 '20 at 19:35
  • The following lines all show as breaks in the code "Private Sub TempCombo_Change() If Me.TempCombo = "" Then Exit Sub" – Arg0n Jul 28 '20 at 19:39
  • is it crashing or is it exiting? – Mech Jul 28 '20 at 20:12
  • It closes the whole program without asking me whether I want to save or not so feels like a crash – Arg0n Jul 28 '20 at 20:26

1 Answers1

0

You don't use .DropDown. It crash Excel when ComboBox hide or delete

Vertinsky Gena
  • 11
  • 2
  • This does not provide an answer to the question. Once you have sufficient [reputation](https://stackoverflow.com/help/whats-reputation) you will be able to [comment on any post](https://stackoverflow.com/help/privileges/comment); instead, [provide answers that don't require clarification from the asker](https://meta.stackexchange.com/questions/214173/why-do-i-need-50-reputation-to-comment-what-can-i-do-instead). - [From Review](/review/late-answers/30703538) – L.Dutch Dec 31 '21 at 07:00