paulfitz54
New Member
- Joined
- Dec 30, 2014
- Messages
- 32
- Office Version
- 2019
- Platform
- MacOS
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
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