VBA lookup and return data from a table to a userform textbox

[Gem866]

In my excel workbook I have a table called Accounts on a worksheet called Accounts.

I have a userform that has a combobox and the combobox data source is the Accounts table.

I am wanting to have a selection from a combobox assign a value from a lookup or an index and match to a textbox on the user form. As part of my userform initialisation I am selecting the data from the table for the combo box list as follows

Private Sub UserForm_Initialize()
With Application
.WindowState = xlMaximized
Zoom = Int(.Width / Me.Width * 100)
Width = .Width
Height = .Height
End With

With Sheets("Accounts")
Me.CmbChurch.List = .Range("Accounts[Acct Name]").Value
End With

End Sub

The combobox shows the Acct Name column from the table. Once the selection has been made from the combobox I then want the account number (Acct #) to be displayed in a text box called TxtChurch. The following is how I am trying to do that but it doesn't work.

Private Sub CmbChurch_AfterUpdate()

'Lookup Values based on first Control
With Me
.TxtChurch = WorksheetFunction.Index(Range("Accounts[Acct #]"), WorksheetFunction.Match(CmbChurch, Range("Accounts[Acct Name]"), 0))
End With

End Sub

My Table 'Accounts' has the following headers
Acct Name Acct # Attention Address Suburb Pcode SSNotes CINotes Type

I want to lookup based on Acct Name (Which I think has become the selection in the Me.CMBChurch.value) and return data from Acct # into the TxtChurch textbox.

Hope someone can help - Sorry I only know a little VBA

Regards

 

[Fluff]

How about

Private Sub ComboBox1_AfterUpdate()
   With Sheets("Accounts").ListObjects("Accounts").ListColumns("Acct #")
      Me.TextBox1.Value = .Range(Me.ComboBox1.ListIndex + 2)
   End With
End Sub

Change combo & text box names to suit.

 

[Gem866]

How about

Private Sub ComboBox1_AfterUpdate()
   With Sheets("Accounts").ListObjects("Accounts").ListColumns("Acct #")
      Me.TextBox1.Value = .Range(Me.ComboBox1.ListIndex + 2)
   End With
End Sub

Change combo & text box names to suit.

Thank you - Worked perfectly

 

[Fluff]

You're welcome & thanks for the feedback.