![]() |
|
|
|
#1
|
|||
|
|||
|
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 |
|
#2
|
|||
|
|||
|
SamD
Thanks very much for the input and resolving the same. SMehta Thread 3: No: 46521 : Post No16 : TM 27 |
|
|
|
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 |