Microsoft Office Forums - View Single Post - Search Para No(s). thru Autofilter Combobox

SamDsouza · 02-27-2021, 04:22 AM

Hello SMehta

Few Observations made after downloading your file and went through the same

First of all you cant remove completely the below code as you mention in your post #12 of this thread.

Code:

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

Indeed above is really beautiful piece of code of Combobox1_ Keydown event to support Combobox1_change to trigger to reset items. Basically an Autofilter as you search by typing text as String or integer.

I've used in my excel templates many times. Really Hatsoff to MS-Excel MVP MikeRickson for the solution

Basically the combobox acts differently in VBA and may not have excellent features like in VB6, VB.net etc. I could be wrong here but Senior MVPs can throw light if they wish to.

But Yes one strong observation on Combobox1_change created by Andrew sir. Sir this does not allow feeling of autofilter Combobobox. Dropdown does not seem to function well as per post #7 or as per post #12. Tried and Tested for last few hours.

Code:

Private Sub ComboBox1_Change()
    Dim oneItem As Variant
    Dim FilteredItems() As String
    Dim NotFlag As Boolean
    Dim Pointer As Long, i As Long
    
    If DisableMyEvents Then Exit Sub
    If AbortOne Then AbortOne = False: Exit Sub
    If TypeName(FullList) Like "*()" Then
        ReDim FilteredItems(1 To UBound(FullList))
        DisableMyEvents = True
        Pointer = 0
        With Me.ComboBox1
            Select Case FilterStyle
                Case 2: .Tag = LCase(.Text) & "*"
                Case 0: .Tag = "*" & LCase(.Text) & "*"
                Case 1: .Tag = "*" & LCase(.Text) & "*": NotFlag = True
                Case 3: .Tag = "*" & LCase(.Text)
                Case "": .Tag = "*"
            End Select
            
            For Each oneItem In FullList
                If (LCase(oneItem) Like .Tag) Xor NotFlag Then
                    Pointer = Pointer + 1
                    FilteredItems(Pointer) = oneItem
                End If
            Next oneItem
            
            .List = FilteredItems
            .DropDown
        
        DisableMyEvents = False
            If Pointer = 1 Then .ListIndex = 0
        End With
    End If
End Sub

So ultimately i had to get on the logic of MikeRickson Ozgrid Solution but applicable to MS-word VBA for Autofilter combobox1.Change event.
You may overwrite the below portion of the code as per your need and appropriately MSGBox has been incorporated as per your requirement

Code:

Option Explicit
Public FullList As Variant
Public IsArrow As Boolean
Public totCntParas As Long


Private Sub UserForm_Initialize()

Dim aPar As Paragraph, sList As String, i As Long, sText As String, prghCnt As Integer Dim resultArrayParalist() As String
Dim j As Long

  For i = 1 To ActiveDocument.Paragraphs.Count
  sText = Trim(Trim(ActiveDocument.Paragraphs(i).Range.Text))
    If Split(sText, vbCr)(0) <> "" Then
          sList = sList & i & ","
          FullList = Split(sList, ",")
    End If
  Next i
  
ComboBox1.List = FullList
End Sub

Private Sub ComboBox1_Change()

Dim i As Long, paraNofound As Boolean
Dim oBkMrk As Bookmark
Dim wdActDoc As Document
Set wdActDoc = ActiveDocument

With ComboBox1

If Not IsArrow Then .List = FullList 
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
   Call updateRecordsInTbx
End If

If .Text = "" Then txtParaDetails.Text = ""

paraNofound = False
totCntParas = wdActDoc.Paragraphs.Count

For i = 0 To .ListCount - 1
   If .List(i) = .Text Then
   paraNofound = True
   Exit For
   End If
Next i

If Not paraNofound Then
   If .Text <> "" And .Text <= totCntParas Then
   MsgBox .Text & " is Empty Para "
   txtParaDetails.Text = ""
   Else
   MsgBox .Text & " Para No. Does Not Exists "
   txtParaDetails.Text = ""
End If
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 ComboBox1.List = FullList

End Sub

Public Sub updateRecordsInTbx()

Dim oBkMrk As Bookmark
Dim wdActDoc As Document
Set wdActDoc = ActiveDocument
   
  txtParaDetails.Text = ""
  txtParaDetails.Text = txtParaDetails.Text & wdActDoc.Paragraphs(ComboBox1.Text).Range & vbCrLf

End Sub

Wish Andrew Sir, will come up with new solution all together. so that Combobx shall appear like Autofilter. This will be learning aspect for me too.

SamD