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

..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

Hi,

Thanks for your help, it was very usefull although I am still a little
lost over some of the code.

DUM

The line of code which sets the array

.range("A2"), .Range("A2")

What does this do why is the range in twice.

Also would it be possible to implement more than one colum. I would
like to implement a mixture of colums for the list box but they are not
next to each other.

Basically I would like there userid - A, surname - E, forename - F etc


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

I'm guessing you're asking about this line:

ListBoxArray = .Range(.Range("A2"), .Range("A2").End(xlDown)).Value

If you select A2, then hit the End key, followed by ctrl-shift-down arrow,
you'll be selecting A2 until your data has a gap in it (no gaps in the data
means that A2 through the last used cell of column A will be selected).

This is the equivalent in code.

And you can get more columns using something like:

Option Explicit
Private Sub Worksheet_Activate()

Dim ws As Worksheet
Dim ListBoxRng As Range
Dim myCell As Range

Set ws = Worksheets("Member_list")

Set ListBoxRng = Nothing

'don't forget to change this!
Me.lstMembers.ColumnCount = 3

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

If ListBoxRng Is Nothing Then
MsgBox "what happens here?"
Me.lstMembers.Clear
Else
With Me.lstMembers
.Clear
'don't forget to change this!
.ColumnCount = 3
.ListFillRange = ""
For Each myCell In ListBoxRng.Cells
.AddItem myCell.Value
.List(.ListCount - 1, 1) = myCell.Offset(0, 2).Value
.List(.ListCount - 1, 2) = myCell.Offset(0, 3).Value
Next myCell
End With
End If

End Sub

I used 3 columns: A, C, D
(.offset(0,2) is two to the right)
(.offset(0,3) is three to the right)

bach wrote:

Hi,

Thanks for your help, it was very usefull although I am still a little
lost over some of the code.

DUM

The line of code which sets the array

range("A2"), .Range("A2")

What does this do why is the range in twice.

Also would it be possible to implement more than one colum. I would
like to implement a mixture of colums for the list box but they are not
next to each other.

Basically I would like there userid - A, surname - E, forename - F etc

--
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

Hi,

Thanks for your help, I have got the following with errors lol


Code:
--------------------
Dim ws As Worksheet
Dim ListBoxArray As Variant

Set ws = Worksheets("Member_list")

With ws
If IsEmpty(.Range("A2")) Then
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
Me.lblTotalNo.Caption = "There are no members currently in the database."
Me.lstMembers.Clear
Else
With Me.lstMembers
.Clear
.ColumnCount = 3
.ListFillRange = ""
For Each myCell In ListBoxArray.Cells
.AddItem myCell.Value
.List(.ListCount - 1, 1) = myCell.Offset(0, 2).Value
.List(.ListCount - 1, 2) = myCell.Offset(0, 3).Value
Next myCell
End With
End If
--------------------


doesnt like .listfillrange and mycell any ideas.


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

The code changed. Try using the newer version.



bach wrote:

Hi,

Thanks for your help, I have got the following with errors lol

Code:
--------------------
Dim ws As Worksheet
Dim ListBoxArray As Variant

Set ws = Worksheets("Member_list")

With ws
If IsEmpty(.Range("A2")) Then
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
Me.lblTotalNo.Caption = "There are no members currently in the database."
Me.lstMembers.Clear
Else
With Me.lstMembers
.Clear
.ColumnCount = 3
.ListFillRange = ""
For Each myCell In ListBoxArray.Cells
.AddItem myCell.Value
.List(.ListCount - 1, 1) = myCell.Offset(0, 2).Value
.List(.ListCount - 1, 2) = myCell.Offset(0, 3).Value
Next myCell
End With
End If
--------------------


doesnt like .listfillrange and mycell any ideas.

--
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

Dave,

Thanks for your reply, I have used the code and i get a type
mismatch.

'pick it up from the list worksheet
ListType = CLng(mySelectedCell.Offset(0, 8).Value)


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

I have no idea what ListType was, so I declared it as long (an integer).

Since I didn't know what it was, I just took the value from that cell and tried
to make sure it was a nice number.

You never shared what ListType was and how it was derived--so I just guessed.

bach wrote:

Dave,

Thanks for your reply, I have used the code and i get a type
mismatch.

'pick it up from the list worksheet
ListType = CLng(mySelectedCell.Offset(0, 8).Value)

--
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

Dave,

It was my attempt, to create the form. As I am not sure what I need to
do for it I have made up some code playing around, so the variable might
not be needed.

I need the double click event to display all the data for the select
user in the frmmember userform. There are a number of fields, which
need to be populated.

Bach


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

You can pick out the data from the line you double clicked by using lines like:

'or pick it up from the member_list worksheet
ListType = CLng(mySelectedCell.Offset(0, 8).Value)



bach wrote:

Dave,

It was my attempt, to create the form. As I am not sure what I need to
do for it I have made up some code playing around, so the variable might
not be needed.

I need the double click event to display all the data for the select
user in the frmmember userform. There are a number of fields, which
need to be populated.

Bach

--
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

Dave,

Could you tell me what that line of code is doing, what is the offset
for.

What should the listtype variable be set to, it is currently set to
string for data but I get an error message of type mismatch.

Bach

I have played with the number is the offset setting the column number?


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