Auto Populate Combox1 and ListBox1 from Workseet

Question

I am trying to connect my NIGOcomboBox list with the ListBox1 list on my worksheet (DropDownMenus). Currently I have the NIGOcomboBox populating from

 Private Sub UserForm_Initialize()
 Dim cell As Range

'Populate NIGO dropdown menu from "DropDownMenus worksheet.
 For Each cell In .Range("B2:B" & .Cells(Rows.Count, 2).End(xlUp).Row)
 If Not IsEmpty(cell) Then NIGOcombobox.AddItem cell.Value
 Next cell
 End With

Then I have a ListBox that populates as follows:

 Private Sub NIGOcombobox_Change()

 With Worksheets("DropDownMenus")
 .Activate
 Select Case NIGOcombobox
 'Populate NIGO Reason list by dropdown menu selection.
 Case "AMRF"
  For Each cell In .Range("C3:C" & .Cells(Rows.Count, 3).End(xlUp).Row)
  If Not IsEmpty(cell) Then ListBox1.AddItem cell.Value
 Next cell

 Case "OATS"
  For Each cell In .Range("D3:C" & .Cells(Rows.Count, 3).End(xlUp).Row)
  If Not IsEmpty(cell) Then ListBox1.AddItem cell.Value
 Next cell

 Case Else
  MsgBox "Please select a NIGO Reason"

 End Select
 End With
 End Sub

Its not exactly working as planned. I need to make this so when the next person comes along to add a new item to the NIGOcombox it auto selects the next row so they do not have t adjust the code.

Example
NIGOCombobox is in sheet (DropDownMenus) column ("A2:A") and the ListBox1 is also on sheet (DropDownMenus) but starts on column ("C3:C"). Each column after - D, E, F G etc. correspond with the next NIGOCombobox item.

So, A2 =("C3:C"), B2 =("D3:D), C2 = ("E3:D") and so on. Than way when a new item is entered into the NIGOCombobox it auto attaches to the next Listbox row. Hope this makes sense! Thank you

Answer 1

Not sure of your aim. The following code will load column B in NIGOcombobox and then seach for the selected value in the first row of Worksheets("DropDownMenus"). So, if you transpose your column B into the first row of Worksheets("DropDownMenus") (starting in C1), that row will behave like a "header", and this might work. PS: if you want to add to previously selected items, delete the line ListBox1.Clear

Private Sub UserForm_Initialize()
Dim cell As Range

    'Populate NIGO dropdown menu from "DropDownMenus worksheet.
    For Each cell In Worksheets("DropDownMenus").Range("B2:B" & Worksheets("DropDownMenus").Cells(Rows.Count, 2).End(xlUp).Row)
        If Not IsEmpty(cell) Then NIGOcombobox.AddItem cell.Value
    Next cell

End Sub

Private Sub NIGOcombobox_Change()
    Dim TheValueInCombobox As String
    Dim TheHeader As Range
    Dim TheHeaderColumn As Long
    Dim LastRow As Long

    ListBox1.Clear
    TheValueInCombobox = NIGOcombobox.Value
    Set TheHeader = Worksheets("DropDownMenus").Range("A1:Z1").Find(TheValueInCombobox) 'You might want to expand the range
    TheHeaderColumn = TheHeader.Column
    LastRow = Worksheets("DropDownMenus").Cells(Rows.Count, TheHeaderColumn).End(xlUp).Row

    For Each cell In Worksheets("DropDownMenus").Range(Cells(3, TheHeaderColumn), Cells(LastRow, TheHeaderColumn))
        If Not IsEmpty(cell) Then ListBox1.AddItem cell.Value
    Next cell

End Sub

EDIT:

There is no need to have the values for populating NIGOcombobox in a dedicated column: you can scan the headers directly. This way data structure would be clearer (I think).

Private Sub UserForm_Initialize()
Dim cell As Range
Dim lColumn As Long

    'Populate NIGO dropdown menu from "DropDownMenus worksheet.
    lColumn = Worksheets("DropDownMenus").Cells(1, Columns.Count).End(xlToLeft).Column
    For Each cell In Worksheets("DropDownMenus").Range(Cells(1, 3), Cells(1, lColumn))
        If Not IsEmpty(cell) Then NIGOcombobox.AddItem cell.Value
    Next cell

End Sub

Private Sub NIGOcombobox_Change()
    Dim TheValueInCombobox As String
    Dim TheHeader As Range
    Dim TheHeaderColumn As Long
    Dim LastRow As Long
    Dim lColumn As Long

    ListBox1.Clear
    TheValueInCombobox = NIGOcombobox.Value
    lColumn = Worksheets("DropDownMenus").Cells(1, Columns.Count).End(xlToLeft).Column
    Set TheHeader = Worksheets("DropDownMenus").Range(Cells(1, 3), Cells(1, lColumn)).Find(TheValueInCombobox)
    TheHeaderColumn = TheHeader.Column
    LastRow = Worksheets("DropDownMenus").Cells(Rows.Count, TheHeaderColumn).End(xlUp).Row

    For Each cell In Worksheets("DropDownMenus").Range(Cells(3, TheHeaderColumn), Cells(LastRow, TheHeaderColumn))
        If Not IsEmpty(cell) Then ListBox1.AddItem cell.Value
    Next cell

End Sub