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

Gem866

New Member
Joined
Feb 11, 2015
Messages
12
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
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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

New Member
Joined
Feb 11, 2015
Messages
12
How about
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,328
Messages
5,624,034
Members
416,007
Latest member
csf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top