Populating Textbox from Collection Object

[MaxChef]

Hi all,

[FONT=&quot]Is there a way to populate a TextBox on a form, to show a correct row value, by using the collection index that is being used to populate a Comboox ? I’m trying to bypass the ten column limit.<o></o>[/FONT]
[FONT=&quot]<o> </o>[/FONT]
[FONT=&quot]<o> </o>[/FONT]
[FONT=&quot]On a user form:<o></o>[/FONT]
[FONT=&quot]<o> </o>[/FONT]
[FONT=&quot]Combobox #1 is populated from a range that had Columns A's (from “IngredientData” range) unique values passed as a collection object.<o></o>[/FONT]
[FONT=&quot]<o> </o>[/FONT]
[FONT=&quot]Combobox #2 is populated using Combobox #1's value to display the rows pertaining to that choice.<o></o>[/FONT]
[FONT=&quot]<o> </o>[/FONT]
[FONT=&quot]The remaining nine listIndex items are displayed in textboxes.<o></o>[/FONT]
[FONT=&quot]<o> </o>[/FONT]
[FONT=&quot]I would like to use the values in both comboboxes to bypass the additem method, and refer directly to the column in the range, using their collection row index number in order to populate a TextBox.<o></o>[/FONT]
[FONT=&quot]Is this possible? Is there another way?<o></o>[/FONT]
<o> </o>

<o:p></o:p>
‘setting up the collection objects<o:p></o:p>
  Set myCollection = New Collection
<o:p></o:p>
  On Error Resume Next
<o:p></o:p>
  With Worksheets("IngredientLists")
    For Each myCell In .Range("IngredientData", .Cells(.Rows.Count, "A").End(xlUp)).Cells
    myCollection.Add myCell.Value, CStr(myCell.Value)
    Next myCell
    End With
<o:p></o:p>
  On Error GoTo 0
<o:p></o:p>
  For iCtr = 1 To myCollection.Count
    Me.ComboBox1.AddItem myCollection.Item(iCtr)
    Next iCtr<o:p></o:p>

<o> </o>

<o:p></o:p>
‘initializing the boxes<o:p></o:p>
  Set myCollection = New Collection
<o:p></o:p>
  On Error Resume Next
<o:p></o:p>
  With Worksheets("IngredientLists")
    For Each myCell In .Range("IngredientData", .Cells(.Rows.Count, "A").End(xlUp)).Cells
    myCollection.Add myCell.Value, CStr(myCell.Value)
    Next myCell
    End With
<o:p></o:p>
  On Error GoTo 0
<o:p></o:p>
  For iCtr = 1 To myCollection.Count
    Me.ComboBox1.AddItem myCollection.Item(iCtr)
    Next iCtr
<o:p></o:p>
  End Sub<o:p></o:p>

<o> </o>

<o:p></o:p>
‘Passing the values to the Text Boxes<o:p></o:p>
  Private Sub ComboBox2_Change()
<o:p></o:p>
  With Me.ComboBox2
    If .ListIndex < 0 Then
    Exit Sub
    End If
<o:p> </o:p>
  Me.PU_txt.Value = .List(.ListIndex, 1)
     ‘yada,yada….<o:p></o:p>
  Me.UnitCost_txt.Value = .List(.ListIndex, 9)
  'Me.LastUpdate_txt.Value = .List(.ListIndex, 10) Can't have 11 cloumns
  'Me.ConvOrganic_txt.Value = .List(.ListIndex, 11) Can't have 12 cloumns

<o> </o>
Any and all comments are appreciated.

 

[Norie]

What 10 column limit?

 

[MaxChef]

Hi Norrie,

When I try to add an additional list item, I receive a runtime error '380', could not set the property. Invalid property value.

ith Me.ComboBox2
    .AddItem myCell.Offset(0, 1).Value
    .List(.ListCount - 1, 1) = myCell.Offset(0, 2).Value
    .List(.ListCount - 1, 2) = myCell.Offset(0, 3).Value
    .List(.ListCount - 1, 3) = myCell.Offset(0, 4).Value
    .List(.ListCount - 1, 4) = myCell.Offset(0, 5).Value
    .List(.ListCount - 1, 5) = myCell.Offset(0, 6).Value
    .List(.ListCount - 1, 6) = myCell.Offset(0, 7).Value
    .List(.ListCount - 1, 7) = myCell.Offset(0, 8).Value
    .List(.ListCount - 1, 8) = myCell.Offset(0, 9).Value
    .List(.ListCount - 1, 9) = myCell.Offset(0, 10).Value
    .List(.ListCount - 1, 10) = myCell.Offset(0, 11).Value

When I comment out the item, it works fine but, without populating the desired last textbox.

 

[Norie]

Sorry I'm confused.

That code isn't populating any textboxes, it appears to be populating, or at least trying to anyway, a combobox.

Why do you want to populate a combobox with multiple columns anyway?

 

[MaxChef]

Sorry for the confusion. I'm a bit over my head here.

I'm trying to make a dataform of sorts.
Combobox #1 - unique values
Combobox#2 - list of choice from the chosen category
All of the textboxes then display the various information relating that that item

Specifically:
Combobox #1 - Food Categories
Combobox #2 - Ingredients from those Categories
Textboxes - Purchase Unit, Various Methods of Measure, Weights associated with those measures , UnitCost ,LastUpdate of Cost and so on.

I can read the values in the code when hovering over

" .List(.ListCount - 1, 10) = myCell.Offset(0, 11).Value"

but I'm unable to display it in my form. This is where it bugs out.