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

 

[paulfitz54]

You have gone to a lot of trouble to put this together, thank you very much, I'll work with this and let you know how I get on.
Thanks again

 

[HighAndWilder]

We are all holed up here until the danger passes and just waiting for some blue material to make scrubs for the nurses so I had lots of time on my hands.
It may all change tomorrow!!

 

[paulfitz54]

I think everybody is putting in a good effort to eradicate this especially the front line workers and the health service, I think were in lock down for another month at least

 

[HighAndWilder]

If its six months I'd not be surprised. Those of us who can sit happily by ourselves in front of a computer for hours are probaably better placed
to cope than those who need to party.

 

[paulfitz54]

You got that right ...