Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Is it possible to use a Table Reference in a ComboBox?

0
I'm struggling with this and it not even be possible.
A Table reference that populates a combobox and allows the return
of the selected row to a userform.

Thank You in advance for any and all suggestions.

OldCityCat

I had started a post but not sure if it does a good job of explaning the issue.

Please see excel file

OldCityCat

Answer
Discuss

Discussion

Hi OldCityCat. 

Not quite sure what you mean but please edit your question to attach a representative Excel file (using the Add Files button) and I'll respond. 
John_Ru (rep: 6172) Sep 13, '23 at 8:56 am
John,
Sorry but I don't see an option to Add Files with ether Discuss or Add to Discission.
Should I add a new question?
OldCityCat (rep: 6) Sep 13, '23 at 9:34 am
John, 
I just found the Edit option.
OldCityCat (rep: 6) Sep 13, '23 at 9:36 am
Please go to edit your original question then look below the text box for the Add Files button (find the file and click ok then Update- I think).

So far the question doesn't make enough sense, sorry. 
John_Ru (rep: 6172) Sep 13, '23 at 10:04 am
Sorry but you can only upload Excel files on this Forum. Please try again with an Excel file
John_Ru (rep: 6172) Sep 13, '23 at 10:38 am
Joean. Thank for adding a file (even though we prefer the question text and code NOT to be hidden in a file but stated in the question - wrapping code using the CODE button).

Real problem is that I don't have time to recreate your userform so it would really help if you could post a file including your userform and macro plus some dummy data (no personal data) to save me, Willie or anyone doing that work before we can start to help you. Saves you time to when you get an aswer you don't have to rework for your file. Make sense? (Note that you can add a second Excel file to your question if you like)
John_Ru (rep: 6172) Sep 13, '23 at 12:32 pm
Add to Discussion

Answers

0
Selected Answer

OldCityCat

You didn't provide a representative file but an Excel file with your code (though no userform or data sadly).

I've created a file (using your named parts) to illustrate how to populate the ComboBox from a table part. See attached.

The secret is to populate the ComboBox when the form is launched. Click the blue "Launch form" button and the associated Show code in module1 will lead to the Initialize macro for the userform:

Private Sub UserForm_Initialize()

Dim ws As Worksheet

' populate the ComboBox
Set ws = ThisWorkbook.Worksheets("ConsumerTbl")
ConsumerForm.cmbConsumerName.RowSource = "Consumer_Table[Consumer Name:]"

End Sub

(and you could set a  default value for that box). 

Now click the ComboBox list to see the simple data (just two names in the example). Click one and it will trigger your Change code (though I've commmeted out the "RowSource" line - now in the Initialize code - and those for the other userform elements I didn't create):

Public Sub cmbConsumerName_Change()
Dim ws As Worksheet
Dim wsLR As Long
Dim i As Integer
Set ws = ThisWorkbook.Sheets("ConsumerTbl")
'ConsumerForm.cmbConsumerName.RowSource = "Consumer_Table[Consumer Name:]"
wsLR = ws.Cells(Rows.Count, 2).End(xlUp).Row
For i = 2 To wsLR
     If ws.Cells(i, 2).Value = ConsumerForm.cmbConsumerName.Text Then
       ConsumerForm.tbAddress = ws.Cells(i, "C")
'       ConsumerForm.tbCity = ws.Cells(i, "D")
'       ConsumerForm.tbState = ws.Cells(i, "E")
'       ConsumerForm.tbZip = ws.Cells(i, "F")
'       ConsumerForm.tbHphone = ws.Cells(i, "G")
'       ConsumerForm.tbCphone = ws.Cells(i, "H")
'       ConsumerForm.tbConEmail = ws.Cells(i, "I")
'       ConsumerForm.tbSSNum = ws.Cells(i, "J")
'       ConsumerForm.tbIDNum = ws.Cells(i, "K")
'       ConsumerForm.tbDob = ws.Cells(i, "L")
'       ConsumerForm.cboGender = ws.Cells(i, "M")
'       ConsumerForm.cboRace = ws.Cells(i, "N")
     Exit Sub
 End If
Next i
End Sub

Change the name and the address will change.

Hope this fixes your problem. If so, please be sure to mark this Answer as Selected.

Discuss

Discussion

John, 
I apologize for not getting back to you sooner. I didn't expect you to recreate the userform. I should have sent you a quick note that I would be unable to prepare a sample file until today.
Once again Thank you for your help.
Gil Chichester
OldCityCat (rep: 6) Sep 14, '23 at 6:07 am
Okay Gil but if that worked for you, please mark my Answer as Selected (that's the only reward I get for my efforts, apart from any kind words users give)
John_Ru (rep: 6172) Sep 14, '23 at 6:30 am
Thanks for selecting my Answer, Gil. That also helps other users with similar problems and adds to your own Reputation (you get 2 points I think).
John_Ru (rep: 6172) Sep 14, '23 at 8:11 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login