PDA

View Full Version : VBA multi column listbox help



Stuck!
04-13-2006, 06:52 AM
Hi all,

I am currently trying to make a user form that has a list box inserted into it with 6 columns and a unspecified amount of rows (the rows will be come more as data is added). Unfortunatly i seemed to have fallen at the first hurdle and i cant even get the list box to show any columns let alone any data from excel.

I have been through the forums and found information requarding List boxes posted by Sheeng and others but i just dont understand the commands and what i actually need to put where. :banghead: :banghead: :banghead: :banghead:

any help would be really appreciated.

Bob Phillips
04-13-2006, 08:52 AM
First you need to set the ColumnCount property of the ListBox to a number greater than 1. Then you set the RowSource property to the worksheet range if referring to a worksheet. If loading manually, use something like


Dim ary, i, j
ary = [{"Bob","M", 123;"Lynne","F",898;"Amy","F",543}]

With ListBox1
For i = 1 To 3
.AddItem ary(i, 1)
For j = 2 To 3
.List(.ListCount - 1, j - 1) = ary(i, j)
Next j
Next i
End With

Norie
04-13-2006, 09:17 AM
Stuck

What do you want to populate the combobox with?

xld

Couldn't you just use List to populate from the array?


ary = [{"Bob","M", 123;"Lynne","F",898;"Amy","F",543}]

ListBox1.List = ary
By the way I never knew you could create arrays like that.:)

Stuck!
04-13-2006, 03:43 PM
@ xld

hi thanks for the quick reply, i havent been able to try that yet as uni is closed for the easter weekend now, but will give it ago first thing tuesday morning. I want to try and populate the listbox by using a worksheet, but when i type in the range such as "sheet1!A2:E" it comes up with an error and typing in "sheet1!A2:E2" doesnt give me all the information from those columns as i have only told it to look at row 2, what sort of coding is used to tell it to look at all the rows in those columns?

@ Norie

Hi thanks for replying Norie, What i am trying to do is make a userform that people can put information into which is then stored in excel, then the list box that i have put in the user form has to take that information from excel and display it in the correct columns so it becomes a searchable data base. The sort of information i am trying to put in the form is just text and numbers. I have managed to make the userform with the use of combo boxes and text boxes store information in the correct columns and rows in excel but i am just completly stumped on the listboxes. :(

Thank you both again for your help so far its is really appreciated.

Bob Phillips
04-13-2006, 04:24 PM
What you should do is define a dynamic range name. So assuming that your data starts in A2 tyo En, then add a name (Insert>Name>define...) of say myRange with a Refersto value of

=OFFSET($A$2,,,COUNTA($A:$A),5)

this assumes A1 is empty, if not use

=OFFSET($A$2,,,COUNTA($A:$A)-1,5)

then assign that range name in the RowSource property

=myRange

Stuck!
04-13-2006, 04:54 PM
Hi xld, thanks for replying again, i am some what very new to VBA and dont really see what you mean, i see what you mean by saying that i need to give a range a name but im not clear on the way to do it,

thanks for your time :thumb

lucas
04-13-2006, 05:41 PM
i havent been able to try that yet as uni is closed for the easter weekend now

Hello Stuck,
I don't know if this is a university or school assignment or not and it doesn't matter as long as you understand that no one here will do your work for you. They will give you advice and bits of code and answers to specific questions.

If I were you I would go to our knowledge base and search the excel entries for "listbox" The search window is here. (http://vbaexpress.com/kb/default.php?action=search) Just enter listbox and select the excel application then hit search.

Several of the listings show easy ways to do what your trying to do with the exception of the 6 columns but you can get answers for that after you get the basics of how to get your data from the spreadsheet to the listbox. Good luck and don't give up.

Stuck!
04-13-2006, 06:02 PM
Hi Lucas thanks for reply, This list box is part of my univeristy project but im not asking anyone to do it for me just to explane the meaning of the code and why/how it works. I have looked at the other posts to making a list box but the coding is to complicated for me to understand how it works and information explaining what differant bits do is very welcome

lucas
04-13-2006, 07:13 PM
Click here (http://vbaexpress.com/kb/getarticle.php?kb_id=600) and take a look at this entry. Its a simple listbox populated from a spreadsheet.

In the green part of the entry is the code(same as in example file).
at the bottom of the page is a link to an example file to download. Download the file, unzip it and run the excel file.
you can see the code by hitting Alt+F11
After you hit Alt+F11 you will enter the visual basic editor.
on the left side of the editor you can select the form or module to examine the code.
right click on the form and select "view code"
or double click on the module(module1)

There are basically just 3 parts to this if you don't include the data in the spreadsheet.

the code in module1 which calls the form
and the two private subs in the code for the form.
one (Private Sub UserForm_activate)populates the listbox on the form(this is the one you are most interested in) and the other highlights the data on the spreadsheet that you choose in the listbox and closes the form....thats all you get for now. Come back with some relevant questions and you will get help.

Stuck!
04-14-2006, 05:53 AM
Hi Lucas, thanks for the link it is very helpfull, but what does this actually do?
Dim MyList(10, 3) 'as array type
Dim R As Integer

or do you know of any sites that will explane all the common commands
and how to use the modules in simple terms, as this is what i really need to know

many thanks for your help so far

lucas
04-14-2006, 06:01 PM
The Dim statements are not commands. they are defining variables for use by commands later in the macro.
Dim MyList(10, 3) 'as array type is defining Mylist as an array
Dim R as Integer means that the variable R will be a number....

More important is what happens between the with statements.


With Listbox1
the lines below this statement will determine what the listbox will look like
With ListBox1
.ColumnCount = 3
.ColumnWidths = 75
.Width = 230
.Height = 110
.ControlTipText = "Click the Name, Job, or ID you're after"
End With


the next with statement will determine where mylist comes from on the worksheet. Be sure to read the comments in the code. This one is set up to only list 9 rows.....if you put data in the 10th row you will not see it in the listbox. Notice that R is equal to a variable number(integer) below.....For R = 0 To 10

With ActiveSheet
'MyList (Row{0 to 9}, Column{0 to 2}) = the ranges given
For R = 0 To 10
MyList(R, 0) = .Range("A" & R + 1)
MyList(R, 1) = .Range("D" & R + 1)
MyList(R, 2) = .Range("G" & R + 1)
Next R
End With


however if you change the 9 to a 10 in the statement above, you will see the tenth row in the listbox from the spreadsheet. Understand?

the with statement also selects which 3 columns to show in the listbox. A, D and G