Dependent ComboBox Drop Down ISSUES

[bemcbride]

I know the easiest way to do a dependent drop down is with Data Validation. However, the options in my lists are too long to use this (unfortunately, I cannot make them shorter.) I need to be able to create a 2 dependent drop downs (total 3 that cascade depending on the selection.)

I was thinking I could use a combo box and the cell link function. Then I could write a code that said "If this cell is equal to 1 then use this selection on the drop down. If this cell is equal to 2 then use this selection...etc.

Problem is I don't know if this would work and I'm not very good at writing code. There might even be a better way...

Example:

Drop down 1 - select meat, veggies, fruits (say we select meat)
Drop down 2 - select chicken, pork, steak (say we select Steak)
Drop down 3 - select medium well, well, rare (we select rare)


So i used a combobox list range and input the first drop down and had in link to cell A2 then I wrote the below code for Drop down 2 but it doesn't work. Again, I'm new to this and yeah




Private Sub ComboBox2_Change()


If Range(A2) = "Fruits" Then
ComboBox2.AddItem "Apple"
ComboBox2.AddItem "Banana"



If Range(A2) = "Meat" Then
ComboBox2.AddItem "Chicken"
ComboBox2.AddItem "Steak"


End If



End Sub


Any help is MASSIVELY appreciated, I've been trying to figure this out for ages and I need this form done ASAP and this is the only thing left.

 

[L. Howard]

I may have a sheet that does sorta the end result of what I believe you are shooting for. Perhaps a bit different than you describe.

Essentially, drop down 1 has a number of selections, each selection can be a segment of the super long list. (Selections titled to an appropriate name for your reference)

You select from drop down 1, then that portion of the long list is posted in the column next to the drop down, and that just posted list becomes the selections available in a second drop down.

My demo sheet has five different lists to select from, you can have more or less.

Howard

 

[bemcbride]

That sounds like it might work. What do i do?

 

[L. Howard]

That sounds like it might work. What do i do?

Suggest you start with a new workbook for a test installation

On Sheet 2 copy these to cell A1:E1, where each stands for a named title for each segment of your long list. (Change later to real names)

DD_1_1 DD_1_2 DD_1_3 DD_1_4 DD_1_5

In each column enter any test data below each title name, vary the lengths just to demo to you that it can be that way.

On Sheet 1:

In cell B2 install a data validation drop down > List > Source: =Sheet2!$A$1:$E$1 > check Ignore blanks & in-cell drop down > OK

In cell H2 install a data validation drop down > List > Source: =OFFSET($C$2,0,0,COUNTA($C$2:$C$200),1) > check Ignore blanks & in-cell drop down > OK

In cell C1 enter ="List "&B2 (this is optional)

And on a one time basis, enter your initials, or whatever, in in C2 and grab and pull down a few cells to make a short list for the code to clear on the first use. Otherwise the code will error if it has nothing to clear to ready itself for the next list.

Copy the code below, top to bottom, right click the Sheet 1 tab > click View Code > paste the code in the large white field. (make sure there is only one Option Explicit at the top of the field) You can use keys Alt + f11 to toggle between the sheet and the VB Editor

This is a change event code macro that is set to monitor cell B2 which is the Target cell, anytime you select a new list name in B2 the code will fetch the proper titled list from sheet 2 and paste it in column C, C2 and as far down as needed. Once that list is in place, it becomes the list for the H2 drop down.

The little sub Sub xxzz is to re-enabling events on the sheet should the code throw an error while you are setting stuff up. If it errors you need to click the little blue square reset button in the tool bar at the top of the VB Editor (where you pasted the code). Then run the Subxxzz from sheet 1. That will let the Worksheet_Change event code work.

Back to sheet 1 and select a title name from B2, your list should appear in C2 and down C1 should be a header of the list name. You will notice that H2 is now the activecell, ready for a selection from its list.

Give this a go and let me know how and if it does what you want.

Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub

Dim rngFound As Range
Dim aRowCount As Long, _
    aColumn As Long, _
    tRowCount As Long, _
    tColumn As Long
    
Dim myFnd As String

myFnd = Target

Application.EnableEvents = False


If Target = Range("B2") Then
   [H2].ClearContents
   [H2].Select
End If

   tColumn = Target.Offset(, 1).Column
  
   tRowCount = Cells(Rows.Count, tColumn).End(xlUp).Row
   
   Target.Offset(, 1).Resize(tRowCount - 1, 1).ClearContents
      
      
Set rngFound = Sheets("Sheet2").Range("A1:E1").Find(What:=myFnd, _
                                               LookIn:=xlValues, _
                                               LookAt:=xlWhole, _
                                               SearchOrder:=xlByRows, _
                                               SearchDirection:=xlNext, _
                                               MatchCase:=False)
                                               
If Not rngFound Is Nothing Then

   aColumn = rngFound.Column
   aRowCount = Sheets("Sheet2").Cells(Rows.Count, aColumn).End(xlUp).Row
   aColumn = rngFound.Column

  rngFound.Offset(1, 0).Resize(aRowCount).Copy Target.Offset(, 1)
  
Else
    MsgBox "No match found."
End If
Application.EnableEvents = True
End Sub


Sub xxzz()
Application.EnableEvents = True
End Sub