Hello,
I'm trying to populate a combo box with two columns and an unknown
number of rows. I would like to populate the items using a reference
to row and column, such as box(nrow,ncol)="X". This code would
ideally put the value "X" into the combo box at the specified row and
column. In short, I would like to write to and access the box as if
it were an array.

And..once populated, I would like to be able to pull values out based
on column. So, if I had a box containing:

"Peanuts" 1
"Cashews" 3
"Brazil Nuts" 2

If someone selected "Cashews", I would like to access the number 3
which is associated with "Cashews".

Can someone explain how to do something like this?

thanks

After serious thinking Andrew wrote :
> Hello,
> I'm trying to populate a combo box with two columns and an unknown
> number of rows. I would like to populate the items using a reference
> to row and column, such as box(nrow,ncol)="X". This code would
> ideally put the value "X" into the combo box at the specified row and
> column. In short, I would like to write to and access the box as if
> it were an array.
>
> And..once populated, I would like to be able to pull values out based
> on column. So, if I had a box containing:
>
> "Peanuts" 1
> "Cashews" 3
> "Brazil Nuts" 2
>
> If someone selected "Cashews", I would like to access the number 3
> which is associated with "Cashews".
>
> Can someone explain how to do something like this?
>
> thanks

Set your BoundColumn property to 2 so when someone selects "Cashews" it
returns the value in the next column. you could even hide the 2nd
column (set Width to 0pt.) so users only see the product list.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Hi Andrew

you could try Debra Dalgliesh's DataValidation code.

It shows you how to setup a Dependant List using INDEX/MATCH.

http://www.contextures.com/xlDataVal15.html

I use it as a means of filtering departments and who is in them.

HTH
Mick.

On Jul 5, 10:50*am, GS <g...@somewhere.net> wrote:
> After serious thinking Andrew wrote :
>
>
>
> > Hello,
> > I'm trying to populate a combo box with two columns and an unknown
> > number of rows. *I would like to populate the items using a reference
> > to row and column, such as box(nrow,ncol)="X". *This code would
> > ideally put the value "X" into the combo box at the specified row and
> > column. *In short, I would like to write to and access the box as if
> > it were an array.
>
> > And..once populated, I would *like to be able to pull values out based
> > on column. *So, if I had a box containing:
>
> > "Peanuts" * *1
> > "Cashews" * 3
> > "Brazil Nuts" 2
>
> > If someone selected "Cashews", I would like to access the number 3
> > which is associated with "Cashews".
>
> > Can someone explain how to do something like this?
>
> > thanks
>
> Set your BoundColumn property to 2 so when someone selects "Cashews" it
> returns the value in the next column. you could even hide the 2nd
> column (set Width to 0pt.) so users only see the product list.
>
> HTH
>
> --
> Garry
>
> Free usenet access athttp://www.eternal-september.org
> ClassicVB Users Regroup! comp.lang.basic.visual.misc

It sounds like we are talking about the same thing, but I don't
understand your response. How do I get a number into the second
column? I'd like to access the elements of the list just as if it
were an array, where I can write to or read from it based on column
and row indices. How is that done with a combobox?

hi,

'ListIndex property Identifies the currently selected item
'0 is first column index
'1 is column index 2

Private Sub ComboBox1_Change()
With ActiveSheet.ComboBox1
MsgBox .List(.ListIndex, 1)
End With
End Sub


--
isabelle

Andrew pretended :
> On Jul 5, 10:50*am, GS <g...@somewhere.net> wrote:
>> After serious thinking Andrew wrote :
>>
>>
>>
>>> Hello,
>>> I'm trying to populate a combo box with two columns and an unknown
>>> number of rows. *I would like to populate the items using a reference
>>> to row and column, such as box(nrow,ncol)="X". *This code would
>>> ideally put the value "X" into the combo box at the specified row and
>>> column. *In short, I would like to write to and access the box as if
>>> it were an array.
>>
>>> And..once populated, I would *like to be able to pull values out based
>>> on column. *So, if I had a box containing:
>>> "Peanuts" * *1
>>> "Cashews" * 3
>>> "Brazil Nuts" 2
>>
>>> If someone selected "Cashews", I would like to access the number 3
>>> which is associated with "Cashews".
>>
>>> Can someone explain how to do something like this?
>>> thanks
>>
>> Set your BoundColumn property to 2 so when someone selects "Cashews" it
>> returns the value in the next column. you could even hide the 2nd
>> column (set Width to 0pt.) so users only see the product list.
>>
>> HTH
>>
>> --
>> Garry
>>
>> Free usenet access athttp://www.eternal-september.org
>> ClassicVB Users Regroup! comp.lang.basic.visual.misc
>
> It sounds like we are talking about the same thing, but I don't
> understand your response. How do I get a number into the second
> column? I'd like to access the elements of the list just as if it
> were an array, where I can write to or read from it based on column
> and row indices. How is that done with a combobox?

The simplest way is to put your data into an array and then assign the
array to the list.

Example:
Dim vaData As Variant

'Get list items from worksheet
vaData = ActiveSheet.Range("$A$1:$B$3") 'revise to suit
'Note that this is a 2D array consisting of 3 rows by 2 cols
'vaData(1, 1) contains "Peanuts"; vaData(1, 2) contains '1'
'vaData(2, 1) contains "Cashews"; vaData(2, 2) contains '3'
'vaData(2, 1) contains "Brazil Nuts"; vaData(2, 2) contains '2'

'Assign list to combobox
ComboBox1.List = vaData
'.List(0, 0) = "Peanuts"; .List(0, 1) = '1'
'.List(0, 0) = "Cashews"; .List(0, 1) = '3'
'.List(0, 0) = "Brazil Nuts"; .List(0, 1) = '2'
'Syntax: .List(nRow, nCol) = x

Now, with BoundColumn set to 2, when a user selects "Cashews" the value
returned is 3. If you set the ColumnWidth property to 50pt,0pt then
the user won't see the 2nd column.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Jul 6, 8:16*am, GS <g...@somewhere.net> wrote:
> Andrew pretended :
>
>
>
> > On Jul 5, 10:50*am, GS <g...@somewhere.net> wrote:
> >> After serious thinking Andrew wrote :
>
> >>> Hello,
> >>> I'm trying to populate a combo box with two columns and an unknown
> >>> number of rows. *I would like to populate the items using a reference
> >>> to row and column, such as box(nrow,ncol)="X". *This code would
> >>> ideally put the value "X" into the combo box at the specified row and
> >>> column. *In short, I would like to write to and access the box as if
> >>> it were an array.
>
> >>> And..once populated, I would *like to be able to pull values out based
> >>> on column. *So, if I had a box containing:
> >>> "Peanuts" * *1
> >>> "Cashews" * 3
> >>> "Brazil Nuts" 2
>
> >>> If someone selected "Cashews", I would like to access the number 3
> >>> which is associated with "Cashews".
>
> >>> Can someone explain how to do something like this?
> >>> thanks
>
> >> Set your BoundColumn property to 2 so when someone selects "Cashews" it
> >> returns the value in the next column. you could even hide the 2nd
> >> column (set Width to 0pt.) so users only see the product list.
>
> >> HTH
>
> >> --
> >> Garry
>
> >> Free usenet access athttp://www.eternal-september.org
> >> ClassicVB Users Regroup! comp.lang.basic.visual.misc
>
> > It sounds like we are talking about the same thing, but I don't
> > understand your response. *How do I get a number into the second
> > column? *I'd like to access the elements of the list just as if it
> > were an array, where I can write to or read from it based on column
> > and row indices. *How is that done with a combobox?
>
> The simplest way is to put your data into an array and then assign the
> array to the list.
>
> * Example:
> * Dim vaData As Variant
>
> * 'Get list items from worksheet
> * vaData = ActiveSheet.Range("$A$1:$B$3") 'revise to suit
> * 'Note that this is a 2D array consisting of 3 rows by 2 cols
> * 'vaData(1, 1) contains "Peanuts"; vaData(1, 2) contains '1'
> * 'vaData(2, 1) contains "Cashews"; vaData(2, 2) contains '3'
> * 'vaData(2, 1) contains "Brazil Nuts"; vaData(2, 2) contains '2'
>
> * 'Assign list to combobox
> * ComboBox1.List = vaData
> * '.List(0, 0) = "Peanuts"; .List(0, 1) = '1'
> * '.List(0, 0) = "Cashews"; .List(0, 1) = '3'
> * '.List(0, 0) = "Brazil Nuts"; .List(0, 1) = '2'
> * 'Syntax: .List(nRow, nCol) = x
>
> Now, with BoundColumn set to 2, when a user selects "Cashews" the value
> returned is 3. If you set the ColumnWidth property to *50pt,0pt *then
> the user won't see the 2nd column.
>
> --
> Garry
>
> Free usenet access athttp://www.eternal-september.org
> ClassicVB Users Regroup! comp.lang.basic.visual.misc

This is perfect. Exactly what I was looking for. Thanks for your
help.

Andy

You're very welcome! Always glad to be of help wherever I can...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc