#1
|
|||
|
|||
Search Para No(s). thru Autofilter Combobox
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 SMehta Thread 3: No: 46521 : Post No1 : TM 19 |
#2
|
||||
|
||||
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?
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#3
|
|||
|
|||
Quote:
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 Thread 3: No: 46521 : Post No3 : TM 20 |
#4
|
||||
|
||||
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?
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#5
|
|||
|
|||
Quote:
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:
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:
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 |
#6
|
|||
|
|||
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 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 |
#7
|
||||
|
||||
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
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#8
|
|||
|
|||
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 |
#9
|
||||
|
||||
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.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#10
|
|||
|
|||
Sorry
Following Variables are not defined xlBeginsWith xlContains xlDoesNotContain xlEndsWith xlNoFilter Quote:
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 |
#11
|
||||
|
||||
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.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#12
|
|||
|
|||
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 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 SMehta Thread 3: No: 46521 : Post No12 : TM 25 |
#13
|
||||
|
||||
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.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#14
|
|||
|
|||
Sir I've attached the file.
SMehta Thread 3: No: 46521 : Post No14 : TM 26 |
#15
|
|||
|
|||
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 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 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 SamD |
|
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 |