Hi Everyone

I've been a member of this community for quite a while, and normally just by browsing through the threads I've often found solutions to my problems, But this time I couldn't so I'm writing to you excel gurus for some much needed help



I'm creating a userform with 2 dynamic, dependent Combobox's,

The Idea is that those 2 combobox's will display certain lists depending on what was selected in Combobox3(STATE)

The lists for the dependent comboboxes are contained in a separate worksheet (LISTS) in the same excel file

So if someone picks the State = QLD in Combobox3, it will bring up 1 dynamic list in Combobox2 and another in Combobox1, but if they selected State VIC in Combobox3, it will bring up 2 different Dynamic list in Combobox2 and Combobox1



written below is the code I have been trying to use, based on what I've seen elsewhere in this Forum and online, however it doesn't seem to work

If anyone can offer any advice it would be greatly appreciated



Private Sub ComboBox3_Change()

    Dim OTHIAC As Range
    Dim QLDIAC As Range
    Dim NSWIAC As Range
    Dim VICIAC As Range
    Dim QLDASS As Range
    Dim NSWASS As Range
    Dim VICASS As Range

    With Worksheets("LISTS")
        Set OTHIAC = .Range("J2", .Range("J65536").End(xlUp))
        Set NSWIAC = .Range("F2", .Range("F65536").End(xlUp))
        Set VICIAC = .Range("H2", .Range("H65536").End(x1up))
        Set QLDIAC = .Range("D2", .Range("D65536").End(x1up))
        Set QLDASS = .Range("L2", .Range("L65536").End(xlUp))
        Set NSWASS = .Range("N2", .Range("N65536").End(x1up))
        Set VICASS = .Range("P2", .Range("P65536").End(xlUp))
    End With

If ComboBox3.Value = "TAS" Then
         
                ComboBox1.RowSource = "LISTS!" & OTHIAC.Address
                ComboBox2.RowSource = "LISTS!" & VICASS.Address
   
ElseIf ComboBox3.Value = "NSW" Then
  
                 ComboBox1.RowSource = "LISTS!" & NSWIAC.Address
                 ComboBox2.RowSource = "LISTS!" & NSWASS.Address

ElseIf ComboBox3.Value = "QLD" Then
                   
                ComboBox1.RowSource = "LISTS!" & QLDIAC.Address
                ComboBox2.RowSource = "LISTS!" & QLDASS.Address

ElseIf ComboBox3.Value = "ACT" Then
  
              ComboBox1.RowSource = "LISTS!" & OTHIAC.Address
              ComboBox2.RowSource = "LISTS!" & NSWASS.Address
    
ElseIf ComboBox3.Value = "SA" Then
  
                ComboBox1.RowSource = "LISTS!" & OTHIAC.Address
                ComboBox2.RowSource = "LISTS!" & QLDASS.Address
    
ElseIf ComboBox3.Value = "VIC" Then
                ComboBox1.RowSource = "LISTS!" & VICIAC.Address
                ComboBox2.RowSource = "LISTS!" & VICASS.Address
    
ElseIf ComboBox3.Value = "WA" Then
                ComboBox1.RowSource = "LISTS!" & OTHIAC.Address
                ComboBox2.RowSource = "LISTS!" & QLDASS.Address
  
End If

    
End Sub
Thanks in Advance