Select Dynamic list for rowsource on a list box
 

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

Select Dynamic list for rowsource on a list box
 

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

Select Dynamic list for rowsource on a list box
 

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