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.)
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
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 SubChange the name and the address will change.
Hope this fixes your problem. If so, please be sure to mark this Answer as Selected.