Multiple Dependent Drop-Down List in Excel VBA-CodePudding

I'm fairly new to VBA and I'm having some trouble with creating multiple drop down lists with an additional twist. The two sheets of the workbook that I'm working on is shown in the images linked in this post. [1]: https://i.stack.imgur.com/BBIA1.png [2]: https://i.stack.imgur.com/2Gkxb.png

So basically what I'm trying to do is to create a dropdown list for cell B6 in the "FORM" sheet which is pulled from cells A2:A4 in the "LISTS" sheet. Based on what is chosen in cell B6, a dropdown list (pulled from the "LISTS" sheet) for cell D6 in the "FORM" sheet will be created. The caveat here is that if "PART" is chosen from the dropdown list in cell B6 of the "FORM" sheet, instead of a generating a dropdown list in D6, I'd like the cell to show "N/A" instead. Shown below is the code that I attempted to write.

Sub PRODUCT_LIST()

Dim FORM As Worksheet
Dim LISTS As Worksheet
Dim PRODUCT As Range
Dim PRODUCT_LIST As Range
Dim MODEL As Range
Dim BIKE_LIST As Range
Dim CHAIR_LIST As Range

Set FORM = ThisWorkbook.Worksheets("FORM")
Set LISTS = ThisWorkbook.Worksheets("LISTS")
Set PRODUCT = FORM.Range("B6")
Set MODEL = FORM.Range("D6")
Set PRODUCT_LIST = LISTS.Range("A2:A4")
Set BIKE_LIST = LISTS.Range("B2:B8")
Set CHAIR_LIST = LISTS.Range("C2:C3")

With PRODUCT.Validation
    .Delete 'delete previous validation
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Formula1:="='" & LISTS.Name & "'!" & PRODUCT_LIST.Address
End With

If PRODUCT.Value = "BIKE" Then
    With MODEL.Validation
        .Delete 'delete previous validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Formula1:="='" & LISTS.Name & "'!" & CHAIR_LIST.Address
    End With
ElseIf PRODUCT.Value = "CHAIR" Then
    With MODEL.Validation
        .Delete 'delete previous validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
            Formula1:="='" & LISTS.Name & "'!" & BIKE_LIST.Address
    End With
ElseIf PRODUCT.Value = "PART" Then
    MODEL.Value = "N/A"
Else
End If
End Sub

This code did not work the way that I wanted it to and I'm not sure what I'm doing wrong. The dropdown list for B6 works just fine but the dropdown list for D6 seems to be ignoring the condtions that I've placed. Regardless of what I choose in the dropdown list from B6, the dropdown list for D6 is always pulling from CHAIR_LIST. Any help would be appreciated. Thank you.

CodePudding user response：

You did not answer the clarification questions...

Please, try the next approach:

Firstly you need to create the validation for the first cell. Only once, or if needed to modify the list content. Please, copy the next code in a standard module:

Sub PRODUCT_LIST()
  Dim FORM As Worksheet, LISTS As Worksheet
  
  Set FORM = ThisWorkbook.Worksheets ("FORM")
  Set LISTS = ThisWorkbook.Worksheets("LISTS")
  
  
  With FORM.Range("B6").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                 Formula1:="='" & LISTS.name & "'!" & "A2:A4"
  End With
End Sub

Please, copy the next code in the FORM sheet code module. Right click on the sheet name, then choose View Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = "B6" Then
        Dim MODEL As Range, LISTS As Worksheet, BIKE_LIST As Range, CHAIR_LIST
        
        Set MODEL = Me.Range("D6")
        Set LISTS = ThisWorkbook.Worksheets("LISTS")
        Set BIKE_LIST = LISTS.Range("B2:B8")
        Set CHAIR_LIST = LISTS.Range("C2:C3")
        
        Application.EnableEvents = False
         If Target.value = "BIKE" Then
            With MODEL.Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                    Formula1:="='" & LISTS.name & "'!" & BIKE_LIST.Address
            End With
            MODEL.value = ""
         ElseIf Target.value = "CHAIR" Then
            With MODEL.Validation
                .Delete 'delete previous validation
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                    Formula1:="='" & LISTS.name & "'!" & CHAIR_LIST.Address
            End With
            MODEL.value = ""
         ElseIf Target.value = "PART" Then
            MODEL.value = "N/A"
         Else
     End If
     Application.EnableEvents = True
    End If
End Sub

If after changing the MODEL validation list you need to place a value from this list (let us say, the first one), it can be easily done...