If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
UserForm Listbox issue
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 |
#2
|
|||
|
|||
..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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
Dave Peterson, Thanks for you help with this, I have copied the code and it does not like *.listfillrange*. I have commented this bit of the code out and it seems to populate the listbox. I will have to play with the code to ensure all the colums they want are included inside the list box. If I have any issues will post back. P.S -Would you have an idea why it does not like *.listfillrange* and is it required.- -- bach ------------------------------------------------------------------------ bach's Profile: http://www.excelforum.com/member.php...o&userid=26134 View this thread: http://www.excelforum.com/showthread...hreadid=468634 |
#8
|
|||
|
|||
Maybe if you swap the order...
With Me.lstMembers .ListFillRange = "" .Clear But I was just trying to make sure you didn't assign a range address to the listbox. If you didn't do that, then this line of code isn't really necessary. bach wrote: Dave Peterson, Thanks for you help with this, I have copied the code and it does not like *.listfillrange*. I have commented this bit of the code out and it seems to populate the listbox. I will have to play with the code to ensure all the colums they want are included inside the list box. If I have any issues will post back. P.S -Would you have an idea why it does not like *.listfillrange* and is it required.- -- 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 |
#9
|
|||
|
|||
Hi, Thanks for your help the generattion of data inside a textbox seems fine. Now I need to write some code to handle a double click event and bring up another form with the details displayed any tips?? Bach -- bach ------------------------------------------------------------------------ bach's Profile: http://www.excelforum.com/member.php...o&userid=26134 View this thread: http://www.excelforum.com/showthread...hreadid=468634 |
#10
|
|||
|
|||
Use the lstMembers_DblClick event???
bach wrote: Hi, Thanks for your help the generattion of data inside a textbox seems fine. Now I need to write some code to handle a double click event and bring up another form with the details displayed any tips?? 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Listbox in Reports | Samantha | Setting Up & Running Reports | 2 | October 4th, 2005 03:24 AM |
Requerying a ListBox | [email protected] | General Discussion | 3 | September 2nd, 2005 12:55 PM |
MultiSelect ListBox Code posted J. Vinson - Help, please!! | MikeyKinAZ | Using Forms | 4 | February 19th, 2005 12:30 AM |
Add Item from one listbox to the next | alex pLEASE HELP | General Discussion | 0 | July 22nd, 2004 08:53 PM |
Listbox Troubles | Jimbo51 | Using Forms | 1 | June 11th, 2004 12:04 PM |