[RESOLVED] Refining AutoComplete Search Combobox from VBA Excel to VB.Net 19 with 2 questions.

[SamDsouza]

Hello

Can anyone help me to refine the AutoComplete Search Comobbox from VBA to VB.NET19
Following is the code in VBA-Excel for Searching particular item from a Range of Column and same being loaded in the Combobox2.

In VBA Excel Combobox as Object had many limited features and so smoothness was not seen while executing the same.
But under the circumstances Functionalbe in VBA

The same type of adaption but with some beautiful functionality i want to derive for a combobox ie data from column B of a worksheet in to Combbobox of .Net Userform and respective row data into respective textbox.

Following VBA Code uploads the Data from Col B of Worksheet 5 in Combobox2 of VBA userform
With ComboBox2_Click Event it displays the respective data of diffrent columns into respective Textboxes of that particular row

VBA Code
Option Explicit
Public FullList As Variant, lastRow As Long
Public IsArrow As Boolean

Private Sub UserForm_Initialize()

Dim Wks As Worksheet, myArray As Variant
Dim lastRow As Long
Set Wks = Worksheets("SHEET5")

lastRow = Worksheets("SHEET5").Cells(Rows.Count, 1).End(xlUp).Row

With Worksheets("SHEET5")
      .Activate
     myArray = .Range("A2:P" & lastRow).Value
    ComboBox3.List() = .Range(Cells(2, 1), Cells(lastRow, 1)).Value
    ComboBox2.List() = .Range(Cells(2, 2), Cells(lastRow, 2)).Value
  End With
End Sub

Private Sub ComboBox2_Change()
Dim recFound As Boolean, totRowsCount As Long
Dim i As Long

lastRow = Worksheets("SHEET5").Cells(Rows.Count, 1).End(xlUp).Row

With ComboBox2
    If Not IsArrow Then .List = Worksheets("SHEET5").Range("A2").CurrentRegion.Offset(1, 1).Value

        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 ComboBox2.ListCount = 0 Then
        MsgBox "Item Does Not Exisits"
     End If
  End With
End Sub

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

 FullList = Worksheets("SHEET5").Range("A2").CurrentRegion.Offset(1, 1).Value
 IsArrow = KeyCode = vbKeyUp Or KeyCode = vbKeyDown
 If KeyCode = vbKeyReturn Then ComboBox2.List = FullList

End Sub

Private Sub ComboBox2_Click()
 Dim idx As Long
    idx = ComboBox2.ListIndex
          If idx <> -1 Then
              ComboBox3.Text = Worksheets("SHEET5").Range("A" & idx + 2).Value
              textBox5.Text = Worksheets("SHEET5").Range("E" & idx + 2).Value
              textBox6.Text = Worksheets("SHEET5").Range("F" & idx + 2).Value
          End If
End Sub

Now What i desire is that if same thing can be adapted in much better method with smooth flow in VB.NET will always be grateful to you.
I tired the Following code in VB.NET19 adapted from https://www.codeproject.com/Articles...oBox-in-VB-Net

somehow succeded to get the Range of Column B values in ComboBox2 but missed to get respective data of different columns into respective Textboxes of that particular row in respective textboxes

Q1. How to get respective data from ComboBox when Selectedchange_event triggered or When Clicked on the item of combobox into respective textboxes from Worksheet

Q2. Any beautiful ways for searching items in Combobox and hiliting the same while being searched

VB.NET19 CODE
Option Strict On

Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop

Public Class ufComboBoxes
    Private Sub ufComboBoxes_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim path As String = "C:\"
        Dim strFilename As String = "C:\Trial\Data.xlsx"

        Dim appXL As New Microsoft.Office.Interop.Excel.Application() 
        Dim wbK As Excel.Workbook 
        Dim wksSht5 As Excel.Worksheet 

        Dim wksNo As Integer = 1
        Dim RngCombo2Cde As Excel.Range
        Dim Rngcombo2Arr As String

        wbK = appXL.Workbooks.Open(strFilename)
        wksSht5 = CType(appXL.Worksheets("SHEET5"), Excel.Worksheet)

        RngCombo2Cde = DirectCast(wksSht5.Range("B2:B25"), Excel.Range)

        For i As Integer = 1 To RngCombo2Cde.Rows.Count
            Dim colCell As Excel.Range = DirectCast(RngCombo2Cde.Rows(i), Excel.Range)
            Rngcombo2Arr &= DirectCast(colCell.Value.ToString, String) & ","
        Next
        Rngcombo2Arr = Rngcombo2Arr.Remove(Rngcombo2Arr.Length - 1, 1)
        ComboBox2.Items.AddRange(Rngcombo2Arr.Split(","c))

        RngCombo2Cde = Nothing
        wksSht5 = Nothing
        wbK = Nothing
        appXL = Nothing

    End Sub

    Public Sub AutoCompleteCombo_KeyUp(ByVal cbo As ComboBox, ByVal e As KeyEventArgs)
        Dim sTypedText As String
        Dim iFoundIndex As Integer
        Dim oFoundItem As Object
        Dim sFoundText As String
        Dim sAppendText As String

        'Allow select keys without Autocompleting
        Select Case e.KeyCode
            Case Keys.Back, Keys.Left, Keys.Right, Keys.Up, Keys.Delete, Keys.Down
                Return
        End Select

        'Get the Typed Text and Find it in the list
        sTypedText = cbo.Text
        iFoundIndex = cbo.FindString(sTypedText)

        'If we found the Typed Text in the list then Autocomplete
        If iFoundIndex >= 0 Then

            'Get the Item from the list (Return Type depends if Datasource was bound 
            ' or List Created)
            oFoundItem = cbo.Items(iFoundIndex)

            'Use the ListControl.GetItemText to resolve the Name in case the Combo 
            ' was Data bound
            sFoundText = cbo.GetItemText(oFoundItem)

            'Append then found text to the typed text to preserve case
            sAppendText = sFoundText.Substring(sTypedText.Length)
            cbo.Text = sTypedText & sAppendText
            'Select the Appended Text
            cbo.SelectionStart = sTypedText.Length
            cbo.SelectionLength = sAppendText.Length
        End If
    End Sub


    Private Sub ComboBox2_Leave(sender As Object, e As EventArgs) Handles ComboBox2.Leave
        Dim recRowView As DataRowView
        'Dim recName As DB.tblNameRow

        AutoCompleteCombo_Leave(ComboBox2)

        'OPTIONAL: Now you can  do some extra handling if you want

        'Get the Selected Record from my Data Bound Combo (Return Type is DataRowView)
        recRowView = ComboBox2.SelectedItem
        If recRowView Is Nothing Then Exit Sub

        'Display the Name Info (Row Type comes from my bound Dataset)
        'recName = recRowView.Row
        'lblAccountNum.Text = recName.AccountNum
        'lblCompanyName.Text = recName.CompanyName

''''''How to get following values in textbox5 and textbox6 with recRowView
              textBox5.Text = Worksheets("SHEET5").Range("E" & idx + 2).Value
              textBox6.Text = Worksheets("SHEET5").Range("F" & idx + 2).Value

        
    End Sub

    Private Sub ComboBox2_KeyUp(sender As Object, e As KeyEventArgs) Handles ComboBox2.KeyUp
        AutoCompleteCombo_KeyUp(ComboBox2, e)
    End Sub

    Public Sub AutoCompleteCombo_Leave(ByVal cbo As ComboBox)
        Dim iFoundIndex As Integer
        iFoundIndex = cbo.FindStringExact(cbo.Text)
        cbo.SelectedIndex = iFoundIndex
    End Sub

End Class

Thankx in advance
SamD
Thread 2: 892141 :
11