comboboxes, extracting specific item

[Andrew]

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

 

[GS]

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

 

[Vacuum Sealed]

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.

 

[Andrew]

After serious thinking Andrew wrote :









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?

 

[isabelle]

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

 

[GS]

Andrew pretended :

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.

 

[Andrew]

Andrew pretended :





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

 

[GS]

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