Excel VBA - How can I link an userform combobox with a dropdown list in a spreadsheet?

Question

On my spreadsheet I have a dropdown box which updates the headers in the spreadsheet dynamically as shown below in the attached gif.

I also a combo box on my data entry userform which uses the same source as the drop down box in the spreadsheet.

As you see, at the bottom of the userform I have a listbox (lstDatabase) display the headers from the spreadsheet (See code block below for code)

Dim iRow As Long
iRow = [Counta(Sheet4!D:D)]
With DataEntry

.lstDatabase.ColumnCount = 17
.lstDatabase.ColumnHeads = True

.lstDatabase.ColumnWidths = "50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50, 50"
 
 If iRow > 1 Then
 .lstDatabase.RowSource = "Sheet4!A4:Q" & iRow
 
 Else
 .lstDatabase.RowSource = "Sheet4!A5:Q5"
 End If
 End With

Currently the only way I can get these headers to change is to close the form, change the selection of the drop-down list on the spreadsheet and restart the userform, at which point it'll update to the new headers.

What I want to instead is interlink the combobox on the user form with the drop-down list in spreadsheet and be able to dynamically update the headers displays in the listbox using the selection made in the combo box.

Psuedo code example

Userform combobox selects "PL531"
Listbox and spreadsheet both dynamically update their headers to show the headers relevant to "PL531"

Userform combobox changes selection to "PL931e"
Listbox and spreadsheet immediatelly dynamically update their headers to show the headers relevant to "PL931e

Obviously I know that the code for lstDatabase.RowSource will have to change based on a if statement located within the Combobox changed() method, but I don't know how I'm able to reference the selection made on the spreadsheet's dropdown box to match that of the selection made in the userform combobox and subsequently update the listbox headers.

Answer 1

In the end, I've decided to create 6 individual spreadsheets and link them together via the combobox.

Using the combobox when a value is selected e.g. PL531e then it will select the worksheet titled PL531e and it will enter the data into the respective worksheet.

I use the following code to accomplish this:

Private Sub CommandButton1_Click()
Dim sh As Worksheet
Dim AddNew As Range

If ComboBox1.Value = "PL531e" Then
Set sh = ThisWorkbook.Sheets("PL531e")
ElseIf ComboBox1.Value = "PL931" Then
Set sh = ThisWorkbook.Sheets("PL931")
ElseIf ComboBox1.Value = "PL968" Then
Set sh = ThisWorkbook.Sheets("PL968")
ElseIf ComboBox1.Value = "PN410X" Then
Set sh = ThisWorkbook.Sheets("PN410X")
ElseIf ComboBox1.Value = "PN510" Then
Set sh = ThisWorkbook.Sheets("PN510")
ElseIf ComboBox1.Value = "GL100" Then
Set sh = ThisWorkbook.Sheets("GL100")
End If
Set AddNew = sh.Range("A6536").End(xlUp).Offset(1, 0)

AddNew.Offset(0, 0).Value = TestNo.Text
AddNew.Offset(0, 1).Value = NeuronID.Text
AddNew.Offset(0, 2).Value = DateCode.Text
AddNew.Offset(0, 3).Value = TextBox4.Text
AddNew.Offset(0, 4).Value = TextBox5.Text
AddNew.Offset(0, 5).Value = TextBox6.Text
AddNew.Offset(0, 6).Value = TextBox7.Text
AddNew.Offset(0, 7).Value = TextBox8.Text
AddNew.Offset(0, 8).Value = TextBox9.Text
AddNew.Offset(0, 9).Value = TextBox10.Text
AddNew.Offset(0, 10).Value = TextBox11.Text
AddNew.Offset(0, 11).Value = TextBox12.Text
AddNew.Offset(0, 12).Value = TextBox13.Text
AddNew.Offset(0, 13).Value = TextBox14.Text
AddNew.Offset(0, 14).Value = TextBox15.Text
AddNew.Offset(0, 15).Value = TextBox16.Text
AddNew.Offset(0, 16).Value = TextBox17.Text

End Sub

Next I'm working on using the original worksheet stated in the question as an overview sheet and will be using the data validation drop-down list to dynamically update all the cells to show me the cell information in each separate worksheet relevant to that particular part including the headers & accompanying data.

Have yet to figure out what formula I'm using for this, but will update this answer when I find out.