Search Para No(s). thru Autofilter Combobox

SMehta · #1 02-21-2021, 10:11 PM

Hello

I was trying to implement to search Para No(s). like Autofilter Combobox
via
Auto Filtering ComboBox - TIP, TRICKS & CODE - OzGrid Free Excel/VBA Help Forum
But somehow it is not Smooth operating. I dont know why

Because when i type the Para No as 1 inthe Combobox Change Event
then the dropdown should show list of
1, 10,11, 12,13,14,......21,31 etc
if i type 2 then result
2, 12, 22,32.....etc

The above results not happening. Dont know why

Code:

Option Explicit
Public myArrayParaList As Variant
Public IsArrow As Boolean

Public Sub GetParaList()
Dim  sList As String, i As Long, sText As String,  myArrayParaList As Variant

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

Private Sub cmbParaNos_Change()

Dim i As Long
Dim oBkMrk As Bookmark
Dim wdActDoc As Document
Set wdActDoc = ActiveDocument

With cmbParaNos

If Not IsArrow Then .List = myArrayParaList
If .ListIndex = -1 And Val(Len(.Text)) Then
    For i = .ListCount - 1 To 0 Step -1
        'If InStr(1, .List(i), .Text, 1) = 0 Then .RemoveItem i
        If InStr(1, .List(i), Val(.Text), 1) = 0 Then .RemoveItem i
    Next i
   .DropDown
End If

End With

End Sub

Private Sub cmbParaNos_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

IsArrow = KeyCode = vbKeyUp Or KeyCode = vbKeyDown
If KeyCode = vbKeyReturn Then cmbParaNos.List = myArrayParaList 
End Sub

Your guidance shall be appreciated.

SMehta
Thread 3: No: 46521 : Post No1 : TM 19

Guessed · #2 02-21-2021, 10:28 PM

If .ListIndex = -1 this means there is no selection
If there is no selection then .Text would be "" so Len(.Text) would be 0 and Val(0) would be zero

What do you think Val(Len(.Text)) would return as a boolean value?

SMehta · #3 02-22-2021, 12:05 AM

Quote:

If .ListIndex = -1 this means there is no selection
If there is no selection then .Text would be "" so Len(.Text) would be 0 and Val(0) would be zero

What do you think Val(Len(.Text)) would return as a boolean value?

Now I corrected as Below.
Not sure for a smooth operation because
Now What happens if i type for eg 2 and if that para is not there but 21, 32, 212 etc para are there. In this case how to implement smoothly with Msgbox "Para no 2 Does Not Exist"
and there is "Sky Blue" background behind the para no. digits. In this case also i had to select full Two digits or 3 digits with DarK Blue Colour for Complete para nos.
Why the selection cant operate automatically ?

Anything also can be worked out on the same for real smooth operation

Code:

Private Sub cmbParaNos_Change()

Dim i As Long
Dim oBkMrk As Bookmark
Dim wdActDoc As Document
Set wdActDoc = ActiveDocument

With cmbParaNos

If Not IsArrow Then .List = myArrayParaList
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

SMehta
Thread 3: No: 46521 : Post No3 : TM 20

Guessed · #4 02-22-2021, 02:13 AM

Can you explain the behaviour you want?

ComboBoxes are typically used by the user clicking in them, not by typing text in a random order and seeing the list reset its contents then using enter or arrow keys to type more numbers.

Edit: I've just had a look at the working Excel code that you referred to in your first post. I'm not seeing any value in stepping away from the actual code shown there. Why have you varied so much of the code in your implementation?

SMehta · #5 02-22-2021, 04:00 AM

Quote:

ComboBoxes are typically used by the user clicking in them, not by typing text in a random order and seeing the list reset its contents then using enter or arrow keys to type more numbers.

Ok,
As i have to Search Para No(s) only. After much exploaration, The Excel link as #1 had this result somehow managed to implement.
I Thought ComboBox = TextBox + DropDown List OR Listbox so by typing something in ComboBox its related values or the defined values will display.
As ComboBox is used to store and display list of items to a list.

So What i thought of : By retrieving a Para No(s). I would like to see the Text of that Para No(s) of the document in Textbox.

Quote:

Can you explain the behaviour you want?

What i Expect from Combbobox if i Type a complete Para.No like 2 it should get hi-lited and load the details of Para (2) in textbox
if Para no 2. Is not there. Then MSBOX TO APPEAR PARA NO 2 Does not Exists ?

But then the list resets its contents and other related nos are seen as per Excel Coding

So When i typed 2 the list displayed as 21, 121, 210 etc but skyblue colour was focussed on 1 of 21 and not fully focussed on 21. This inidcated that Para No. 2 is not there in the List.
As list reset it contents then 21, 121 210 etc were seen in the Combobox.

To see one of the effect
i took cursor to extreme left of 21 and i typed 1 to 21 which became 121. This got automatically hilited and details of Para 121 were seen in textbox.

Also by CLICKING (Mouse Arrow / KeyPad Arrow) on the other related Displayed list Para number. The details of Para got uploaded in textbox.

I've explained my expected behaviour against the Strange Default Behaviour of MS-Word Userform Combobox.

Quote:

Why have you varied so much of the code in your implementation?

Really Got confused. Sorry My mistake. Then I rectified as per excel coding. Also learnt about .ListIndex after your comment in #2

My Sincere request to you and other senior Forum Team members of this forum to kindly bear with me. As this is something new for me. Will take sometime to digest and implement.

SMehta
Thread 3: No: 46521 : Post No5 : TM 21

SMehta · #6 02-23-2021, 09:36 AM

Somehow managed but still not Suceesful

Code:

Private Sub cmbParaNos_Change()

Dim i As Long
Dim oBkMrk As Bookmark
Dim wdActDoc As Document
Set wdActDoc = ActiveDocument

With cmbParaNos

If Not IsArrow Then .List = myArrayParaList
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

 If .ListIndex > -1 Then
    Else
    MsgBox .Text & " Empty Para No(s)."   
End If

End With
End Sub

The above coding for correct Msgbox appearing is not sufficient enough to derive the 1st and 2nd Criteria Msg

As the List contains only Para.Nos with details
Msgbox Required here as per Criteria
1. Msg for Empty Para.nos which are not shown in the List
2. Msg for Non-Existing Para Nos.
For eg Total Para are 243 with Empty Paras
so 244, 245 etc are
if typed/Selected 244 then MSGBOX 244 "DOES NOT EXISTS"
if typed/Selected 245 then MSGBOX 245 "DOES NOT EXISTS"
if typed/Selected 242 then MSGBOX 242 "Empty Para No(s)."

SMehta
Thread 3: No: 46521 : Post No6 : TM 22

Guessed · #7 02-23-2021, 05:24 PM

You are still nowhere near the code that you referred to in your initial post

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 xlBeginsWith: .Tag = LCase(.Text) & "*"
                Case xlContains: .Tag = "*" & LCase(.Text) & "*"
                Case xlDoesNotContain: .Tag = "*" & LCase(.Text) & "*": NotFlag = True
                Case xlEndsWith: .Tag = "*" & LCase(.Text)
                Case xlNoFilter: .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

If you want help here and insist on blazing a new trail from one that obviously works, post a sample document that contains the userform and code you are having problems with. And tell us WHICH LINE of code is causing your issue.

SMehta · #8 02-23-2021, 07:09 PM

What have you DIM the following as

I suppose you have DIM for following variables
Dim DisableMyEvents As Boolean, AbortOne As Boolean

But what about
FullList
FilterStyle

As Errors generated

SMehta
Thread 3: No: 46521 : Post No8 : TM 23

Guessed · #9 02-23-2021, 07:29 PM

Refer to the link in your original post for those questions.

I would assume that FullList is an array
FilterStyle appears to be an integer

Both of these are dimensioned and populated outside of that particular subroutine but in your source Excel code.

SMehta · #10 02-23-2021, 08:28 PM

Sorry

Following Variables are not defined
xlBeginsWith
xlContains
xlDoesNotContain
xlEndsWith
xlNoFilter

Quote:

Both of these are dimensioned and populated outside of that particular subroutine but in your source Excel code.

Sir, I implemented the code in Word itself and not Excel although Initial code adopted from Ozgrid Excel Forum to work on Combobox
Are theere diffrent type of combobox for VBA MS-Word and VBA MS-Excel

And in my Initial Post #1 of this thread I mentioned ActiveDocument and Paragraphs.

SMehta
Thread 3: No: 46521 : Post No10 : TM 24

Guessed · #11 02-23-2021, 08:55 PM

Those constants are all defined in Excel. In Excel these resolve as
xlBeginsWith = 2
xlContains = 0
xlDoesNotContain = 1
xlEndsWith = 3
xlNoFilter = ""

The ComboBox in a VBA userform is essentially the same but when interacting with the application may cause you to use different methods to achieve the same thing. For instance a Range in Excel can be used to populate the .List property of a combobox. Word requires you to convert a Range into an array before you can populate the .List property.

SMehta · #12 02-23-2021, 09:28 PM

In order to avoid confussion. i Ve changed as per Word Requirements only the SELECT CASE part

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

Also Checked the following code Not At all Required

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

And Where to Apply appropriate Msgbox as post #6

SMehta
Thread 3: No: 46521 : Post No12 : TM 25

Guessed · #13 02-23-2021, 09:51 PM

You are asking questions that reveal this is a learning experience for you. The thing you are trying to achieve is not entry level and you are attempting something well beyond your current knowledge levels.

I can help you learn but I'm not prepared to create a working example in Word from scratch because it is apparent your knowledge has considerable gaps and I would need to explain more than I particularly care to do.

Provide a sample document with the code you already have implemented and specify EXACTLY which step you need help with.

SMehta · #14 02-23-2021, 10:16 PM

Sir I've attached the file.

SMehta
Thread 3: No: 46521 : Post No14 : TM 26

SamDsouza · #15 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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Trying to get number of para in Combobox	SMehta	Word VBA	2	02-20-2021 07:15 AM
How to autofilter by exact date?	LearnerExcel	Excel	5	06-19-2018 03:30 PM
Autofilter on two Date Columns	OTPM	Excel Programming	3	04-29-2014 12:56 AM
Sum on AutoFilter	Grasshopper	Excel	3	02-14-2013 01:41 AM
AutoFilter Criteria3	coxjamd	Excel Programming	2	01-17-2013 02:24 PM