Results 1 to 13 of 13

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

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    May 2021
    Posts
    172

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

    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

    Code:
    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

    Code:
    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
    Last edited by SamDsouza; Jun 5th, 2021 at 06:56 AM.

  2. #2
    Hyperactive Member Frabulator's Avatar
    Join Date
    Jan 2015
    Location
    USA
    Posts
    393

    Re: Refining AutoComplete Search Combobox from VBA Excel to VB.Net 19 with 2 question

    What I would do is one of two things, both of which involves a multi column datatable.

    Code:
    Dim dt As New System.Data.DataTable
    
    '---------------------------------
    dt.Columns.Add("ComboBox", GetType(String))
    dt.Columns.Add("Cell", GetType(String))



    A) I would go through a loop and add in all values from the Excel Document into the datatable, instead of recalling them constantly with Excel running in the background. If I need to dump the data back into Excel, open the document again and then dump the data. This will also clear the document up in case other people are going to be using it.
    Code:
    dt.Rows.Add(Nothing, Nothing) 'replace nothing with your values
    In my thoughts, the datatable would populate column 1 (your combobox data) and column 2 (your textbox data). Once it is all loaded you can loop through it and add all the values to the combobox. When the combobox value is selected it will update the textbox with the second column data.

    Code:
    'for column one
    
    For i As Integer = 0 To dt.Rows.Count - 1
            With ComboBox1.Items
                .Clear()
                .Add(dt.Rows(i).Item(0))
            End With
            ComboBox1.SelectedIndex = 0
    Next
    Put this into your SelectionChangeEvent for the combobox

    Code:
    Textbox1.Text = dt.Rows(Combobox1.SelectedIndex).Item(1)
    B) Load the values on demand. I would only do this if you feel like your data in the the textbox will need to change or be updated more frequent than A would allow.
    Using the same method above you create a loop to add in two columns into a datatable. The first column will be your combobox data and the second will be the row associated with that data (textbox data). When a combobox item is selected, open Excel and pull data from that row and use that for your textbox value.
    Last edited by Frabulator; Jun 6th, 2021 at 07:28 AM.
    Oops, There it goes. Yep... my brain stopped...
    _________________________________

    Useful Things:

    How to link your VB.Net application to Excel

  3. #3
    Hyperactive Member Frabulator's Avatar
    Join Date
    Jan 2015
    Location
    USA
    Posts
    393

    Re: Refining AutoComplete Search Combobox from VBA Excel to VB.Net 19 with 2 question

    And for your auto complete, it has been well documented here: Vb.net ComboBox Autocomplete. It is more or less some settings that you enabled
    Oops, There it goes. Yep... my brain stopped...
    _________________________________

    Useful Things:

    How to link your VB.Net application to Excel

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    May 2021
    Posts
    172

    Re: Refining AutoComplete Search Combobox from VBA Excel to VB.Net 19 with 2 question

    Frabulator Really appreaciate your suggestions for Question1.

    Although you have suggested the logic but i am unable to understand.
    The syntaxes which you have shown where can i write them is it in Form_Load or any other sub-routine or whatever you have suggested Is there any other forum where it almost matches your logic. This has almost gone over my brains.
    Can you show me a concrete example of what you are mentioning and deriving the desired results.


    What prior syntaxes and Excel Workhseet references etc are required before
    Code:
    Dim dt As New System.Data.DataTable
    
    '---------------------------------
    dt.Columns.Add("ComboBox", GetType(String))
    dt.Columns.Add("Cell", GetType(String))
    https://www.dotnetperls.com/datatable-vbnet
    The above link shows almost the same thing as you mentioned but not how it relates combobox but only adding data to Datatable.

    If i get clarity for above then i can ask for below
    In my thoughts, the datatable would populate column 1 (your combobox data) and column 2 (your textbox data). Once it is all loaded you can loop through it and add all the values to the combobox. When the combobox value is selected it will update the textbox with the second column data.

    Code:
    'for column one

    For i As Integer = 0 To dt.Rows.Count - 1
    With ComboBox1.Items
    .Clear()
    .Add(dt.Rows(i).Item(0))
    End With
    ComboBox1.SelectedIndex = 0
    Next
    Thanks for suggestin the Link for Question2
    SamD
    Thread 2: 892141 :
    12
    Last edited by SamDsouza; Jun 7th, 2021 at 04:01 AM.

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    May 2021
    Posts
    172

    Re: Refining AutoComplete Search Combobox from VBA Excel to VB.Net 19 with 2 question

    Hi

    I tried as is from https://social.msdn.microsoft.com/Fo...orum=vbgeneral

    Code:
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            For i = 0 To GetDataFromExcelByCom(False).Rows.Count - 1
                ComboBox1.Items.Add(GetDataFromExcelByCom(False).Rows(i)(0))
            Next
            For i = 0 To GetDataFromExcelByCom(False).Rows.Count - 1
                ComboBox2.Items.Add(GetDataFromExcelByCom(False).Rows(i)(1))
            Next
        End Sub

    I get error on below line as System.NullReferenceException: 'Object variable or With block variable not set.'
    This exception was originally thrown at this call stack:

    For i = 0 To GetDataFromExcelByCom(False).Rows.Count - 1

    SamD
    Thread 2: 892141 :
    13

  6. #6
    Hyperactive Member Frabulator's Avatar
    Join Date
    Jan 2015
    Location
    USA
    Posts
    393

    Re: Refining AutoComplete Search Combobox from VBA Excel to VB.Net 19 with 2 question

    Quote Originally Posted by SamDsouza View Post
    Frabulator Really appreaciate your suggestions for Question1.

    Although you have suggested the logic but i am unable to understand.
    The syntaxes which you have shown where can i write them is it in Form_Load or any other sub-routine or whatever you have suggested Is there any other forum where it almost matches your logic. This has almost gone over my brains.
    Can you show me a concrete example of what you are mentioning and deriving the desired results.
    I admit, it is awesome that you have taken the insensitive to actually look up this information before commenting back (most people just assume someone on here will do the leg work for them). So thank you!

    As far as getting the code in the exact place, I can try to guide you, but my setup might not be 'exactly' the same as yours.

    ------

    Connecting to Excel

    As I am sure that you have found out, you need to somehow connect to Excel. There are several methods of doing this: using the pre-built references with Visual Studio Professional ($$$), unpackaging the excel document and reading it as an XML file, or using an interop. I prefer the interop method because A) its free and B) while it can be a pain to setup to work, once it is set up, it works brilliantly.

    From the code provided above, it looks like you have the interop already set up.

    Code:
    Imports Microsoft.Office.Interop.Excel
    Imports Microsoft.Office.Interop
    It is also important to know that the year of release for the interop will work for that version of excel onward. Interop Version 12 will work for Excel v12 (ie: Excel 2007). This version will work for 2007-2019/365 desktop. This version is also limited to what features were present in excel 2007. Interop Version 14 (ie: Excel 2010) will not work with Excel 2007, but will work with 2010-2019/365 desktop... and so on. Excel 2007 was the first version that came with the ribbon/toolbars that we have today and access to the expanded VBA editor. ALMOST everyone is using 2007+. I myself am still running 2010 on one machine and 2016 on my work station. This is something that you want to keep in mind when selecting your interop version.

    Here is a link to all versions of Excel and their version numbers.

    Next, you want to specify WHAT the excel app is, WHERE it is and HOW you are going to modify/use it.

    This code is similar to the code you are using, but I am copying this from my own application, so the declared names may be different.

    This can be declared as a public outside of the main sub or inside of a sub that pulls data. It is up to you.

    Code:
            Dim xlApp As Microsoft.Office.Interop.Excel.Application 'tells your program what the Excel app is
            Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook 'tells your program what a workbook is
            Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet 'tells your program what a sheet is
            Dim xlChart As Microsoft.Office.Interop.Excel.Chart 'OPTIONAL- tells your program what a chart is
            Dim chartPage As Excel.Chart 'OPTIONAL- tells your program what a chart is
            Dim xlCharts As Excel.ChartObjects 'OPTIONAL- tells your program what chart objects are
            Dim theChart As Excel.ChartObject 'OPTIONAL- tells your program what a chart object is

    To start gathering or creating data you need to either start a new workbook:

    Code:
            xlApp = New Microsoft.Office.Interop.Excel.Application
            xlApp.Application.DisplayAlerts = False 'this is optional in case you do not want to show warnings about opening up a document
            xlWorkBook = xlApp.Workbooks.Add()
            xlWorkSheet = CType(xlWorkBook.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet)
    or open an existing one:

    Code:
            Dim SheetNamePlease as String = "Sheet 1"
            xlApp = New Microsoft.Office.Interop.Excel.Application
            xlApp.Application.DisplayAlerts = False 'this is optional in case you do not want to show warnings about opening up a document
            xlWorkBook = xlApp.Open(strFilename)
            xlWorkSheet = CType(xlWorkBook.Worksheets(SheetNamePlease), Microsoft.Office.Interop.Excel.Worksheet)
    If you would like to make your excel document run in the background and not show the user the Excel window, add the following codes after opening or creating a new document:

    Code:
            'make sure it is not visible
            xlApp.Visible = False
            xlApp.ScreenUpdating = False
            xlApp.DisplayAlerts = False
    To pull your data from Excel you are going to need to specify two things. A string for gathering the data, and a range from excel as the source. These can be accomplished as such:

    Code:
    Dim rng As Microsoft.Office.Interop.Excel.Range
    Dim str As String = "My Value"

    To put data into a range in the Excel Document you need to first decide where the range you want to dump the data is at, and you need to decide what the data you are dumping is. In this example I am using the 'str' as the data dump and the cell 'E2' as the range in the per-determained Excel document.

    Code:
                Dim RowStart as Integer = 1 'you can use this in a loop to go through all cells in the column if needed
                rng = xlWorkSheet.Range("E" & RowStart)
                rng.Value = str
                ReleaseCOM(rng)

    To pull data from the Excel Document you do the same thing, except you are using a string to hold the data from the range. An example of this could be:

    Code:
                Dim RowStart as Integer = 1 'you can use this in a loop to go through all cells in the column if needed
                rng = xlWorkSheet.Range("E" & RowStart)
                str = Cstr(rng.Value) 'this should always be a string, but just in case something got a little screwy, we are going to convert to a string
                ReleaseCOM(rng)

    Once you are finished gathering and dumping data, if you want to show the Excel document, you can issue the following commands:



    Code:
            xlApp.ScreenUpdating = True
            xlApp.Visible = True
            xlApp.DisplayAlerts = True
            xlApp.WindowState = XlWindowState.xlMaximized
    If you are finished 100% and wish to close out of Excel, use the following codes:

    Code:
            Try
                xlWorkBook.Close()
            Catch ex As Exception
    
             End Try
    
            releaseObject(xlWorkSheet)
            ReleaseCOM(xlWorkSheet)
            releaseObject(xlWorkBook)
            ReleaseCOM(xlWorkBook)
            releaseObject(xlApp)
            ReleaseCOM(xlApp)

    You may have noticed in my example the 'ReleaseCOM()' and the 'releaseObject()' calls in my code. I found these little snipbits online many years ago and have adopted them into all of my Excel linked applications. These codes disconnects all of your references from Excel, Workbooks, Apps, Ranges, etc., in one simple solution. I would recommend you dumping these somewhere in your main class of your application and reference them when needed.

    Code:
    #Region "Excel Remover"
        Public Shared Sub ReleaseCOM(ByVal COMObj As Object, Optional ByVal GCCollect As Boolean = False)
            Try
                If COMObj IsNot Nothing Then
                    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(COMObj)
                End If
            Finally
                COMObj = Nothing
                If GCCollect Then
                    GC.WaitForPendingFinalizers()
                    GC.Collect()
                End If
            End Try
        End Sub
    
    
    
    
    
        Private Sub releaseObject(ByVal obj As Object)
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            Catch ex As Exception
                obj = Nothing
            Finally
                GC.Collect()
            End Try
        End Sub
    
    
    
    #End Region


    ------------------


    Looping your data


    As I have previously mentioned, you can loop through your Excel document to pull your data and populate it into a datatable.

    Please note that this is an example and you will need to modify all of this code to fit your needs.

    In your form_load sub (you can access this by double clicking in Visual Studio on the top portion of your form in display mode), you will want to run a sub class that pulls the data from excel. An example would be like this:


    Code:
    Imports System
    Imports System.IO
    Imports System.Data
    Imports Microsoft.Office.Interop
    Imports Microsoft.Office.Interop.Access
    
    
    Public Class Form1
    
        Public dt As New System.Data.DataTable
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            PullExcel()
        End Sub
    
        Sub PullExcel()
            'your code for excel
        End Sub
    
    End Class
    It is a good idea to section these request off in separate subs just in case you ever need to run the sub again. It will prevent you from coping and pasting code (never a good practice).

    Populating the datatable is easy enough to do, but you need the data first. You will need to locate/open the Excel Document and run the data through a loop.

    Code:
        Sub PullExcel()
            dt.Clear() 'clears it out so we can start fresh
    
            dt.Columns.Add("ComboBox", GetType(String)) 'adds an item for combobox
            dt.Columns.Add("Cell", GetType(String)) 'adds an item for listbox
    
    
            Dim xlApp As Microsoft.Office.Interop.Excel.Application 'tells your program what the Excel app is
            Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook 'tells your program what a workbook is
            Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet 'tells your program what a sheet is
            Dim xlChart As Microsoft.Office.Interop.Excel.Chart 'OPTIONAL- tells your program what a chart is
            Dim chartPage As Excel.Chart 'OPTIONAL- tells your program what a chart is
            Dim xlCharts As Excel.ChartObjects 'OPTIONAL- tells your program what chart objects are
            Dim theChart As Excel.ChartObject 'OPTIONAL- tells your program what a chart object is
    
            Dim strFilename As String = "C:\File.xlsx"
    
            Dim SheetNamePlease as String = "Sheet 1"
            xlApp = New Microsoft.Office.Interop.Excel.Application
            xlApp.Application.DisplayAlerts = False 'this is optional in case you do not want to show warnings about opening up a document
            xlWorkBook = xlApp.Open(strFilename)
            xlWorkSheet = CType(xlWorkBook.Worksheets(SheetNamePlease), Microsoft.Office.Interop.Excel.Worksheet)
    
            Dim rng As Microsoft.Office.Interop.Excel.Range
            Dim str1 As String = "My ComboBox"
            Dim str2 As String = "My TextBox"
    
    
            For i As Long = 1 To 99999999 'doubt you have that many cells, but this will end when it reaches its first empty cell
                    Try
    
                        rng = xlWorkSheet.Range("E" & i)
                        str = Cstr(rng.Value)
                        If str = "" Then Exit For 'this closes out of the loop so we dont add empty points
                        
                        'now we are going to do this again with str2 so we have a valid data to put into the textbox
                        ReleaseCOM(rng)
    
                        rng = xlWorkSheet.Range("F" & i)
                        str2 = Cstr(rng.Value)
                        ReleaseCOM(rng)
    
                        dt.Rows.Add(str1, str2) 'this adds in the data we gathered
    
                        
    
                    Catch ex As Exception
                        Exit For
                    End Try
    
             Next
    
    
            Try
                xlWorkBook.Close()
            Catch ex As Exception
    
             End Try
    
            releaseObject(xlWorkSheet)
            ReleaseCOM(xlWorkSheet)
            releaseObject(xlWorkBook)
            ReleaseCOM(xlWorkBook)
            releaseObject(xlApp)
            ReleaseCOM(xlApp)
    
        End Sub
    Once that is done we can make a call that will add the datatable values to the combobox. This is easily done like so:

    Code:
        Sub AddToCombobox()
            With ComboBox1.Items
                .Clear()
    
                For i = 0 To dt.Rows.Count - 1
                    .Add(dt.Rows(i).Item(0))
                Next
            End With
    
        End Sub

    The ability to automatically update the textbox when a selection is made on the combobox can be activated by either double clicking on the combobox in the designer form, or going to the properties tab when the combobox is selected. The sub should be "SelectedIndexChanged":

    Code:
        Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
            Textbox1.Text = dt.Rows(Combobox1.SelectedIndex).Item(1)
            Textbox1.Update 'Updates the textbox
        End Sub


    ------


    When all that is put together it should look something like this: [untested]



    Code:
    Imports System
    Imports System.IO
    Imports System.Data
    Imports Microsoft.Office.Interop
    Imports Microsoft.Office.Interop.Access
    
    
    Public Class Form1
    
        Public dt As New System.Data.DataTable
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            PullExcel()
            AddToCombobox()
            Me.Update
        End Sub
    
    
        Sub PullExcel()
            dt.Clear() 'clears it out so we can start fresh
    
            dt.Columns.Add("ComboBox", GetType(String)) 'adds an item for combobox
            dt.Columns.Add("Cell", GetType(String)) 'adds an item for listbox
    
    
            Dim xlApp As Microsoft.Office.Interop.Excel.Application 'tells your program what the Excel app is
            Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook 'tells your program what a workbook is
            Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet 'tells your program what a sheet is
            Dim xlChart As Microsoft.Office.Interop.Excel.Chart 'OPTIONAL- tells your program what a chart is
            Dim chartPage As Excel.Chart 'OPTIONAL- tells your program what a chart is
            Dim xlCharts As Excel.ChartObjects 'OPTIONAL- tells your program what chart objects are
            Dim theChart As Excel.ChartObject 'OPTIONAL- tells your program what a chart object is
    
            Dim strFilename As String = "C:\File.xlsx"
    
            Dim SheetNamePlease as String = "Sheet 1"
            xlApp = New Microsoft.Office.Interop.Excel.Application
            xlApp.Application.DisplayAlerts = False 'this is optional in case you do not want to show warnings about opening up a document
            xlWorkBook = xlApp.Open(strFilename)
            xlWorkSheet = CType(xlWorkBook.Worksheets(SheetNamePlease), Microsoft.Office.Interop.Excel.Worksheet)
    
            Dim rng As Microsoft.Office.Interop.Excel.Range
            Dim str1 As String = "My ComboBox"
            Dim str2 As String = "My TextBox"
    
    
    
            For i As Long = 1 To 99999999 'doubt you have that many cells, but this will end when it reaches its first empty cell
                    Try
    
                        rng = xlWorkSheet.Range("E" & i)
                        str = Cstr(rng.Value)
                        If str = "" Then Exit For 'this closes out of the loop so we dont add empty points
                        
                        'now we are going to do this again with str2 so we have a valid data to put into the textbox
                        ReleaseCOM(rng)
    
                        rng = xlWorkSheet.Range("F" & i)
                        str2 = Cstr(rng.Value)
                        ReleaseCOM(rng)
    
                        dt.Rows.Add(str1, str2) 'this adds in the data we gathered
    
                        
    
                    Catch ex As Exception
                        Exit For
                    End Try
    
             Next
    
            Try
                xlWorkBook.Close()
            Catch ex As Exception
    
             End Try
    
            releaseObject(xlWorkSheet)
            ReleaseCOM(xlWorkSheet)
            releaseObject(xlWorkBook)
            ReleaseCOM(xlWorkBook)
            releaseObject(xlApp)
            ReleaseCOM(xlApp)
    
        End Sub
    
    
        Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
            Textbox1.Text = dt.Rows(Combobox1.SelectedIndex).Item(1)
            Textbox1.Update 'Updates the textbox
        End Sub
    
        Sub AddToCombobox()
            With ComboBox1.Items
                .Clear()
    
                For i = 0 To dt.Rows.Count - 1
                    .Add(dt.Rows(i).Item(0))
                Next
            End With
    
        End Sub
    
    
    
    #Region "Excel Remover"
        Public Shared Sub ReleaseCOM(ByVal COMObj As Object, Optional ByVal GCCollect As Boolean = False)
            Try
                If COMObj IsNot Nothing Then
                    System.Runtime.InteropServices.Marshal.FinalReleaseComObject(COMObj)
                End If
            Finally
                COMObj = Nothing
                If GCCollect Then
                    GC.WaitForPendingFinalizers()
                    GC.Collect()
                End If
            End Try
        End Sub
    
    
    
    
    
        Private Sub releaseObject(ByVal obj As Object)
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            Catch ex As Exception
                obj = Nothing
            Finally
                GC.Collect()
            End Try
        End Sub
    
    
    
    #End Region
    
    End Class
    I hope that wasnt too over the top, but your question(s) were a little indepth in response.
    Last edited by Frabulator; Jun 8th, 2021 at 08:22 AM. Reason: typos
    Oops, There it goes. Yep... my brain stopped...
    _________________________________

    Useful Things:

    How to link your VB.Net application to Excel

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    May 2021
    Posts
    172

    Re: Refining AutoComplete Search Combobox from VBA Excel to VB.Net 19 with 2 question

    Fabrulator
    Indeed Fantastic Informative presentation
    Really Dont know how to Thank you for the information you have provided.

    Let me work, check on the last part and will revert back
    Thanks

    SamD
    Thread 2: 892141 :
    14

  8. #8
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,042

    Re: Refining AutoComplete Search Combobox from VBA Excel to VB.Net 19 with 2 question

    Hi Sam,

    for filtering and searching Data I would use OLEDB, it's much less code and easy to adjust

    here a small sample, this is what the sheet looks like
    Name:  FilterSam.jpg
Views: 1159
Size:  18.5 KB



    and here the code for testing
    Code:
    Option Strict On
    
    
    Public Class Form1
    
    
    
        Public Function ExcelOleDb(ByVal strTextPath As String, _
        ByVal sSQL As String) As System.Data.DataTable
            Dim con As New System.Data.OleDb.OleDbConnection
            Dim myCmd As New System.Data.OleDb.OleDbCommand
            Dim myadp As New System.Data.OleDb.OleDbDataAdapter
            Dim mydt As New System.Data.DataTable
            With con
                .ConnectionString = "provider=microsoft.ACE.OLEDB.12.0;"
                .ConnectionString &= "data source=" & strTextPath & ";"
                .ConnectionString &= "Extended Properties = ""Excel 12.0 XML"";"
            End With
            With myCmd
                .Connection = con
                .CommandType = CommandType.Text
                .CommandText = sSQL
            End With
            With myadp
                .SelectCommand = myCmd
                Try
                    .Fill(mydt)
                Catch ex As Exception
                    MessageBox.Show(ex.Message)
                End Try
            End With
            Return (mydt)
        End Function
    
        Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
            'Sample.1) select all:
            DataGridView1.DataSource = ExcelOleDb("E:\TestFolder\excelFilter.xlsx", "SELECT * FROM [Sheet1$]")
            '###############################
            'Sample.2) select Range: A1:C4 and show in Datagridview
            'DataGridView1.DataSource = ExcelOleDb("E:\TestFolder\excelFilter.xlsx", "SELECT * FROM [Sheet1$A1:C4]")
            '################################
    
            'Fill the ComboBox with Distinct Product names
            ComboBox1.DataSource = ExcelOleDb("E:\TestFolder\excelFilter.xlsx", "SELECT Distinct Product FROM [Sheet1$A:A]")
            ComboBox1.DisplayMember = "Product"
    
    
        End Sub
    
        Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
            'Sample.3) Filter Data with Combobox and show in Datagridview
            DataGridView1.DataSource = ExcelOleDb("E:\TestFolder\excelFilter.xlsx", "SELECT * From [Sheet1$] Where Product Like '" & ComboBox1.Text & "'")
        End Sub
    
    
    End Class
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    May 2021
    Posts
    172

    Re: Refining AutoComplete Search Combobox from VBA Excel to VB.Net 19 with 2 question

    Frabulator

    Part 1 of the Question has been resolved and as mentioned i've worked on the last part of your coding and made changes as per my requirements.
    One Importing thing which i observed was Let us not Dim XlApp because when typing xlApp in the progamming editor of .Net19
    the following words (not familar with technical terms) are typed
    XlApplicationInternational
    XlApplyNamesOrder
    So the above is Important Note for Excel Lovers from VB.Net 19 to Excel for the Members of this forum and Non-members who browse for solutions.
    So here is the Final refinement of the Code
    Code:
    Imports System
    Imports System.IO
    Imports System.Data
    Imports Microsoft.Office.Interop
    Imports Microsoft.Office.Interop.Excel
    
    Public Class ufComboBoxes4
    
        Public dt As New System.Data.DataTable
    
        Private Sub ufComboBoxes4_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            PullExcel()
            AddToCombobox()
            Me.Update()
        End Sub
    
        Sub PullExcel()
            dt.Clear() 'clears it out so we can start fresh
    
            dt.Columns.Add("ComboBox", GetType(String)) 'adds an item for combobox
            dt.Columns.Add("Cell", GetType(String)) 'adds an item for listbox
    
    
             Dim myExcel As New Microsoft.Office.Interop.Excel.Application()
             Dim xlWrkBks As Excel.Workbooks = Nothing
             Dim xlWorkbook As Excel.Workbook = Nothing
             Dim xlWorksheet As Worksheet = Nothing
             Dim xlWorksheets As Worksheets = Nothing
    
            myExcel = New Microsoft.Office.Interop.Excel.Application
            Dim strFilename As String =  "C:\File.xlsx"
             xlWorkbook = myExcel.Workbooks.Open(strFilename)
            Dim SheetNamePlease As String = "Sheet 1"
    
            xlWorkSheet = CType(xlWorkBook.Worksheets(SheetNamePlease), Microsoft.Office.Interop.Excel.Worksheet)
    
            Dim rng As Microsoft.Office.Interop.Excel.Range
            Dim str1 As String = "My ComboBox"
            Dim str2 As String = "My TextBox"
    
            Str1 = CStr(rng.Value) 'this should always be a string, but just in case something got a little screwy, we are going to convert to a string
            ReleaseCOM(rng)
    
            For i As Long = 1 To 99999999 'doubt you have that many cells, but this will end when it reaches its first empty cell
                Try
    
                    rng = xlWorkSheet.Range("B" & i)
                    str1 = CStr(rng.Value)
                    If str1() = "" Then Exit For
                    'now we are going to do this again with str2 so we have a valid data to put into the textbox
                    ReleaseCOM(rng)
    
                    rng = xlWorkSheet.Range("C" & i)
                    str2 = CStr(rng.Value)
                    ReleaseCOM(rng)
    
    
                    dt.Rows.Add(str1, str2) 'this adds in the data we gathered
    
    
                Catch ex As Exception
                    Exit For
                End Try
    
            Next
    
            Try
                xlWorkBook.Close()
            Catch ex As Exception
    
            End Try
    
            releaseObject(xlWorkSheet)
            ReleaseCOM(xlWorkSheet)
            releaseObject(xlWorkBook)
            ReleaseCOM(xlWorkBook)
            releaseObject(xlApp)
            ReleaseCOM(xlApp)
    
        End Sub
    
        Sub AddToCombobox()
            With ComboBox1.Items
                .Clear()
    
                For i = 0 To dt.Rows.Count - 1
                    .Add(dt.Rows(i).Item(0))
                Next
            End With
    
        End Sub
    
        Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
            Textbox1.Text = dt.Rows(Combobox1.SelectedIndex).Item(1)
            Textbox1.Update 'Updates the textbox
          End Sub
    
        '#Region "Excel Remover"
        Public Shared Sub ReleaseCOM(ByVal COMObj As Object, Optional ByVal GCCollect As Boolean = False)
            Try
             If COMObj IsNot Nothing Then
             System.Runtime.InteropServices.Marshal.FinalReleaseComObject(COMObj)
             End If
             Finally
             COMObj = Nothing
            If GCCollect Then
             GC.WaitForPendingFinalizers()
             GC.Collect()
            End If
            End Try
        End Sub
    
        Private Sub releaseObject(ByVal obj As Object)
             Try
             System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
             obj = Nothing
             Catch ex As Exception
             obj = Nothing
             Finally
            GC.Collect()
            End Try
        End Sub
    End Class
    Indeed without your informative guidance i would have gone crazy and would have been still struggling.
    Your Valuable inputs were truly helpful to me to achieve the desired result. Thanks a Tonne.

    ChrisE
    I will try yours give me sometime to revertback.

    SamD
    Thread 2: 892141 :
    15

  10. #10

    Thread Starter
    Addicted Member
    Join Date
    May 2021
    Posts
    172

    Re: Refining AutoComplete Search Combobox from VBA Excel to VB.Net 19 with 2 question

    Fabrulator I forgot to ask What is GC
    in
    Code:
    If GCCollect Then
             GC.WaitForPendingFinalizers()
             GC.Collect()
    And how about adding one more textbox and getting respective data of another column related to selected item of combobox

    SamD
    Thread 2: 892141 :
    16
    Last edited by SamDsouza; Jun 8th, 2021 at 02:06 AM.

  11. #11
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: Refining AutoComplete Search Combobox from VBA Excel to VB.Net 19 with 2 question

    Quote Originally Posted by SamDsouza View Post
    Fabrulator I forgot to ask What is GC
    in
    Code:
    If GCCollect Then
             GC.WaitForPendingFinalizers()
             GC.Collect()
    SamD
    Thread 2: 892141 :
    16
    GC = Garbage Collector
    You don't need to worry what that code does. Just use it as provided and it'll work. I'll tell you though, it's about disposing used objects and freeing up memory.

  12. #12

    Thread Starter
    Addicted Member
    Join Date
    May 2021
    Posts
    172

    Re: Refining AutoComplete Search Combobox from VBA Excel to VB.Net 19 with 2 question

    .Paul.
    GC = Garbage Collector
    You don't need to worry what that code does. Just use it as provided and it'll work. I'll tell you though, it's about disposing used objects and freeing up memory.
    OK
    One should know
    Thanks for the information

    Pl ignore the below as in #10 as i have resolved the same too
    And how about adding one more textbox and getting respective data of another column related to selected item of combobox
    Thanks
    SamD
    Thread 2: 892141 :
    17

  13. #13
    Hyperactive Member Frabulator's Avatar
    Join Date
    Jan 2015
    Location
    USA
    Posts
    393

    Re: Refining AutoComplete Search Combobox from VBA Excel to VB.Net 19 with 2 question

    Quote Originally Posted by ChrisE View Post
    Hi Sam,

    for filtering and searching Data I would use OLEDB, it's much less code and easy to adjust

    here a small sample, this is what the sheet looks like
    Name:  FilterSam.jpg
Views: 1159
Size:  18.5 KB



    and here the code for testing
    Thank you! I will have to look into this for myself


    Quote Originally Posted by .paul. View Post
    GC = Garbage Collector
    You don't need to worry what that code does. Just use it as provided and it'll work. I'll tell you though, it's about disposing used objects and freeing up memory.
    Thank you for responding back in my absence !



    Quote Originally Posted by SamDsouza View Post
    .Paul.

    OK
    One should know
    Thanks for the information
    If your questions are resolved could you mark the thread as resolved? This can be done in the top right corner of the first post in the dropdown menus.

    This helps people who are looking for the same solution know that the issue was resolved.

    It also helps to rate the post that helped.

    Thanks! Good luck with your program
    Oops, There it goes. Yep... my brain stopped...
    _________________________________

    Useful Things:

    How to link your VB.Net application to Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width