user form VBA
 

I have a user form with combo boxes that draw the list from anothe
workbook. I use this code.

Private Sub UserForm_Initialize()
Dim wb As Workbook
Set wb = Workbooks.Open("f:\db1.xls")
For Each cell In wb.Sheets("Cust").Range("a2:A500")
Me.cboCust.AddItem cell.Value
Next cell
For Each cell In wb.Sheets("Subdiv").Range("a2:A500")
Me.cboSub.AddItem cell.Value
Next cell
wb.Close False


My problem is the .Range, instead of going to row 500, is ther
something I could do to make the list end at the last row used
regardless how long the list will becomes

--
Message posted from http://www.ExcelForum.com

user form VBA
 

Private Sub UserForm_Initialize()
Dim wb As Workbook
Dim rng as Range
Set wb = Workbooks.Open("f:\db1.xls")
With wb.Sheets("Cust")
set rng = .Range(.Range("A2"), .Range("A2").End(xldown))
End with
Me.cboCust.List = rng
With wb.sheets("Subdiv")
set rng = .Range(.Range("A2"),.Range("A2").End(xldown))
End With
Me.cboSub.List = rng
wb.Close False
end Sub

--
Regards,
Tom Ogilv

--
Message posted from http://www.ExcelForum.com

user form VBA
 

Thanks for the answer, but I received an error.

"could not set the list property, Invalid property array index"

I'm gonna keep messing with it. Please post any other ideas

--
Message posted from http://www.ExcelForum.com

user form VBA
 

Hey Tom,

I found out the problem.

Me.cboCust.List = rng

should be

Me.cboCust.List = rng.value

guessing it has something to do with office 2003

Thanks alot for your help. I wouldn't have been able to figure it ou
on my own

--
Message posted from http://www.ExcelForum.com

user form VBA
 

I usually do use Value in that situation (although I have seen it wor
without it), but I somehow omitted it. My bad.

--
Regards,
Tom Ogilv

--
Message posted from http://www.ExcelForum.com