VBA Code for two drop down lists to display department info, then sub department info

[paulfitz54]

Hi,

I recently enquired about a macro to display a list from a value selected in a validation drop-down list in an Excel worksheet.

The tips I got from MrExcel worked perfectly, thank you.

I now want to run a macro to display a list from a value selected in a second drop-down list, i.e. first drop-down list would display department and the second drop-down list would display sub department (updating the department list). Is this possible? below is a copy of the change worksheet code for the original single drop-down list.

Each Macro in the code displays a list from the value selected in the drop-down list

'Event code, Target variable contains the cell or range that has been changed
Private Sub Worksheet_Change(ByVal Target As Range)

'Intersect method returns a range object of a rectangular intersection of two or more cell ranges
If Target.Address(True, True) = "$B$3" Then

'Select Case statment performs one out of several groups of statements based on a condition
Select Case Target

'If text in cell B3 is equal to Automation a macro named Automation is executed
Case "Automation"
Call Automation

'If text in cell B3 is equal to Basic Curriculum a macro named Basic is executed
Case "Basic Curriculum": Basic

'If text in cell B3 is equal to Cleaning a macro named Cleaning is executed
Case "Cleaning": Cleaning

'If text in cell B3 is equal to Engineering a macro named Engineering is executed
Case "Engineering": Engineering

'If text in cell B3 is equal to Facilities a macro named Facilities is executed
Case "Facilities": Facilities

'If text in cell B3 is equal to Finance a macro named Finance is executed
Case "Finance": Finance

'If text in cell B3 is equal to GSC a macro named GSC is executed
Case "GSC": GSC

'If text in cell B3 is equal to GSC Quality a macro named GSC Quality is executed
Case "GSC Quality": GSCQuality

'If text in cell B3 is equal to HR a macro named HR is executed
Case "HR": HR

'If text in cell B3 is equal to Engineering a macro named Engineering is executed
Case "IT": IT

'If text in cell B3 is equal to Labeling a macro named Labeling is executed
Case "Labeling": Labeling

'If text in cell B3 is equal to Maintenance a macro named Maintenance is executed
Case "Maintenance": Maintenance

'If text in cell B3 is equal to Materials a macro named Materials is executed
Case "Materials": Materials

'If text in cell B3 is equal to Operations a macro named Operations is executed
Case "Operations": Operations

'If text in cell B3 is equal to PPNL a macro named PPNL is executed
Case "PPNL": PPNL

'If text in cell B3 is equal to Quality a macro named Quality is executed
Case "Quality": Quality

'If text in cell B3 is equal to Receiving Inspection a macro named Receiving Inspection is executed
Case "Receiving Inspection": Receiving

'If text in cell B3 is equal to Safety a macro named Safety is executed
Case "Safety": Safety

'If text in cell B3 is equal to Specialist a macro named Specialist is executed
Case "Specialist": Specialist

'If text in cell B3 is equal to Training a macro named Training is executed
Case "Training": Training

'If text in cell B3 is equal to Validation a macro named Validation is executed
Case "Validation": Validation



'Stops Select Case statement
End Select
End If
End Sub

 

[HighAndWilder]

Do you have a list of Departments and Sub-Departments in a worksheet?

How does the Department drop down list get populated?

 

[Peter_SSs]

Also, what version of Excel are you using? Please update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.

 

[paulfitz54]

I have a search page with a drop-down list of departments, when I select a department, I.E. Cleaning a macro called Cleaning is called from the worksheet change code above to display the Cleaning list.
I am trying to create a second drop-down list for sub-departments to reduct the Cleaning list further

 

[paulfitz54]

I forgot to mention the list gets populated from a hidden master sheet that the Macro filters

 

[HighAndWilder]

Try this out on a new sheet first.

The code goes in the Worksheet Code Module.

It will demonstrate how you may be able to do it.

Set up a drop down menu (validation list) in cell C2.

Set up columns A and B as in the image.

You will need to change cell and range references to work on your sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Address = "$C$2" Then
        Call subAddValidationList(Range("$C$2"))
    End If
    
End Sub

Public Sub subAddValidationList(strDepartment As String)
Dim intCount As Integer
Dim rngData As Range
        
        intCount = Application.WorksheetFunction.CountIf(Range("A2:A260"), strDepartment)
        
        Set rngData = Range("B" & Application.WorksheetFunction.Match(strDepartment, Range("A2:A260"), 0) + 1).Resize(intCount, 1)
        
        With Range("D3").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="='" & ActiveSheet.Name & "'!" & rngData.Address
        End With
        
        Range("D3") = ""
        
End Sub

 

[paulfitz54]

I'll give a that a run later, thanks for your help ...

 

[Peter_SSs]

Thanks for updating your profile.

 

[paulfitz54]

Thanks for the tip, but I'm afraid I don't know how this works, are you using a cascading validation list on the above code?
I don't know enough about VBA to decipher the code
Apologies ...

 

[HighAndWilder]

I have documented the code to explain how it works.

The cell location of the department and sub department data validation lists can be changed but you will need to change
the references to these cells.

' *************************************************************************************************
' This code will take the department name selected from the datavalidation list in cell C2 and change the list
' on the data validation list in cell D3 containing the sub departments. The sub department data validation list will only
' contain the sub departments related to the selected department.
' It depends on having a list of departments and sub departments in columns A and B as depicted in the image provided.
' This list needs to be in department and sub department order.
' This list could be on another worksheet but the "A2:A260" range would need to be changed.

' *******************************************************************************************************
' This code goes in the Worksheet Code Module. (Right click on the sheet tab and select View Code. Right click and paste.)
' *******************************************************************************************************

' Procedure : Worksheet_Change
' This procedure below is invoked when any change is made to a cell on the worksheet.
' If checks to see if the cell changed is D2. (Cell D2 contains the Department data validation list)
' If cell D2 has been changed when a department is selected then the sub procedure called 'subAddValidationList' is called and the
' name of the department is passed to it.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Address = "$D$2" Then
        Call subAddValidationList(Target.Value)
    End If
    
End Sub

' *******************************************************************************************************
' Procedure subAddValidationList
' This procedure takes the department name passed to it and finds out how many times this exists in column A.
' It then creates a range, rngData, which makes reference to the block of cells in column B which contain the sub departments
' for that department.
' It then deletes and recreates a data validation list in cell D4 containing the sub departments.
' It then deletes the current selection in cell D4 for you to make a new selection.
Public Sub subAddValidationList(strDepartment As String)
Dim intCount As Integer
Dim rngData As Range
        
        intCount = Application.WorksheetFunction.CountIf(Range("A2:A260"), strDepartment)
        
        Set rngData = Range("B" & Application.WorksheetFunction.Match(strDepartment, Range("A2:A260"), 0) + 1).Resize(intCount, 1)
        
        With Range("D4").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="='" & ActiveSheet.Name & "'!" & rngData.Address
        End With
        
        Range("D4") = ""
        
End Sub