Using an Index formula to populate a TextBox on a Form

[gheyman]

I have a ComboBox (Combo1) that after a selection is made I want to populate other Textboxes using an Index formula

The names are"
ComboBox = ComboBox1
The Tab where my table is = BuyerID or Sheet10
The table field that I want to pull the data from = Buyer
The Match from selection in ComboBox
Match Field "BuyerID" (Yes, its the same name as the tab)

Private Sub ComboBox1_Change()



TextBox1 = Sheet10.Range("C18").Value

TextBox2 = WorksheetFunction.Index(Range("BuyerID[Buyer]"), WorksheetFunction.Match(ComboBox1, Range("BuyerID[BuyerID]"), 0))

End Sub

not sure what's wrong with the index formula but its not working
Thanks

 

[mart37]

Range("BuyerID[Buyer]") ==> Sheets("BuyerID").[Buyer]

 

[gheyman]

Still getting an error

Private Sub ComboBox1_Change()

TextBox2 = WorksheetFunction.Index(Sheets("BuyerID").[Buyer], WorksheetFunction.Match(ComboBox1, Sheets("BuyerID").[BuyerID], 0))

End Sub

Run-time error '1004':
Unable to get the Match property of the worksheetfunction class

 

[Norie]

How are you populating the combobox?

 

[mart37]

Put under you code:

Debug.Print [Buyer].Address
Debug.Print [BuyerID].Address
Debug.Print ComboBox1

Open the View: Window Direct
Run your macro

 

[gheyman]

How are you populating the combobox?

Norie Im not sure I follow your question. The combobox is on a userform. The User scrolls to the item they want and then clicks on it.

 

[Fluff]

How is the combo getting it's list of values?

 

[gheyman]

I am using the RowSource properties

RowSource =BuyerID!A2:F5000
BoundColumn 1 (Which is the BuyerID)
ListRows 8

 

[Fluff]

Ok, how about

Private Sub ComboBox1_Click()
   Me.TextBox2 = Range("Buyerid[Buyer]").Cells(Me.ComboBox1.ListIndex + 1, 1)
End Sub

 

[gheyman]

Thank you.

For future reference: I was using the Worksheet name when I should have been using the table name.

This worked

Me.TextBox1 = Range("ModelGeneral_vluBuyer[BuyerID]").Cells(Me.ComboBox1.ListIndex + 1, 1)
Me.TextBox2 = Range("ModelGeneral_vluBuyer[Buyer]").Cells(Me.ComboBox1.ListIndex + 1, 1)

Thank you all