..range("a2").end(xldown)

is the equivalent of selecting A2, then hitting the End key and then the down
arrow.

If you only have data in A2 (or nothing in A2 all the way down to A65536), then
that .end(xldown) goes all the way to the bottom of the worksheet.

So you either have to make sure A2 and A3 (at a minimum) are populated or add
some checks to your code. I also like to start at the bottom of the column
(A65536) and work my way up the column. (Like going to A65536, hitting End,
then up arrow--but this can suffer the same problem if there's nothing in A2.)

I guess the next question is what should happen if you don't have data in that
range.

This may give you some ideas:

Option Explicit
Private Sub Worksheet_Activate()

Dim ws As Worksheet
Dim ListBoxArray As Variant

Set ws = Worksheets("Member_list")

With ws
If IsEmpty(.Range("a2")) Then
'do nothing
ElseIf IsEmpty(.Range("a3")) Then
ListBoxArray = Array(.Range("a2").Value)
Else
ListBoxArray = .Range(.Range("A2"), .Range("A2").End(xlDown)).Value
End If
End With

If IsArray(ListBoxArray) = False Then
MsgBox "what happens here?"
Me.lstMembers.Clear
Else
Me.lstMembers.List = ListBoxArray
End If

End Sub




bach wrote:

Hi,

After some searching on the forum I found a article which was what I
was looking for.

WHAT AM I DOING

I have a sheet with data on it, I want to populate a list box with data
displayed on this sheet.

CODE CURRENTLY USING

Code:
--------------------

Dim ws As Worksheet
Set ws = Worksheets("Member_list")

With ws
Me.lstMembers.List = Application.Transpose(.Range(.Range("A2"), .Range("A2").End(xlDown)).Value)
End With

--------------------


PROBLEMS


- The list box populates with every row in the worksheet. I want the
list box to populate with only the data on the sheet so I guess I
need to do a check on available data first any idears ???

- The listbox is only using the first colum A2 but since I dont
really understand the code I not sure what to change. Could someone
explain and give possible solutions


Kind Regards

--
bach
------------------------------------------------------------------------
bach's Profile: http://www.excelforum.com/member.php...o&userid=26134
View this thread: http://www.excelforum.com/showthread...hreadid=468634

--

Dave Peterson