Macro Compile Error Sub or Function not defined

paulfitz54

New Member
Joined
Dec 30, 2014
Messages
32
Office Version
  1. 2019
Platform
  1. MacOS
Hello,

I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile error when Im trying to run them from the validation list.
Any help would be greatly appreciated
I am using MS Office 2016 for Mac
Thank you in advance,
Paul

I have pasted the code below;

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

'Interesect 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 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 IT a macro named IT is executed
        Case "Cleaning": Cleaning
      
         '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 Quality macro named Quality is executed
        Case "Quality": Quality
      
         '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 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

New Member
Joined
Dec 30, 2014
Messages
32
Office Version
  1. 2019
Platform
  1. MacOS
Its working a treat now with the original code, is this an alternative code?
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,448
Office Version
  1. 365
Platform
  1. Windows
Yes, it just needs to be run manually as requested. :)
 

paulfitz54

New Member
Joined
Dec 30, 2014
Messages
32
Office Version
  1. 2019
Platform
  1. MacOS
Got ya, thanks again, your providing an awesome service
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,448
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback
 

paulfitz54

New Member
Joined
Dec 30, 2014
Messages
32
Office Version
  1. 2019
Platform
  1. MacOS

ADVERTISEMENT

My pleasure
 

paulfitz54

New Member
Joined
Dec 30, 2014
Messages
32
Office Version
  1. 2019
Platform
  1. 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 and the tips you gave me 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. Is this possible? below is a copy of the change worksheet code.

Each Macro in the code displays different list

'Event code, Target variable contains the cell or range that has been changed
VBA Code:
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,929
Office Version
  1. 365
Platform
  1. Windows
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. Is this possible? below is a copy of the change worksheet code.

Each Macro in the code displays different list
Brand new questions should be posted in new threads, so that they appear as new, unanswered questions (not many people are going to look at threads that already have 3 pages worth of posts, unless they are already involved in it, so the number of people who would see your new question would be very limited).
 

Watch MrExcel Video

Forum statistics

Threads
1,114,520
Messages
5,548,534
Members
410,845
Latest member
OldSwimmer1650
Top