dropdown list with criteria

[Godders199]

Hello, currently i am using the following recorded code to create a dropdown list
Columns("ab:ab").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="='SQ Hierarchy'!$A:$A"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

What i need to do now is add a criteria into this vba, to only show names in the list from SQ Hierarchy A:A , where SQ Hierararchy B:B is Cardiff for example.

would be good to know 2 options, either to hardcode it into the VBA , or make it dependant on the value in another cell, which could be the best option.
from reading google , it appears it is a dependant dropdown list, but cant find any vba examples.

Any help would be appreciated

 

[Fluff]

Could you supply some sample data from the SQ Hierararchy sheet, so that we can see what your data looks like?

 

[Godders199]

Hi, its as simple as Names and Locations as below,

[TABLE="width: 128"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Location [/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]Cardiff[/TD]
[/TR]
[TR]
[TD]Jayne[/TD]
[TD]London[/TD]
[/TR]
[TR]
[TD]Will[/TD]
[TD]Bristol[/TD]
[/TR]
[TR]
[TD]Ian[/TD]
[TD]Cardiff[/TD]
[/TR]
[TR]
[TD]Sarah[/TD]
[TD]Cardiff[/TD]
[/TR]
[TR]
[TD]Frank[/TD]
[TD]Swansea[/TD]
[/TR]
[TR]
[TD]Ellen[/TD]
[TD]London[/TD]
[/TR]
</tbody><colgroup><col span="2"></colgroup>[/TABLE]

I Know i could make a list for each location, just be easier to pull data from this existing list.

 

[Fluff]

Ok, how about

Option Explicit
Private Dic As Object

Private Sub Worksheet_Activate()

   Dim Cl As Range
   Dim Lst1 As String

   Set Dic = CreateObject("scripting.dictionary")
   Dic.comparemode = vbTextCompare
   With Sheets("SQ Hierarchy")
      For Each Cl In .Range("B2", .Range("B" & Rows.Count).End(xlUp))
         If Not Dic.exists(Cl.Value) Then
            Dic.Add Cl.Value, CreateObject("scripting.dictionary")
            Dic(Cl.Value).Add Cl.Offset(, -1).Value, Nothing
         ElseIf Not Dic(Cl.Value).exists(Cl.Offset(, -1).Value) Then
            Dic(Cl.Value).Add Cl.Offset(, -1).Value, Nothing
         End If
      Next Cl
   End With

   Lst1 = Join(Dic.keys, ",")
Application.EnableEvents = False
   With Range("AB:AB").Validation
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
         Operator:=xlBetween, Formula1:=Lst1
   End With
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim Lst2 As String

   If Target.CountLarge > 1 Then Exit Sub
   If Intersect(Target, Range("AB:AB")) Is Nothing Then Exit Sub
Application.EnableEvents = False

   Target.Offset(, 1).ClearContents
   Lst2 = Join(Dic(Target.Value).keys, ",")
   With Target.Offset(, 1).Validation
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
         Operator:=xlBetween, Formula1:=Lst2
   End With
Application.EnableEvents = True
End Sub

This all needs to go in the sheet module for the sheet containing the DropDowns

 

[Godders199]

Thanks ALot.

I presume that C1, is the location of the names I want to appear in my list.

I have added the the code to the summary sheet.The drop down just gives me the list of locations, rather than the names at a single location, currently I made C1 Cardiff so my drop down should only show names located in Cardiff, if I am understanding correctly.

 

[Fluff]

If you select a location in AB2 then another dropdown will be in AC2 that will give the list of names for that location

 

[Godders199]

ok understand thanks