VBA data validation, dependent dropdown list

Hi everyone,

I'm working on an excel that consists of a fill-in form within the worksheet. I understand it may be better to use a userform and activeXcontrols, but I already have 2 userform in the workbook.
*The workbook needs to be usable by all users, therefore I can't use plug-ins.
*I'm totally new in VBA, so please avoid "refer to this link" only. I won't be able to understand the code unless it has comments. I need to know where and which part I can/cannot change.

I need to generate a dependent dropdown list. Which I managed to have it working, however, (as attached) I need to place the reference value in Cell M2. Is there possibly a method to not having to display a value in cell M2 and still have it working?

Notes:
1. information of column [F, G] and [I,J,K] is not constant, as user can add new "company", "product", "detail" to list
2. the actual lists is separated into different worksheet (ie. sheet1 = in-sheet form, sheet2 = company list, sheet 3 = product list)
3. reason that I'm doing this in VBA and not excel built-in functions is to avoid formula loss (overwriting the data without realizing it and then saving the workbook [which happened before])

Possible Error:
I realized that if the list from column I to K is not sorted according to column I, the drop down list will generate mismatch

For example
Col I Col J
A apple
A orange
B Beef
A Lemon

with this list above, the dependent drop down list for A will be (apple, orange, BEEF). It can count there are 3 "A" but the results are shifted.

I appreciate for your help!

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim selected_company As String
    
    With Worksheet("sheet1")
        If .Range("a2") = Empty Then
            .Range("b2,c2,m2") = Empty
            Exit Sub
        Else
            selected_company = Application.WorksheetFunction.VLookup(.Range("A2"), .Range("F:G"), 2, False)
                
            With .Range("B2").Validation
                .Delete
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                xlBetween, Formula1:= _
                "=OFFSET(OFFSET(I:I,0,1,1,1),MATCH(M2,I:I,0)-1,0,COUNTIF(I:I,M2))"
                'perheps i can replace M2 with selected_company, but as I have tried it didn't work
            End With
        End If
    End With

End Sub

Hi,

It looks like you are wanting to learn Cascading Validation Lists. Read about them at:
http://www.excel-user.com/2011/02/ca...ion-lists.html

Because you want to add to data it also seems you need more than Named Ranges. There are these magic things called Dynamic Named Ranges that may help in your puzzle.
Read about them at:
http://www.bettersolutions.com/excel...G820716330.htm

I hope the above topics will help with your problem. Note - No VBA required for either of the above topics, as you requested.

Originally Posted by revzephyr

... I need to place the reference value in Cell M2. Is there possibly a method to not having to display a value in cell M2 and still have it working?

If you don't use cell M2, how would the user select a company?

Edit: disregard. I'm a little slow tonight.

Try this.

For the B2 Data validation list formula use this.
=INDIRECT("DetailA")

And this for C2
=INDIRECT("DetailB")

This will use two named ranges as their list ranges


The code below will update the DetailA and DetailB named ranges when you make a change to A2.

You could also put the lists on other sheets and edit the code to those sheet names and ranges. When the form-sheet is activated, the named ranges are updated to reflect any changes the user may have made.

Private Sub Worksheet_Activate()
    Update_Lists
End Sub
    
Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Address(0, 0) = "A2" Then
        Range("B2,C2").ClearContents
        If Target <> "" Then Update_Lists
    End If
    
End Sub
    
Private Sub Update_Lists()
    
    Dim selected_company As String, r As Long, rc As Long
    
    If Range("A2").Value = "" Then Exit Sub
    
    With Sheets("Sheet1")   'Company list sheet
         selected_company = Application.WorksheetFunction.VLookup(Range("A2"), .Range("F:G"), 2, False)
    End With
    
    With Sheets("Sheet1")   'Product\Detail sheet
        
        'sort on company
         .Range("I:K").Sort Key1:=.Range("I1"), Order1:=xlAscending, Header:=xlYes, MatchCase:=False
         
         'First row of Company
         r = Application.Match(selected_company, .Range("I:I"), 0)
         'Count of company entries
         rc = Application.CountIf(.Range("I:I"), selected_company)
        
         'Named ranges
         ThisWorkbook.Names.Add Name:="DetailA", RefersTo:="=" & .Range("J" & r).Resize(rc).Address(1, 1, xlA1, 1)
         ThisWorkbook.Names.Add Name:="DetailB", RefersTo:="=" & .Range("K" & r).Resize(rc).Address(1, 1, xlA1, 1)
         
    End With
    
End Sub

"company" isn't a company; It's a header. There is no detail data associated with it.

Change the DV list in A2 so it doesn't have "company" in the list. Problem solved.