Change ComboBox list values depending on the selected OptionButton

[Kakaska]

Dear forum Members!

First of all, I apologize for my poor English. Not my native language...

I would like to ask you for help in solving a complex problem.
I have a Userform that has three OptionButtons, three TextBoxes, and one ComboBox element.
What I want to achieve is that when I select OptionButton1, it lists the table1 elements of Sheet1 as its ComboBox elements.
If I select OptionButton2, it lists the table2 elements of Sheet2 as its ComboBox elements.
And when I select OptionButton3, it lists the table3 elements of Sheet3 as its ComboBox elements.

- I would like the Combobox elements to not contain empty lines, so exclude them so that they cannot be selected.
- I should be able to type something into the ComboBox, not just select it.
- If I select something from the ComboBox elements, it reads the values of the table from the appropriate table (which is in one line) and automatically enters them as the values of TextBox1, TextBox2, TextBox3. Textboxes are located inside the Userform (where the ComboBox is).
- If I delete the name, delete the contents of the TextBoxes.
- I don't want to tie the filling of the TextBoxes to a button.
- None of the three OptionButtons is checked by default. It is up to the user to choose which one they want. When an OptionButton is selected, the previously hidden ComboBox and TextBoxes appear. I think this makes the solution easier.
- On each worksheet, the table values start with row A2 and are 4 columns wide. The first line is the header line.
- The three worksheets are in one workbook.

Do you think this is feasible?
Currently, these two codes are in operation, but they are mutually exclusive and the possibility of expansion.
Thank you in advance for your help!

Private Sub Userform_Initialize()
Dim LastRow As Long
Dim Cella As Range
Dim WSL As Worksheet

    Set WSL = Worksheets("Sheet1")

   
With WSL
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    
    For Each Cella In .Range("A2:A" & LastRow)
        If Cella.Value <> "" Then
            Me.ComboBox_Name.AddItem Cella.Value
        End If
    Next
End With
End Sub


Private Sub ComboBox_Name_Afterupdate()
Dim Search As String
Dim FoundCell As Range, SearchRange As Range
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")

Set SearchRange = WS.Range("A1", WS.Range("D10").End(xlUp))

Search = Me.ComboBox_Name.Text

If Len(Search) = 0 Then Exit Sub

Set FoundCell = SearchRange.Find(What:=Search, LookIn:=xlValues, LookAt:=xlWhole)

If Not FoundCell Is Nothing Then
    Me.TextBox_PostCode.Value = FoundCell.Offset(0, 1).Value
    Me.TextBox_City.Value = FoundCell.Offset(0, 2).Value
    Me.TextBox_Street.Value = FoundCell.Offset(0, 3).Value
Else
    Me.TextBox_PostCode.Text = ""
    Me.TextBox_City.Text = ""
    Me.TextBox_Street.Text = ""
End If
End Sub

 

[Bosquedeguate]

Something like this?

Book1
	A	B	C	D
1	Name	Postal Code	City	Street
2	a	90808	Long Beach, CA
3	b	89321	Reno, NV
4	c	85431	Billings, MT
5
6
Sheet1

Private Sub btnDelete_Click()
  DeleteSelection
End Sub

Private Sub btnUpdate_Click()
  UpdateSelection
End Sub

Private Sub ListBox1_Click()
  With ListBox1
    TextBox1 = .List(.ListIndex, 1)
    TextBox2 = .List(.ListIndex, 2)
    TextBox3 = .List(.ListIndex, 3)
  End With
End Sub

Private Sub ListBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
  If Button = 2 Then
    MsgBox "show menu"
  End If
End Sub

Private Sub OptionButton1_Click()
  PopulateListBox (Me.OptionButton1.Caption)
End Sub

Private Sub OptionButton2_Click()
  PopulateListBox (Me.OptionButton2.Caption)
End Sub

Private Sub OptionButton3_Click()
  PopulateListBox (Me.OptionButton3.Caption)
End Sub

Private Sub PopulateListBox(wsname As String)
  Dim ws As Worksheet
  Dim lr As ListRow
  Dim obj As ListObject
  
  Set ws = Worksheets(wsname)
  
  Set obj = ws.ListObjects(1)
'    obj.
'  Next obj
  With Me.ListBox1
    If .ListCount > 0 Then .Clear
    For Each lr In obj.ListRows
      .AddItem lr.Range(1)
      .List(.ListCount - 1, 1) = lr.Range(1, 2)
      .List(.ListCount - 1, 2) = lr.Range(1, 3)
      .List(.ListCount - 1, 3) = lr.Range(1, 4)
      .List(.ListCount - 1, 4) = lr.Index
    Next lr
  End With
End Sub

Private Sub UpdateSelection()
  Dim i As Integer, n As Integer
  Dim sheetname As String
  Dim ws As Worksheet
  Dim r As Long
  
  sheetname = GetOptButtonSheetName()
  If Len(sheetname) = 0 Then Exit Sub
  
  Set ws = Worksheets(sheetname)
  'update selected record
  n = 0
  For i = 0 To ListBox1.ListCount - 1
  
    If ListBox1.Selected(i) Then
      idx = i
      n = n + 1
    End If
  Next i
  If n = 0 Then
    MsgBox "no ListBox records have been selected to update"
    Exit Sub
  End If
  
  If n > 1 Then
    MsgBox "Select only one record to update in the ListBox"
    Exit Sub
  End If
  
  r = ListBox1.List(i - 1, 4) 'get Table row that is being updated

  With ws.ListObjects(1).ListRows(r)
    .Range(1, 2) = Val(TextBox1)
    .Range.NumberFormat = "00000"
    .Range(1, 3) = TextBox2
    .Range(1, 4) = TextBox3
  End With

End Sub

Private Sub DeleteSelection()
  Dim i As Integer
  Dim sheetname As String
  Dim ws As Worksheet
  
  sheetname = GetOptButtonSheetName()
  If Len(sheetname) = 0 Then Exit Sub
  
  Set ws = Worksheets(sheetname)
  'delete selected records
  For i = ListBox1.ListCount - 1 To 0 Step -1
    If ListBox1.Selected(i) Then
      ws.ListObjects(1).ListRows(ListBox1.List(i, 4)).Delete
      ListBox1.RemoveItem (i)
    End If
  Next i
  
  'After delections refresh the list (i.e. clear it and reload it)
  PopulateListBox (sheetname)
    
  
  
End Sub

Private Function GetOptButtonSheetName()
  Dim i As Integer
  Dim opt As MSForms.OptionButton
  
  GetOptButtonSheetName = ""
  For i = 1 To 3
    Set opt = Me.Controls("OptionButton" & i)
    If opt.Value = True Then Exit For
  Next i
  If i < 4 Then GetOptButtonSheetName = opt.Caption
End Function

I implemented a ListBox with 5 columns, instead of a ComboBox.
The 5 columns in the ListBox contain: Name, Postal Code, City, Street and the table index for the ListBox item (table row).
This way a lookup of the data from the worksheet is not necessary to populate the 3 TextBoxes;
The index (or row number) stored in column 5 of the ListBox is the row number of the item in the Worksheet Table.
When entries in the table are Deleted or Updated this index provides the needed row number.

Here is a link to my test worksheets and userform.

OptionButtonAndListBox

 

[Kakaska]

Hello!
Sorry for the late reply, but I've been working a lot and haven't had any free time!
Thank you for the answer!
I wasn't exactly looking for such a solution, but part of it is very useful for me.
Because I can partially incorporate it into my code, which will work well that way.

I couldn't find a solution to a -for me- more complicated part...
So, from a programmer's point of view, it's not exactly a nice solution, but it was solved.
I put 3 stacked comboboxes on the userform, whose visibility I control in vb.

Once again, thank you very much for your time!