By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,871 Members | 2,418 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,871 IT Pros & Developers. It's quick & easy.

multidimensional listbox

P: 88
hi experts,

i would like to enter several columns of information from textboxes in my userform into a list box - for each item i need five posts of data (item number, description, unit price, quantity and total price). but when i try to add an item with the lstItems.Add() method, i can only give one argument.

how can i do this?

when i have entered all the items i want into the list box, i am going to loop through all items and insert them into a table in word, so i need all the columns to be handled separately. if it's easier to go via a multidimensional array i don't mind doing that - i just don't know how to.

please help me with this.
Sep 17 '07 #1
Share this Question
Share on Google+
8 Replies


kadghar
Expert 100+
P: 1,295
hi experts,

i would like to enter several columns of information from textboxes in my userform into a list box - for each item i need five posts of data (item number, description, unit price, quantity and total price). but when i try to add an item with the lstItems.Add() method, i can only give one argument.

how can i do this?

when i have entered all the items i want into the list box, i am going to loop through all items and insert them into a table in word, so i need all the columns to be handled separately. if it's easier to go via a multidimensional array i don't mind doing that - i just don't know how to.

please help me with this.
I think the array would be a nice idea. It depends the version you're using, but try something like this:

Expand|Select|Wrap|Line Numbers
  1. Dim Arr(1 To 10, 1 To 10) As Integer
  2. Dim j As Integer
  3. Dim i As Integer
  4. For i = 1 To 10
  5.     For j = 1 To 10
  6.         Arr(i, j) = i * j
  7.     Next
  8. Next
  9. ListBox1.ColumnCount = 10
  10. ListBox1.List = Arr
HTH
Sep 17 '07 #2

P: 88
I think the array would be a nice idea. It depends the version you're using, but try something like this:

Expand|Select|Wrap|Line Numbers
  1. Dim Arr(1 To 10, 1 To 10) As Integer
  2. Dim j As Integer
  3. Dim i As Integer
  4. For i = 1 To 10
  5.     For j = 1 To 10
  6.         Arr(i, j) = i * j
  7.     Next
  8. Next
  9. ListBox1.ColumnCount = 10
  10. ListBox1.List = Arr
HTH
a couple of problems that would arise if i took that approach is that i for one thing only need to add one row at a time, and for another that i don't know on beforehand how many items i need to add. in other words, i can't specify the size of the array vertically when declaring it.

how do i declare a multidimensional array with five columns and x rows?
Sep 17 '07 #3

kadghar
Expert 100+
P: 1,295
a couple of problems that would arise if i took that approach is that i for one thing only need to add one row at a time, and for another that i don't know on beforehand how many items i need to add. in other words, i can't specify the size of the array vertically when declaring it.

how do i declare a multidimensional array with five columns and x rows?
Use Redim:

Expand|Select|Wrap|Line Numbers
  1. Dim Arr() As Integer
  2. Dim j As Integer
  3. Dim i As Integer
  4.  
  5. redim arr(1 to n, 1 to m)
  6. For i = 1 To n
  7.     For j = 1 To m
  8.         Arr(i, j) = i * j
  9.     Next
  10. Next
  11. ListBox1.ColumnCount = m
  12. ListBox1.List = Arr
Note that you can redim the array as many times you want only for the last dimension, i.e.
if you have:

Dim Array1() as double
redim Array(1 to 10 , 1 to 20)

The next time you redim it, it must be (1 to 10) in the first dimension, and any size for the last one.

Each time you redim it, the values you have stored in your array will be lost unless you use Redim Preserve
Sep 17 '07 #4

P: 88
Use Redim:

Expand|Select|Wrap|Line Numbers
  1. Dim Arr() As Integer
  2. Dim j As Integer
  3. Dim i As Integer
  4.  
  5. redim arr(1 to n, 1 to m)
  6. For i = 1 To n
  7.     For j = 1 To m
  8.         Arr(i, j) = i * j
  9.     Next
  10. Next
  11. ListBox1.ColumnCount = m
  12. ListBox1.List = Arr
Note that you can redim the array as many times you want only for the last dimension, i.e.
if you have:

Dim Array1() as double
redim Array(1 to 10 , 1 to 20)

The next time you redim it, it must be (1 to 10) in the first dimension, and any size for the last one.

Each time you redim it, the values you have stored in your array will be lost unless you use Redim Preserve
thank you very much for your help! it is sincerely appreciated.

just one last question: have i understood things right when i assume that the first argument of the redim sub is the number of columns, and the second argument is the number of rows? that is, you could use variables named "rows" and "columns" to make it more clear, and get

dim arr() as integer
redim arr(1 to columns,1 to rows)

am i correct?
Sep 17 '07 #5

kadghar
Expert 100+
P: 1,295
thank you very much for your help! it is sincerely appreciated.

just one last question: have i understood things right when i assume that the first argument of the redim sub is the number of columns, and the second argument is the number of rows? that is, you could use variables named "rows" and "columns" to make it more clear, and get

dim arr() as integer
redim arr(1 to columns,1 to rows)

am i correct?
you can use them as you want... just dont get messed with the indexes.

Now, when asigning it to the listbox, i think it will add them like (rows, colums)

But in case it doesnt fit what you need, you can always create a dummy array to switch that.
Sep 17 '07 #6

hariharanmca
100+
P: 1,977
There are many controls are there for multi columns
  • MSFlex Grid
  • List view
  • MSHierarchicalFlexGrid
  • Data grid (Link with database)

You have to import it from components.
Sep 18 '07 #7

P: 88
There are many controls are there for multi columns
  • MSFlex Grid
  • List view
  • MSHierarchicalFlexGrid
  • Data grid (Link with database)
i'm not getting the results i wanted from the list box - how do i import the controls from components?

i'm using vba for outlook
Sep 18 '07 #8

QVeen72
Expert 100+
P: 1,445
Hi,

I'am not sure about the Outlook VBA, but this is how we do in Excel.
Right-Click the VB ToolBox, and Click on "Additional Controls", u will get list of all the OCX registered in ur system, select MSFlexGrid and say OK. the grid control will be Included in ur ToolBox

Regards
Veena
Sep 18 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.