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
