Context Sensitive Form extracting Data from Excel Worksheet

Using: Microsoft Visual Studio Community 2015, Version 14.0.24720.00 Update 1, with Microsoft .NET Framework Version 4.6.01055

Language: Visual Basic 2015
Target: Windows 7 application

Objective: Open Excel Worksheet from shared Box.com account, and get all of the data on the sheet (used range) and save into application. Populate ComboBoxes with columns from the excel data, filtering out duplicate data points from the dropdown lists. Auto complete for each ComboBox further filtering the list when user inputs text into the ComboBox. Selection in any ComboBox further filtering the list items in all other ComboBoxes on the form. Save button writes selected (or typed in the case of new data) from ComboBoxes to the first unused row of the Excel Worksheet (the same worksheet from above).

Completed: My code is currently opening the Worksheet and bringing the data into the application. I am bringing the Excel data into a 2-dimensional array. I have created event handlers and autocomplete for the ComboBoxes so they are all populating from the columns (from the array data copied from the Worksheet). My current code also writes data back to the Excel Worksheet directly from the values in the ComboBoxes.

The struggle I am having is with manipulating the Array Data after extracting it from the Excel Worksheet. I am unable to redimension and preserve a new array to collect each row of array data that matched a ComboBoxes selection.

The excel data is related to quote numbers. a Short example is:
a1: Quote Number, b1: Date/Time Stamp (when last saved), c1: Client, d1, Item 1 Cost... 73 more data points.

Each data point is populated into the comboboxes, and duplicates are filtered out. The user needs to be able to further filter the comboboxes by completing a selection in any of the comboboxes on the form. So if the user selects a client, then only the quote numbers in the same row (from the excel worksheet) would populate in the comboboxes. If the user then selects an Item from the Items combobox, the quote numbers would filter further (rebuild the list of items in the combobox). The end result is that the user would only need to know a couple data points to filter out all of the comboboxes to resolve at the only possible option. I would then like to turn all of the comboboxes background color a light shade of green to acknowledge to the user that there are no further options, and populate all of the selections of all remaining comboboxes with the only options remaining.

I do not know a lot about working with 2-dimensional dictionaries or lists(of lists). I have tried, but failed since almost all of the searches I have dont work explicitly with 1 dimensional data or dictionaries of dictionaries (1 dimensional populated by 1 dimensional). The problem I run into is how to get the excel data into that where I can then still work with the "Rows" and "Columns". In a 2-diminsional array its just a loop Array(i, 1).

Code for extracting the data from the Excel Worksheet into a global 2-Dimensional array (myData):

Code:

---

Sub myGetArray(ByVal myWB As Integer, mySheetNumber As Integer)
        Dim myUser As String = Environ("USERPROFILE")
        Dim myDBSubPath As String = "\Box Sync\Tech Writer Projects\Quote Interface\"
        Dim myDBFileName As String = "QuoteFakeDatabase.xlsx"
        Dim myPBFileName As String = "TestPB.xlsx"
        Dim myDBFullPath As String = Nothing

        If myWB = 1 Then
            myDBFullPath = myUser & myDBSubPath & myDBFileName
        ElseIf myWB = 2 Then
            myDBFullPath = myUser & myDBSubPath & myPBFileName
        End If

        Dim myExcel As Excel.Application = New Excel.Application
        Dim myWB1 As Excel.Workbook = myExcel.Workbooks.Open(myDBFullPath,, True)
        Dim mySheet As Excel.Worksheet = myWB1.Sheets(mySheetNumber)
        Dim myRange As Excel.Range = mySheet.UsedRange

        If myWB = 1 Then
            myData = myRange.Value(Excel.XlRangeValueDataType.xlRangeValueDefault)
        ElseIf myWB = 2 Then
            SSData = myRange.Value(Excel.XlRangeValueDataType.xlRangeValueDefault)
        End If
     
        myWB1.Close(False)
        myExcel.Quit()
        releaseObject(myExcel)
        releaseObject(myWB1)
        releaseObject(mySheet)

    End Sub

---

NOTE: this sub is called from my forms load event. It will also be called from a form reset button that is not currently included (and not part of the problem).

Code for populating the Comboboxes on the form from the myData array:

Code:

---

Sub myDropdownFill2(ByVal sender As Object, myWB As Integer)

        Dim myTempArray(,) = Nothing

        If myWB = 1 Then
            myTempArray = myData
        ElseIf myWB = 2 Then
            myTempArray = SSData
        End If

        Dim myLastRow As Integer = myTempArray.GetUpperBound(0) ' Last Row
        Dim myFirstRow As Integer = myTempArray.GetLowerBound(0) ' First Row
        Dim myLastColumn As Integer = myTempArray.GetUpperBound(1) ' Last Column
        Dim myFirstColumn As Integer = myTempArray.GetLowerBound(1) ' First Column
        Dim myCB As ComboBox = Nothing

        For myi As Integer = myFirstColumn To myLastColumn ' Loop through first to last column of the myData array
            Try
                If sender = "LOAD" Then ' If the Form Load called this sub
                    myCB = Controls.Find(myTempArray(1, myi), True)(0) ' Finds controls related to database row1
                Else
                    myCB = CType(sender, ComboBox) ' Otherwise, the control is the ControlExpanded event sender for ComboBoxes
                End If
                If Not myCB Is Nothing Then ' If the Control exists
                    For myj As Integer = myFirstRow To myLastRow ' Loop through each array element by row
                        If Not myTempArray(myj, myi) = Nothing Then ' and if the array element in not empty/nothing
                            If Not myCB.Items.Contains(myTempArray(myj, myi)) Then ' and if the array element does not already exist to the ComboBox items list
                                myCB.Items.Add(myTempArray(myj, myi)) ' Then add the array element to the ComboBox
                            End If
                        End If
                    Next myj
                End If
            Catch
            End Try
        Next myi
    End Sub

---

NOTE: not part of the problem, however, the myData and SSData are 2 different Excel Workbooks which I am reading data from into two different arrays.

NOTE 2: sender is the expand event of all comboboxes, and passes the switch for which excel workbook to open based on the comboboxes name value. The comboboxes are all named identically to their related excel column text (so excel cell A1.value and ComboBox1.name are identical.)

Current code for trying to get the matches from the selection in a combobox by transplanting data from myData array to TempArray, where comboboxes can be repopulated from the condensed list:

This is where I am struggling. Maybe my whole process needs to be reconsidered from the excel data extraction?

Code:

---

Sub myReadArrayandExtractRange()
        Dim bound0 As Integer = myData.GetUpperBound(0) ' Rows
        Dim bound1 As Integer = myData.GetUpperBound(1) ' Columns
        Dim mySender As String = QuoteNumber.Text ' name of combobox and excel a1 - Change later to event handler
        Dim myDBColumn As Integer = 1 ' excel column number - Change later to event handler pass
        Dim tempArrayRows As Integer = 0 ' Count of matches in loop k

        ' k loop counts sender matches in myData column X for dimensioning TempArray
        For k As Integer = 1 To bound0
            If myData(k, myDBColumn).ToString = mySender Then
                tempArrayRows = tempArrayRows + 1
            End If
        Next k

        'Dimension TempArray from matches
        Dim tempArray(tempArrayRows, bound1)

        ' i loop iterates each row in myData looking for a match to sender
        For i As Integer = 1 To bound0
            If myData(i, myDBColumn).ToString = mySender Then
                ' ji loop changes to each row in TempArray
                For ji = 1 To tempArrayRows
                    ' j loop moves across a row to each column of data and write the data to TempArray
                    For j As Integer = 1 To bound1
                        tempArray(ji, j) = myData(i, j)
                    Next j
                Next ji
            End If
        Next i
    End Sub

---

No one?

Anyone?