I have written code for a similar situation. I don't know how elegant
this is... but here's how I solved this problem. I created a new
worksheet to which is hidden from the user. I assume that you are
getting the data from and external DB so once the query has been
returned with the data, the data is then listed within the hidden
worksheet line by line. All you do then is look for the first blank
line to set your new range. The code that then pulls the data would
look something like this.
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim VRange As Range
Dim Cell As Range
Dim ir As Integer
Set VRange = Sheet1.Cells(2, 15)
For Each Cell In Target
If Union(Target, VRange).Address = VRange.Address Then
' Refresh the table
Sheet2.Range("A1").QueryTable.Refresh
For ir = 2 To 2000
If Sheet2.Cells(ir, 6).Value = "" Then
sheet1.listbox1.listfillrange = "'Sheet2'!$A$1:$" &
chr((ir-1)+64) & "$6"
End If
Next ir
end if
next cell
*** Sent via Developersdex
http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!