I have a form which is getting values in a list box from a range on a sheet
called Customer. THe length of that list can vary depending on other
selections, it may be from A5 to A500 or A5 to A120. How can I set up the
rowsource to automatically adjust tothe correct range. (it will always start
in Cell A5


thanks

try something like this:

Private Sub UserForm_Initialize()
Dim ws As Worksheet
Dim lastrow As Long

Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row

With Me.ListBox1
.RowSource = ws.Range("A5:A" & lastrow).Address
End With
End Sub


--


Gary

"Nigel" wrote in message
...
I have a form which is getting values in a list box from a range on a sheet
called Customer. THe length of that list can vary depending on other
selections, it may be from A5 to A500 or A5 to A120. How can I set up the
rowsource to automatically adjust tothe correct range. (it will always start
in Cell A5


thanks

PERHAPS

you could do the dynamic part on the list name.

instertnameDefine Place this formula on the source
=offset( A5,0,0,conta(A:A),1)

A5 = your starting Cell
conta(A:A) = number of rows that have values "remenber to not have any
empty spaces on this column"
1= width "you could do a conta here as well"





"Gary Keramidas" wrote:

try something like this:

Private Sub UserForm_Initialize()
Dim ws As Worksheet
Dim lastrow As Long

Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row

With Me.ListBox1
.RowSource = ws.Range("A5:A" & lastrow).Address
End With
End Sub


--


Gary

"Nigel" wrote in message
...
I have a form which is getting values in a list box from a range on a sheet
called Customer. THe length of that list can vary depending on other
selections, it may be from A5 to A500 or A5 to A120. How can I set up the
rowsource to automatically adjust tothe correct range. (it will always start
in Cell A5


thanks