calling excel xldown function properly

Coleman34

Member
Joined
Mar 10, 2015
Messages
5
Programming Experience
1-3
I am updating some antiquated code for a VB6 upgrade. I am trying to find an elegant way to call the xldown function in the code below. I don't think what I see upgraded is the correct way to do it. I have tried to bold the line of code in question, if anyone can help.

VB.NET:
    Public Sub extendListFillRange(ByRef wb As Microsoft.Office.Interop.Excel.Workbook)


        On Error Resume Next
        Err.Clear()


        Dim ws As Microsoft.Office.Interop.Excel.Worksheet
        ws = wb.Worksheets(comboboxSheetName)


        Dim cb As Microsoft.Office.Interop.Excel.OLEObject
        cb = ws.OLEObjects(comboboxName)


        If (Err.Number = 0) Then 'combobox found, so continue


            'get refernece to Listfill Range
            Dim listFillRangeSheetName As String
            listFillRangeSheetName = GetSheetNameFromAddress(cb.ListFillRange, comboboxSheetName)
            ws = wb.Workshets(listFillRangeSheetName)


            'get current combobox Listfill Range
            Dim rangeAddress As String
            rangeAddress = GetSimpleAddress(cb.ListFillRange)


            Dim rng As Microsoft.Office.Interop.Excel.Range
            rng = ws.Range(rangeAddress)


            'find last cell in range's first column


[FONT=arial black]            Dim lastCell As Microsoft.Office.Interop.Excel.Range[/FONT]
[FONT=arial black]            lastCell = rng.Cells(1, 1).end(-4121) 'xlDown --in this case A1 give the upper left cell in the present range not A1 absolutely[/FONT]




            'extend range (or contract it, as the case may be)
            rng = ws.Range(rng.Rows(1), lastCell)


            'update combobox


            Dim fullAddress As String
            fullAddress = GetFullAddressFromRange(rng)


            If (cb.ListFillRange <> fullAddress) Then
                'note: the cjec is necessasry beacuse if you try to set listFilLRange to something equivalent, excel crashes (Excel 2007 n Windows 7 enviroment)
                cb.ListFillRange = fullAddress
            End If


            cb.object.ListIndex = 0 ' reset combo box to first item
            'note: for some unknown reason, you need to use the "object" property ti access ListIndex but not ListFillRange
        End If


        On Error GoTo 0


    End Sub
 

JohnH

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,604
Location
Norway
Programming Experience
10+
Top Bottom